How do I use a table of useremails to set certain ...

tcanelli · 12-23-2019 06:57 AM

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

LeventK

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

tcanelli

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

tcanelli

Could something like this also be used?

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

Steve

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:

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.

tcanelli

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

LeventK

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

tcanelli

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?

LeventK

@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

tcanelli

Yes, that worked! Thank you!

LeventK

You’re welcome, my pleasure to help.

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