cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tsparkman
Helper I
Helper I

Filter Lookup Dropdown list based on values in another SharePoint list

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
1 ACCEPTED SOLUTION

Accepted Solutions

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:

  1. 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.)
  2. 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! 

View solution in original post

2 REPLIES 2
WarrenBelz
Super User III
Super User III

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.

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:

  1. 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.)
  2. 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! 

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (75,248)