Filtering the gallery with the main list based on the second list with items

[Nowak]

Hi, 

I need help. 

I have an application where Notification are added based on the area, sub-area, status and department to which the application belongs. I don't have columns with people in my sharepoint list. I want logged in users who belong to a department and were responsible for a subarea to make a gallery where they will not have to select anything but automatically the gallery will filter requests based on the logged in user, subareas that belong to the user and department. 

I've heard something about filtering galleries based on the second list, where items will consist of people and sub-areas assigned to people, etc.

Could you help? Thanks

[VaidasG]

Hi @Nowak ,

As step 1, lookup logged in users department and set that as variable value varUserDepartment.

Step 2, use this variable as filter value for your notifications

Hope this helps

Regards

VaidasG

[Nowak]

Hi @VaidasG ,

How can i lookup looged users? Sorry, im begginer with this. First what i to do is create another list with users and department? Then look up as variable and then filter ? But what do with sub-area belong to the users?

Best Regards

Nowak

[VaidasG]

@Nowak 

basic User().Email will return you logged in user email, same way you can get full name...

it is up to your logic how you link current user with department in your SharePoint list. if your intent is to use AD users properties, then following formula would help you with retrieval of current user Department:

First(Office365Users.SearchUser({searchTerm:User().Email})).Department

You can use this value as your filter parameter... 

p.s. make sure you include Office365Users as your apps data source

Hope this helps

Regards

VaidasG

[Nowak]

This does not work unfortunately.

I have a problem that I have to file the gallery based on the logged in user, who will be responsible for subareas and additionally in which department they work. My departments are divided into A/B/C/D shifts, health and safety and maintenance, the OHS department is responsible for all places, maintenance is responsible for places depending on the person, A/B/C/D changes are the same as maintenance. 

I can not assign specific people to subareas and the department to which they belong. For example, that Name.Surname@email.com is responsible for Subarea1, Subarea2, Subarea...., and department A. So when this person logs in to the application, the gallery automatically filters items to him based on this data. In my gallery all columns are of the choice type and I do not have in adding more items to whom the item should be assigned.

[VaidasG]

@Nowak 

If you are not using AD department in you logic, can you provide more info as to where your User & Department data/ relationship is stored?  Then we can look into using that logic in filter parameters

[Nowak]

Hi,

I don't have any relationships set up at the moment. I have only one list with choice columns and there is data about assignment, area and subarea.

[Nowak]

Sorry @VaidasG 

At the moment, to filter the gallery, I use the function IF(user().email = "email@dad.com" , filter(database, subarea exactin ["xxx","xxx1",etc], "department allocation" exactin "department" )) and its work but I have about 30 users from different "department allocation" and different subareas. I have about 100 of them. I don't want to use hardcoding, besides using this feature limits gallery and app.

[VaidasG · Accepted solution]

hi @Nowak 

To prevent hardcoding each of your mentioned users and their department, you need to turn user/department relationship into some data source. Since you are using SharePoint lists as your data source, why don't you create another "work" list UsersDepartament with just 2 columns as min (something like this):

• User1 - DepA
• User1 - DepB
• User2 - DepA
• User2 - DepC

This can be more advanced, by using multiselect choice column, additional list for departments and users etc... just these options would require additional set-up, but it's up to you which path to take...

Then in your app, you can do a quick lookup for all departments for current user, and use that value in your main list filter...

Regards
VaidasG

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[Nowak]

Okey, thanks, @VaidasG 

I understand, in addition to assigning specific subareas to users, create another list and define it similarly.

Best regards

Nowak