Security Filter for Many to Many Relationship

[Peter_S1]

Hi,

I’m trying to do a security filter that will only allow users belonging to a certain company to view records for their company.
This would be a many to many relationship?
Meaning that a company can have many users, and I want all users of that company to be able to see information for that company only.

A user could also own more than one company/entity. Is there a way to do this where more than one user has access to more than one company?

[Steve]

“Many to many” has no bearing here.

How does each row of the table with the security filter identify the company to which it’s related?

How does the app know which companies the current user is associated with?

If each row has a Company column that identifies the company, and the user identifies the companies they want in users settings, you might use something like:

ISNOTBLANK(
  FILTER(
    "_Per User Settings",
    IN([_THISROW].[Company], [My Companies])
  )
)

[Peter_S1]

Thank you, Steve. I have two tables:

1. Company table which lists company ID and Name
2. Users table which lists all the users (emailID) of each company referenced by company ID ( this table will have same users belonging to different companies.

Is your expression above suitable for this situation?

[Steve · Accepted solution]

This might be closer:

ISNOTBLANK(
  FILTER(
    "Users",
    AND(
      ISNOTBLANK([Email]),
      ([Email] = USEREMAIL()),
      ISNOTBLANK([Company ID]),
      ([Company ID] = [_THISROW].[Company ID])
    )
  )
)

[Peter_S1]

Thank you, Steve. This is gold! Works pretty well. Really appreciate your help.