Security Filter against Value in Other Table that is referenced by Bridge Table

[Markus_Malessa]

I have a TableA that already has a security filter for ColumnX = “Something”. Now I would also like a security filter in TableB where the relationship between TableB and TableA is stored in TableC in Columns TableB_key and TableA_key.

In a SQL like query this would look like:
Select * from TableB Where TableB.key = TableC.TableB_key And TableA.key = TableC.TableA_key And TableA.ColumnX = “Something”

Due to restrictions of comparing values to lists ultimately I tried this in AppSheet but did not get any results:
AND(IN([key], TableC[TableB_key]), IN(TableA[key], TableC[TableA_key]), IN(“Something”, TableA[ColumnX]))

Is something like this even possible in AppSheet and if so does anyone have any suggestions? The main reason I am using this approach is because I want to keep the amount of data returned to the app more manageable.

[Steve · Accepted solution]

This?

ISNOTBLANK(
  INTERSECT(
    FILTER(
      "tableA",
      ("something" = [columnX])
    ),
    SELECT(
      tableC[tableA row],
      ([_THISROW] = [tableB row]),
      TRUE
    )
  )
)

1. FILTER(...) gives all rows in tableA that have the desired value in columnX.

2. SELECT(...) gives all rows in tableA that bridge to this row in tableB.

3. INTERSECT(...) gives only the rows common to both lists (1) and (2).

4. ISNOTBLANK(...) asks, are there any rows in common?

We could achieve the same result with nested SELECT() expressions, but this approach is hugely more efficient!

See also:

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

INTERSECT()

Items common to two lists

help.appsheet.com

ISNOTBLANK()

Is value present?

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Markus_Malessa]

Hm, might be missing something on my side I suppose but it is giving me this error:

Of course in this case I am using the actual table names and columns vs shortened versions that I used to ask the question. Any ideas?

[Steve]

Ah, I see INTERSECT() is still broken. Try preceding FILTER() and SELECT() each with LIST + :

ISNOTBLANK(
  INTERSECT(
    (LIST() + FILTER(...)),
    (LIST() + SELECT(...))
  )
)

[Markus_Malessa]

Excellent, that does it! Thanks a lot @Steve! Say do you do office hours for Q&A sessions? Lol

[Steve]

Markus_Malessa:

Say do you do office hours for Q&A sessions?

I do not, I’m afraid. I’m happy to answer questions here, though.

[Steve]

I’ve managed to get a developer’s attention on the INTERSECT() behavior. He asks:

Out of curiosity what is the type of the key column on PermitList, and what is the type of the PermitList_fk column on MeterInformation_Has_PermitList

Can you provide those? If type Ref, please also note the referenced table.

[Markus_Malessa]

The table PermitList has key column of [id] set as ‘Text’ with initial value of UNIQUEID(). The bridge table MeterInformation_Has_PermitList has column of [MeterInformation_fk] set as ‘Ref’ and links to table MeterInformation, column of [PermitList_fk] set as ‘Ref’ and links to table PermitList, it also has a virtual column as the key [_ComputedKey] set as ‘Text’ with formula of CONCATENATE([MeterInformation_fk], [PermitList_fk]).
Let me know if you need any additional info. Thanks for the follow up.