Answered

Filter Lookup Dropdown list based on values in another SharePoint list

(0) Share

Report

Posted on by tsparkman

Hi There – I’m hoping someone can help me with this.

In my PowerApp, I have a form for users to complete a timecard. The form contains a single-select drop-down field called HourType which is a lookup to another SharePoint list called CSHourTypes. This lookup works just fine.

However, I want to filter the options in the drop-down to only display HourTypes that the user is permitted to bill against.

I store the logged in user’s email address in a global variable called gbl_User and am trying to lookup the user in the CSUsers SharePoint list where the EmailAddress field = gbl_User and get the values in the CSHourTypesAllowed field which a multi-select field.

Then I want to populate the Items in the drop-down to only display matching hour types.

I've tried a couple of things along the lines of the following, but can't seem to get it to work. Any help or insight is greatly appreciated!

Filter(CSHourTypes, Title in LookUp(CSUsers, EmailAddress = gbl_User, HourTypesAllowed.Value), "Title")

CSHourTypes SharePoint List

Title
ID

CSUsers SharePoint List

EmailAddress
CSHourTypesAllowed

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

WarrenBelz 155,962 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @tsparkman ,

I am not sure if I am "undercomplicating" this, but if your have the user stored in a Variable and want a list of hour types, this will get it

Filter( CSUsers, Title = gbl_User, ).CSHourTypesAllowed

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Verified answer

tsparkman 99 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi Warren - thanks so much for your response!

Unfortunately this didn't work for me, but it did get my mind thinking in a different direction -- so THANK YOU!

I was able to solve it by:
Changing the field from a drop-down to a combo-box. (Something I read made it sound like some of the things you can do with combo-boxes simply can't be done with drop-downs.)
Then, in my Items property I added the following which basically filter the HoursTypes list to only display items that are in the HourTypesAllowed field on the user profile.
Filter(CSHourTypes, Title in LookUp(CSUsers, EmailAddress=gbl_User, HourTypesAllowed.Value).Value)
3. Then I set the DefaultSelectedItems to this, so it would default to the users Preferred HourType.
LookUp(CSUsers, EmailAddress = gbl_User, PreferredHourType)

There might have been a different/easier/more efficient way to do this, but it was the only thing I could get to work 😀

Thanks again for your guidance and setting me on a different path!

Was this reply helpful? Yes No