Validate table access with criteria

AppSheet AppSheet Q&A
, ,
1

Hi Community

I have a user table, with a column called [Level] with two possible entries “full” or “limited”

If [Level] = “full” then you can see all records in the RESERVE table

Else If [Level] = “limited” then can see only the records, which in the column [Email] is the same as USERSETTINGS(EMAIL), in the RESERVE table

I would like some help with this syntax. How can I declare this condition?

2

Does your user table already have a security filter of [Email] = USEREMAIL()?

If so, then implement a security filter on the RESERVE table with expression of IF(INDEX(user table[Level], 1) = "full", true, [useremail] = INDEX(user table[email], 1)

That should do it. (Untested)

3

Markus_Malessa:

Does your user table already have a security filter of [Email] = USEREMAIL() ?> > If so, then implement a security filter on the RESERVE table with expression of IF(INDEX(user table[Level], 1) = "full", true, [useremail] = INDEX(user table[email], 1)> > That should do it. (Untested)

Hi @Markus_Malessa

Thanks for the tip, but it didn’t work for me.

Correcting a piece of information, I’m not using useremail() … I’m using usersettings.

So, i thought that, knowing who the usersettings(email) is, i check the user table column [level] .

If USERSETTINGS(EMAIL), table USER [Level] = “full” then you can see all records in the RESERVE table

Else If USERSETTINGS(EMAIL), table USER [Level] = “limited” then can see only the records, which in the column [Email] is the same as USERSETTINGS(EMAIL), in the RESERVE table.

1 Like
4

Maybe this?

ISNOTBLANK(
  FILTER(
    "User",
    AND(
      ([user-email] = USERSETTINGS("Email")),
      OR(
        ("full" = [Level]),
        AND(
          ("limited" = [Level]),
          ([user-email] = [_THISROW].[reserve-email])
        )
      )
    )
  )
)

Replace user-email (2x) with the name of the column in the User table that contains the user’s email address, and reserve-email (1x) with the name of the column in the Reserve table that contains the email address.

The expression answers the question: is there at least one row in the User table (ISNOTBLANK(FILTER("User", ...))) that belongs to the current user (([user-email] = USERSETTINGS("Email"))) that indicates the user has either full access (("full" = [Level])) or limited access (("limited" = [Level])) and the Reserve row is this user’s (([user-email] = [_THISROW].[reserve-email]))?

1 Like
5

Steve:

Maybe this?> > auto> ISNOTBLANK(> FILTER(> "User",> AND(> ([user-email] = USERSETTINGS("Email"))> OR(> ("full" = [Level]),> AND(> ("limited" = [Level]),> ([user-email] = [_THISROW].[reserve-email])> )> )> )> )> )> > > > Replace user-email (2x) with the name of the column in the User table that contains the user’s email address, and reserve-email (1x) with the name of the column i the Reserve table that contains the email address.> > The expression answers the question: is there at least one row in the User table ( ISNOTBLANK(FILTER("User", ...)) ) that belongs to the current user ( ([user-email] = USERSETTINGS("Email")) ) that indicates the user has either full access ( ("full" = [Level]) ) or limited access ( ("limited" = [Level]) ) and the Reserve row is this user’s ( ([user-email] = [_THISROW].[reserve-email]) )?

Hi @Steve

Thanks for the class, but it didn’t work for me.

I’m thinking about:

MsgError: Condition AND(([EMAIL] = [_THISUSER].[EMAIL])) has an invalid structure: at least 2 subexpression(s) required

1 Like
6

Whoops! I missed a comma. I’ve fixed the expression above.

2 Likes
7

The comma validated the expression, when I put the email column of the user table.

When I put the email column from the reserve table, it didn’t find that column in the table.

But they are there haha