Setting a variable with a value from a Sharepoint List then filter a gallery with it

(0) Share

Report

Posted on by glitter1234567

125

Hi all,

I thought this should be simple but I am struggling with the formula.

I am trying to simply set a variable varUserDepartment with the staff members department as stored on a sharepoint list

i.e. Sharepoint List "Staff List" is as follows

Department (choice field) Name (people field)

Accounts Joe Bloggs

IT Janet Doe

In PowerApps - when Janet Doe logs in I want to set the varUserDeprtment to equal 'IT' (based on a "Staff List") and then set a Gallery (based on a "Projects List") to only show Projects where the department matches varUserDepartment

I am setting the value for the variable varUserDepartment on my onStart and getting this correctly populated (tested with just adding a text label on my screen).

This is the Items property of my gallery - I can't get to work (I have some filter options - these are working correctly on their own)

SortByColumns(
Filter(
Search('Staff Projects',txtSearchName_2.Text, "Full_x0020_Name"),
('Review Status' = cmbSearchStatus_2.Selected.Value || IsBlank(cmbSearchStatus_2.Selected.Value)) &&
('Department' = varUserDepartment || IsBlank(varUserDepartment))),
"Review_x0020_Status", SortOrder.Ascending);

Can anyone assist me please 🙂

Thanks so much

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

JeffreyGonzalez 61 on at

Like (0)

Report
If I am understanding correctly what you are trying to accomplish, you may want to look into the Office365Users.UserProfilev2() Power-fx function in conjunction with the User() function a simple way to do this would be to do the following in the onstart property of your app:
Set(varUserDepartment, Office365Users.UserProfileV2(User().Email).department)

Was this reply helpful? Yes No
glitter1234567 125 on at

Like (0)

Report

Hi Jeffrey,

Many thanks for your reply - my set varUserDepartment is working fine on the onstart. It's the filter on the gallery that I can't get right 🙂

Thanks
Karen

Was this reply helpful? Yes No
JeffreyGonzalez 61 on at

Like (0)

Report
Once you get the department set, you will want to do the following to get the data into a gallery:
ClearCollect(colGalleryItems, Filter('SharePoint List Name', Department = varUserDepartment))
Try plugging the collection into your formula in place of the SharePoint list data source.

Was this reply helpful? Yes No
JeffreyGonzalez 61 on at

Like (0)

Report

I am going to set this up as similar as I can on my end and recreate. In the meantime, let me know if collecting the data into a collection and filtering/sorting that helps.
Are there any errors that pop up when you try to filter like that?

Was this reply helpful? Yes No
glitter1234567 125 on at

Like (0)

Report

So is colGalleryItems a collection - where do I set that? Sorry I haven't used collections before - I am new to PowerApps and learning on the job!

Was this reply helpful? Yes No