We have a Work Order table that is linked to a Location Services table. When creating a work order the users select the associated Location Service they are scheduling the work for. There are users who are authorized to create work orders but they are not authorized to view the Customer Price field in the Location Services table (restricted access by role). When a person in the restricted role creates a work order the Customer Price is shown as $0. There are other people who will view the work order who do have access to the Customer Price field and need that info for billing & profitability analysis, etc. but it still shows up as $0. Is there a way to pull this information in when the person creating the work order doesn't have access to it?
If I understand you correctly - the Customer Price is a lookup field - from Work Order, looking up the Customer Price in the Location Service.
The value of the lookup field should not be affected by the lack of access of the person creating the Work Order.
Or is the Customer Price a field that is editable on Work Orders? If so - since the person creating the Work Order does not have access to the Customer Price field, it most likely is defaulting to 0.
Test set up:
- 1 Location Service has many Work Orders
- Customer Price (Numeric Currency field) in the parent Location Services table
- Location Service - Customer Price as a lookup field - in the child Work Order table
- Snapshot Price - as a snapshot Numeric Currency field - in the child Work Order table
- and then a user in the Participant role
- changing field level permissions for the Participant user, for the following fields:
Customer Price (in Location Services table)
Location Service - Customer Price (in Work Orders table)
Snapshot Price (in Work Orders table)
1) Participant has "None" access to Customer Price (in Location Services table)
- Snapshot Price is blank (that's my default)
- so if the Participant cannot see the Customer Price in the parent Location Services table, the snapshot field cannot be filled in with the correct value
- as expected the lookup field still works, even if the Participant user can't see it - but it doesn't store the snapshot
2) Participant has "None" access to Location Service - Customer Price AND Snapshot Price (in Work Orders), but "View" access to Customer Price (in Location Services table)
- this still worked! The Snapshot Price populated properly. This was unexpected - I thought that if you didn't have edit access to Snapshot Price, it would default to 0 (or blank).
- however - this still leaves visible Customer Price (in Location Services table) - which is not what you want
- Looks like whatever writes in the Snapshot Price has to have at least View access to Customer Price (in the Location Services table)
- I even tried using WebHooks to edit the Snapshot value - but it still didn't work - again because the Participant user needed to be able to see the original Customer Price value to write to the final field
- You can't just use the lookup field Location Services - Customer Price - because you want to "snapshot" the price at the time of creating the Work Order, and not have it change later.
- So maybe you could have reports that the Managers / Admins can see, where they can properly populate the Snapshot Price (after the Work Orders have been created).
- Or you can use Workato or some other external script / service that has broader view permissions, and can populate the Snapshot Price (or a similar field) properly
I played around with Webhooks some more, and got it to work - but on a "non-snapshot" field.
- I created a Numeric-Currency field, called "Current Price"
- I set the permissions for this field to be "None" for the Participant role
- the Participant role still cannot see the Customer Price - in both the Location Services or Work Order tables ("None" access)
- the webhook uses the API_EditRecord to update this, with a user token for a user that has access to the Customer Price
- the "Current Price" is updated
If your account have access to Webhooks (with a QuickBase plan of Premier and above), and you can store the Customer Price in a non-snapshot field - let me know, and I can go into more detail on setting up the Webhook.