Comparing coma separated fields between tables as a security filter

[Michael2]

On the face it seems simple but, I’m struggling to find the expression that is both valid and acceptable as a security filter. (Virtual Columns are not an option with security fields)

To summarize; Two separate tables, People and Apps. Each table has a single field [Crew]

In the People table, a person can belong to more than one crew. This is saved in field People[Crew]. If this field contains multiple crews, (person belongs to multiple crews) each value is separated with a coma and a space: Crew1, Crew2.

In the Apps table, the [Crew] field determines which crew(s) can access the app specified but the row. Each app can be accessed by several multiple crews:

App ‘A’ can be accessed by Crew1, Crew2
App ‘B’ can be accessed by Crew1, Crew3 (but not crew 2)

The following expression works fine so long as the person only belongs to ONE crew. If additional crews are added to People[Crew], the expression returns no rows from the Apps table.

From the Apps table I set the following as the security filter:
CONTAINS([Crew], SELECT(People[Crew], [UserEmail]=USEREMAIL()))

I have tried many different combinations of IF(), INTERSECT(), CONTAINS(), etc and all fail for one reason or another. The expression is either outright invalid, valid or cannot be used as a security filter (VCs), or is valid but doesn’t return any values.

I’d appreciate anyone that could get me pointed in the right direction…

[Marc_Dillon]

Maybe something like this?
Assuming Apps[Crew] is also a a text field separated by a comma and a space.

ISNOTBLANK(

  INTERSECT(

    SPLIT( [Crew] , ", ")
    ,
    SPLIT( LOOKUP( USEREMAIL() , "People" , "UserEmail" , "Crew") , ", ")

  )

)

[MultiTech]

IN() is a better option than CONTAINS()

This might help:

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks 💡

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

[Michael2]

Because this setup relies on the use of a slice, wouldn’t it’s use be ruled out of security filters? All data would be sent to and from the user’s app instance, correct?

The setup I currently have is very similar with the exception of creating the slice…

[MultiTech]

This has been in an app of mine for… 3… 3.5 years now.

[Steve]

Try:

ISNOTBLANK(
  FILTER(
    "People",
    IFS(
      ISBLANK([UserEmail]),
        FALSE,
      NOT([UserEmail] = USEREMAIL()),
        FALSE,
      ISBLANK(
        INTERSECT(
          [_THISROW].[Crew],
          [Crew]
        )
      ),
        FALSE,
      TRUE,
        TRUE
    )
  )
)

This is expensive. @MultiTech_Visions’ Current_User slice would help a lot.