Security Filter on a table: expression needed to show more results

AppSheet AppSheet Q&A
,
1

Hello smart fellows,

Once again I am struggling with an expression and need your help. I want to add a Security Filter to my table “Gebaeude” so only those building-entries are shown to the User, which were assigned to him/her.

I am using a MySQL DB. The structure looks as follows:

3X_c_f_cf38c129b04b3ddca2bff501c17619abbb2ae400.png
3X_c_f_cf38c129b04b3ddca2bff501c17619abbb2ae400.png659×168 35.3 KB

Users are saved in the table “Person” and my buildings in the table “Gebaeude”. The information on who is assigned to which building is saved in table “PersonGebaeude”. Each entry is saved separately. This looks as follows:

3X_3_0_30c19cdbeb5f757eced27f2eb89e55397035a8c8.png

My formula in the security filter for table “Gebaeude” shows only the first result from “PersonGebaeude”:
[Id] = LOOKUP(LOOKUP(USEREMAIL(), "Person", "Email", "Id"), "PersonGebaeude", "PersonId", "GebaeudeId")
How can I change it to give out a whole list that matches? (I would prefer to use the Security Filter for it rather than slice.)

2

Have you thought to use horizontal technique with security filters? Please check this article…

3 Likes
3

Your expression at the moment is looking for a single equality and therefore is returning one result only. What you really want to do is return a list of matches, for that you would probably be better using techniques like SELECT() and IN()

1 Like
4

This:

ISNOTBLANK(
  FILTER(
    "PersonGebaeude",
    AND(
      ([_THISROW].[ID] = [GebaeudeID].[ID]),
      (USEREMAIL() = [PesonID].[Email])
    )
  )
)
2 Likes
5

(post deleted by author)

6

Thank you for sharing this!

1 Like
7

Hi Steve, thanks a lot! Works as wanted.

(Small adjustments:)

ISNOTBLANK(
  FILTER(
    "PersonGebaeude",
    	AND(
      	  ([_THISROW].[Id] = [GebaeudeId]),
      	  (USEREMAIL() = [PersonId].[Email])
    	)
  )
)

One question to it, as I haven’t used this in an expression before: if you write [PersonId].[Email] this goes from the column “GebaeudeId” to column “Id” in the same row? Like using LOOKUP()? Is there an article on that?

1 Like