User Data Access Restriction based on specific role

Hi together,

I have created an app with a set of different tables like:

  • Company
  • Vehicle
  • Driver
  • User
  • Documents
  • Incidents

Following User roles are defined for now:

For these four Roles not security filter shall be applied on all tables of the app:
Admin
HolcimLogistik
HolcimCCC
HolcimKosice

For all others one Security filter shall be applied like the following:

[FirmenID] = LOOKUP(USEREMAIL(), “Nutzer”, “E-Mail”, “FirmenID”)

On all tables I have included the REF column [FirmenID]. This is nevertheless only maintained for the security relevant Roles on User table.
How can i make this SF conditional?
How can multiple Roles best be assigned IF a more detailed separation of functions is needed?

Thanks so much for any help.
Cheers,
Sebastian

Thanks Steve,

but how can I use this referencing from the security filter of one table to a non-referenced field from another table. I am a bit lost here. Have been trying for hours to get this to work now, but was not able to… Sorry I am almost a complete noobie…