Solved: Re: Filter Data based on Team/Dept or Category

Yogesh_Patel · 11-07-2020 07:01 PM

Challenge Summary:
I want to do some level of role-based access control for my data as per below.

Here is my setup:

Table 1 (Contacts) - with columns Name, Phone, Address, Country etc…

Table 2 (Volunteers) - Names, Email Address, Team(s) they are part of by country etc… A volunteer might just be part of one Team (i.e. USA) or multiple Teams (i.e. USA, UK or ALL).

The two tables are not related to each other but can be if absolutely required.

Here is the problem I am trying to solve:

Once the volunteer opens the app, they should only see contacts that are in the country or countries the volunteer as been assigned to.

For example,

Volunteer1 is assigned to USA Team and therefore only USA contacts should be visible
Volunteer2 is assigned to USA and UK Teams and therefore any USA or UK contacts should be visible.
Volunteer3 is assigned to ALL Team and therefore they should see all contacts regardless of the country listed in the contact record.

My approach and where I need help:

Based on above, I thought the best way to do this might be to:

Create a slice where an expression filters data by:

Checking the logged in Volunteer by USEREMAIL()
Looking up in Table 2 what Team or Teams the user is assigned to (maybe via LOOKUP()).
Filter so the country of the contact matches country or countries the Volunteer is assigned to.

Any help would be greatly appreciated.

Thank you,

Yogesh_Patel

Thank you Steve. This is perfect. Works exactly as intended.

View solution in original post

khuslid

I’m not sure how you related the two tables, but if you have used REF, then you could add a filter in ‘Valid if’ filtering either another column or a text value in the same column.
Not sure I understood yot problem here…

Yogesh_Patel

Hello Khuslid,

I updated my original post to make if more clear. Hope this helps better explain what I am trying to do. Any help would be greatly appreciated.

Thank you.
Yogesh

khuslid

Haha. Just as I was about to come up with a suggestion, Steve (the genious) beat me to it.
But there are other solutions as well.
If you really want to make sure they don’t access other contries, you could put the filter in the security filter of the table 1
LOOKUP(USEREMAIL(), Table2, Email, Contry) = [Contry]

Yogesh_Patel

Thank you Khuslid.

Adomseth

I have been looking for a solution like this for a long time. Thank @Yogesh_Patel . I'm most grateful

khuslid

If you want to show a list after filtering, you could add a slice.

Steve

Try this as the slice row filter expression:

ISNOTBLANK(
  FILTER(
    "Volunteers",
    AND(
      ([Email Address] = USEREMAIL()),
      OR(
        IN("ALL", [Teams]),
        IN([_THISROW].[Country], [Teams])
      )
    )
  )
)

Make appropriate table and column name substitutions. I’ve assumed the Teams column of the Volunteers table is an EnumList.

See also:

Yogesh_Patel

Thank you Steve. This is perfect. Works exactly as intended.

BenCarney

Not sure if this is right to post here since it's solved, but it's the same question/solution, I'm just not quite getting the results.

My tables:

Content - has a Content Name column and a Topic column

Responsibiliities - has a Topic column and Responsible column (email).

I have the below filter on a slice for my Content table, but it's only filtering by Topic in the responsible table ie. if the topic is there, it will show in the results, and doesn't also filter by the email address assigned to that topic.

I tested this by adding a topic to the Responsibilities table, without assigning it to anyone, and the Content rows with that topic became visible.

This is my filter for my Content slice:

ISNOTBLANK(FILTER("Responsibilities",AND(IN(USEREMAIL(),Responsibilities[Responsible]
),IN([_THISROW].[Topic],Responsibilities[Topic]))))

Steve

Please start a new topic for help with this.