Is there a way to display a field that the person who creates the record doesn't have access to for the people who do have access to it?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

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?

Photo of Terri

Terri

  • 70 Points

Posted 2 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
Is the table relationship as follows?  1 Location Service has many Work Orders

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.
Photo of Terri

Terri

  • 70 Points
You may be on to something.  You are correct about the relationship.  However, revisiting this I see I created a "Snapshot Price" field for the edit & view mode which that role does not have access to.  Would that cause it to default to 0 as you indicated?
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
I did some tests on this - looks like it's the combination of the snapshot field, and removing all permissions from the Customer Price in the parent Location Services table.

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

Tests:
- 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)

Test results:
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

Options:
- 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
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
Update on this:

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.