So my form has 3 fields: Requested By, Supervisor, and Manager. They're all User fields, but the Supervisor and Manager fields are lookups.
I created a formula - text field like so:
if([Requested By]=User() or [Supervisor]=User() or [Manager]=User(), "YES", "NO")
On the access settings for the table, I chose "Custom Rule" for View access, and my rule is simply: when [Viewable] is equal to "YES"
For some reason it only works for the person in [Requested By] but not for the Sup or Manager.. I tried different ways to write the formula like:
if(([Requested By]=User()) or ([Supervisor]=User()) or ([Manager]=User()), "YES", "NO")
[Requested By]=User(), "YES", "NO",
[Supervisor=User(), "YES", "NO",
[Manager]=User(), "YES", "NO")
to no avail. Am I missing something? I feel like it should work.
[Requested By] = User() OR [Supervisor] = User() OR [Manager] = User(),
If([Requested By] = User() OR [Supervisor] = User() OR [Manager] = User(), "YES", "NO")
I'm actually not seeing what is wrong with Matthew's formula as the "or" is not case sensitive on a formula.
Why do you think that it is not working? Are you having some Supervisors see records that they should not be seeing? Are they in multiple Roles? Note that QuickBase gives the Most access possible when the same user is in multiple roles.
So to test this I turned off access restrictions, and added a record with me as the "Requested By" and the guy sitting next to me as the "Supervisor." When I view the record, the formula outputs "YES", but when he looks at it, it shows "NO".
Could it be because they're lookup fields? I don't think so, because I have a ton of other rules and notifications that run off of user lookups without any issue...
Like I said, I've done this setup before without issue. Here's a screenshot of an identical field on another table that has a similar setup, and works just fine: https://www.dropbox.com/s/zknjbxor9du2qsp/Working%20supervisor%20settings.PNG?dl=0
I'm going to open a support case, maybe its a problem on the back end.
A good night's rest solves everything... I was able to track down the problem today, it turned out to be a faulty custom rule for viewing access in the Employees table that only affected some of our roles, including the roles I was using for testing...........
It makes me wonder though, why were there no other issues in the rest of the app? Or maybe there were and nobody said anything? Who knows.
Anyways, thank you both for trying to help me out with this!