Using a formula field to determine view access is not working.. Help please!

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

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")

and

if(
[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.

Thanks


Photo of Matthew

Matthew

  • 60 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Several problems with your formula. Try this:

If(
  [Requested By] = User(), "YES"
  [Supervisor] = User(), "YES",
  [Manager] = User(), "YES",
  "NO"
)

Or:

If(
  [Requested By] = User() OR [Supervisor] = User() OR [Manager] = User(),
  "YES",
  "NO"
)
Dan, you second formula with a syntax correction comma removed is the same as what Matthew posted,

If(
  [Requested By] = User() OR [Supervisor] = User() OR [Manager] = User(),
  "YES",
  "NO"
)

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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Maybe my formula is wrong - I am tired from working 24/7 to clear my schedule for next week.

FWIW  his original formula was missing a closing bracket:

[Supervisor=User(), "YES", "NO",
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
fixed the comma. FWIW I try to write my formulas using whites space and indentation to make the formulas easier to read.
I do that too, I was just pointing out that removing your indentation made it the same as Matthew's first try.
Photo of Matthew

Matthew

  • 60 Points
Thanks for the quick responses.
Dan, I tried your first one and still no luck. The missing bracket was just a typo on here, that's what I get for not copy/pasting.

The part that's throwing me off is that it works for [Requested By] but not the others.
So let now assume that your formula is perfect.

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

Matthew

  • 60 Points
Well, assuming my formula is correct, it should output the text "YES" if the current user is in any of those fields.
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...
Photo of Matthew

Matthew

  • 60 Points
Also, my comrade is in a single role
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Are they user fields? That is what I assumed.
Photo of Matthew

Matthew

  • 60 Points
Yeah the field type is User (lookup)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
So what is the reference to roles all about? There are no field types or formula functions that deal with roles.
Photo of Matthew

Matthew

  • 60 Points
Do you mean Mark's question?
 "Are they in multiple Roles?  Note that QuickBase gives the Most access possible when the same user is in multiple roles."
Photo of Matthew

Matthew

  • 60 Points
I think the concern was if there was a conflict with the "Supervisor" being in more than 1 role, but that isn't the case.
Photo of Matthew

Matthew

  • 60 Points
I reduced the formula to just:
If([Supervisor]=User(), "YES", "NO")
And it didn't make any difference. Hmmm.
Photo of Matthew

Matthew

  • 60 Points
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
That would suggest [Supervisor] is not a user field maybe?.
Photo of Matthew

Matthew

  • 60 Points
Dan, see the screenshots above.
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.
Is the choice  for the Supervisor taken from a relationship from a table of Supervisors?  Or is it just a User field type.  If the former, then do all users have view access to that Table of Supervisor choices?  Maybe your buddy has no access to that table, so he cannot see the lookup of the Supervisor userid.
I see now from your screen shot that the choice of supervisors is indeed from a lookup.  Does your buddy have access to that table?
Photo of Matthew

Matthew

  • 60 Points
The "Supervisor" isn't a separate table, just a User field on the Employees table.

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!
Thx for letting us know.