How do I use a table of useremails to set certain tables to edit vs read only for everyone else

AppSheet AppSheet Q&A
,
1

I have a table that has all employees and another table that list the directors and primary and backup liaisons.

I will be using our company domain to authenticate login access but want to limit who can update records based on this second table, so that only the director and primary/backup liaisons can update records for their department. Department is a field in both tables.

I’m really stuck on how to do this.

I know I can take the individual email addresses and list them in the Date > Table > are updates allowed section but I think there may be a better way.

Attached is a screenshot of the columns for the table I want to use for the security filters. They can see all employees regardless of department, but should only be able to update records for their department. Hope that makes sense.

Thanks

2X_c_c639bcb64f906b2a179a309e86c1f40fb5de9219.jpeg

2

@tcanelli
Just create a table for User Access Privileges including user’s email, tablenames and ENUM values for the access levels. Than you can simply use a LOOKUP expression via Data > Table > Are updates allowed property i.e.

LOOKUP(
    USEREMAIL(),
    "UserAccessTable",
    "EmailColumn",
    "AccessRightsColumn"
)


[Looking for some insights and support from APPSHEET user](https://community.appsheet.com/t/looking-for-some-insights-and-support-from-appsheet-user/17466/11) Questions

@Appswonder_India I may advise to setup a USERS table and define in that table which users can access which view and what access privileges (read, write, delete, all changes) with a simple LOOKUP expression both in the UX Views Show_if expression, Security filters and table access modes. LOOKUP( USEREMAIL(), “Users”, “Email”, “Access_Mode” ) which will eventually return you one of the below results (if you had correctly set an ENUM column with the following values for that Acc…

3

I don’t really understand. What would go in the “accessrightscol”? And how would this just limit them to edit their own department info?

4

Could something like this also be used?

OR(IN(USEREMAIL(), “Dept PMs (Liaisons to EM)”[Backup PM Emails]), USEREMAIL() = [Backup PM Emails])

5

tcanelli:

I […] want to limit who can update records based on this second table, so that only the director and primary/backup liaisons can update records for their department.

There are several ways to approach this depending on how tight you want security. The most straight-forward way is to attach a condition to the table’s row edit action so that the row can only be edited by the appropriate users:

2X_0_0f212e2cdf6b8fb04e49e8285d575acfeedb86b4.png
2X_0_0f212e2cdf6b8fb04e49e8285d575acfeedb86b4.png796×503 39.2 KB

With an expression like this:

ISNOTBLANK(
  FILTER(
    "MyTable",
    AND(
      ([_THISROW].[Department] = [Department])
      IN(
        USEREMAIL(),
        LIST(
          [Director Email],
          [Primary PM Emails],
          [Backup PM Emails]
        )
      )
    )
  )
)

Note that if Primary PM Emails and Backup PM Emails are lists, the expression will need an adjustment.

1 Like
6

Steve:

ISNOTBLANK( FILTER( “MyTable”, AND( ([_THISROW].[Department] = [Department]) IN( USEREMAIL(), LIST( [Director Email], [Primary PM Emails], [Backup PM Emails] ) ) ) ) )

2X_1_18ba2d128520e5709081714904c12ad12c96ebc7.jpeg
2X_1_18ba2d128520e5709081714904c12ad12c96ebc7.jpeg797×535 28.2 KB

I get the attached error. I wasn’t sure what your comment about the lists meant but that doesn’t seem to be what the error is about???

7

tcanelli:

ISNOTBLANK( FILTER( “MyTable”, AND( ([_THISROW].[Department] = [Department]) IN( USEREMAIL(), LIST( [Director Email], [Primary PM Emails], [Backup PM Emails] ) ) ) ) )

@tcanelli
Unintentionally, @Steve had forgotten a comma within AND. Try with this:

ISNOTBLANK(
  FILTER(
    "MyTable",
    AND(
      ([_THISROW].[Department] = [Department]),
      IN(
        USEREMAIL(),
        LIST(
          [Director Email],
          [Primary PM Emails],
          [Backup PM Emails]
        )
      )
    )
  )
)
1 Like
8

Thank you @Steve and @LeventK - this seems to work - I think. I don’t see the edit button on records outside my department, but notice that fields I set for quick edit still allow for updates. Is this normal? Should I add the above expression in another place to prevent even the quick edit fields from being updating?

9

@tcanelli
You can use the same expression for the Editable_if or Show_if property of your Quick Edit columns I believe. If you prefer Show_if, then you need to set the global property to ON from UX > Options

1 Like
10

Yes, that worked! Thank you!

1 Like
11

You’re welcome, my pleasure to help.