Comparing coma separated fields between tables as ...

Michael2 · 12-30-2020 12:43 PM

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:

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.

Comparing coma separated fields between tables as a security filter