Problem with security filter or slice row conditio...

monedero · 04-27-2021 06:57 AM

I try to use the following expression as a security filter or as a row condition in a slice but the result is not what I would expect. I want the user (identified by his email) to be able to see only the data of the remaining members of the same Coro (Choir) than him. Why a randomly chosen value from the list? The email is the key value of table Miembros and it should identify uniquely the user if he is in that table and therefore, tell us his choir to compare with the choir of every row and only show the user the rows with his same choir. What am I doing wrong?

[Coro]=lookup(useremail(),Miembros,Email,Coro)

(The value of column ‘Coro’) is equal to (One randomly chosen value from this list (

…The list of values of column ‘Coro’

…from rows of table ‘Miembros’

…where this condition is true: ((The value of column ‘Email’) is equal to (USEREMAIL()))))

Marc_Dillon

The LOOKUP() expression is just a wrapper for ANY( SELECT() ). The ANY() expression is labelled as selecting a random value from a List, but really it just selects the first value. In most use-cases of LOOKUP(), you should be specifying a condition that will only return a List of a single item. Nothing to worry about there.

Your expression seems to be correct otherwise.

What result do you get?

Rifad

Even I have noticed some issues with LOOKUP. ANY(SELECT()) just works perfect. I have seen lookup only taking the first value from ENUMLIST. But ANY(SELECT()) Selects the entire value as a list from ENUMLIST.

Rifad

I am not sure if this is true. When I Use LOOKUP() for an ENUMLIST it only select the first value from that ENUMLIST Column.

From my use case i have found ANY(SELECT()) is not equivalent in to the LOOKUP() Sometimes like mentioned in below image.

Even if i wrap LOOKUP() around LIST() its not giving me right output but ANY(SELECT()) Works perfect.

You can try using

[Coro]=ANY(SELECT(Miembros[Coro],USEREMAIL()=[Email]))

monedero

Thanks for your suggestion. But it is not working. When I access the app with a user name whose role is not admin, the app should not show me Related records from the table Finanzas when those records belong to a different choir than the one to whichh belongs the current user. Instead it is showing those data through the view “Coros” in the section Related finanzas.

Marc_Dillon

So you just added in a bunch of extra complexity here that you seemed to forget to indicate in your first post. I see nothing wrong with the expression you initially posted. How about you do a simple test with just that expression in a slice or security filter and confirm that it does indeed work, before adding on more complexity? Otherwise, you’re going to have to explain your data and app setup (with screenshots!) a lot more thoroughly so someone can get an idea of what you are doing, in order to help.

monedero

Sorry, I believe it is my Spanish convoluted way of explaining the problem. Let me explain the problem.
Several choirs, each have its own administrator (who needs to see only data related to his choir) and one person is the owner of all choirs (who must see everything). I have given the owner a role of admin and each choir administrator a role of user.
In the view that shows the choirs, when you open a choir, you see its related members, its related songs and its related finances. The problem is that through that view, the choir administrator can see only members related to his choir, songs related to his choir, but finances related to all choirs. In the three cases I have used the same security filter.

monedero

Here is the security filter used in all cases
OR(LOOKUP(USEREMAIL(),MAESTROS,EMAIL,CORO)=[Coro],“Admin”=USERROLE())

in order for this to happen I access as one of the users, not the admin role.

Marc_Dillon

Be aware that if you are using the “Preview App as” field from underneath the emulator, there are some funky things that can happen here.

First and foremost, even if you are “previewing as” someone with USERROLE() = “User”, but you are still logged into the editor on an account with USERROLE() = “Admin”, then the emulator is still going to return your USERROLE as “Admin”. To properly test in this case, switch your own role from “Admin” to “User” while you are previewing.

Second, after you’ve switched your role as suggested above, you may mor may not need to refresh the editor’s browser page.

Marc_Dillon

For clarification, when you open the Detail View for a specific Choir, you can see an Inline View of “Related Finances”, but it shows Finances for ALL Choirs?

This sounds like there is something wrong with how you have the References between the Table setup, and possibly has nothing to do with this Security Filter expression that you keep talking about.

Please show some screenshots of what you see in the app, as well as how your Tables/Columns are setup.

monedero

I will test what you suggest but here are the screen shots with table relationships and the emulator screens.

monedero

I just tested and I realized that my statement is wrong. I was misled by the sample data. The real problem is that the security filter is not working at all. I am being able to see all data for all choirs in the inline view of each choir.

Marc_Dillon

Again, for clarification:

Please provide screenshots showing the exact problematic situation in the emulator, and explaining why it is not what you need.
Please provide screenshots of the Columns page in the editor.

monedero

I see finances only for the specific choir but I should not see any related finances for that choir as I am not identified as the administrator of that choir and I am not the owner of the organization.

Marc_Dillon

Ok, understood.

This MAESTROS Table, these are the records for the “choir administrators”?
Is it the case that not every user will have a record of their own in this Table?

You might be running into the issue where a blank value on the left side of an equality makes it return TRUE, no matter what is on the right side of the equality. Given this, and just the general way that I personally would think about this situation, I would write your Security Filter as follows:

OR(
  USERROLE() = "Admin" ,
  USEREMAIL() = LOOKUP( [_THISROW].[Coro] , MAESTROS , CORO , EMAIL)
)

It may also be advisable to create a “my administered choirs” slice of the MAESTROS Table (named however you want of course), with a row filter condition of:
USEREMAIL() = [EMAIL]
Then your Security Filter can more simply become:

OR(
  USERROLE() = "Admin" ,
  IN( [Coro] , my administered choirs[Coro] )
)

Problem with security filter or slice row condition using lookup