Validate table access with criteria

[Alvim]

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?

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

[Alvim]

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.

[Steve]

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]))?

[Alvim]

Steve:

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

[Steve]

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

[Alvim]

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