Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Apps - Building Power Apps
Unanswered

Filter a lookup field

Like (0) ShareShare
ReportReport
Posted on 12 Nov 2024 20:35:49 by 86
Hi, all.
 
I've created a SharePoint form in PowerApps.  When an employee (DataCardValue46) is chosen, their title and department are auto-loaded using DataCardValue46.Selected.title and DataCardValue46.Selected.department.
 
I want to also be able to add the user's department head, which isn't an option for DataCardValue46. 
 
So, in the SharePoint list I have a lookup column to a second list which connects the department to the department head in a drop-down.
 
Back in PowerApps, is there a way to filter this look-up drop-down based on DataCardValue46.Selected.department?  I want to be able to submit the form and have the look-up drop-down set to the correct department, which should then trigger additional information from the look-up.
 
I realize this is convoluted, but I'm hoping there is a PowerApps Filter formula that can achieve this.
 
Thank you in advance.
  • CatalinaBP Profile Picture
    86 on 12 Nov 2024 at 23:39:07
    Filter a lookup field
     
    Maybe I didn't explain myself.
     
    I have one lookup column in SharePoint, that connects to a list with the department name, department head name, and department head email.  When I use the drop down to select a department in SharePoint, the other info populates.
     
    In the PowerApps form, I added the lookup column from SharePoint.  
     
    What I want is to choose a name from an employee drop-down that automatically chooses the correct department in the lookup column.
     
    I assume this is done by comparing the employee's department from DataCardValue46.Selected.department with the look-up field and filtering out incorrect choices?
  • Mark Nanneman Profile Picture
    989 Super User 2025 Season 1 on 12 Nov 2024 at 22:16:40
    Filter a lookup field
    If I understand correctly, you have two lookup columns on your Users list?  One is to a Department and another is back to the Users list (Department Head)?  And you're trying to auto populate the Department Head based on the selection of Department?

    Getting PowerApps to auto select or filter a lookup is relatively straightforward, however I run into issues when submitting the form.  Once you customize a DataCardValue control for a sharepoint lookup column in PowerApps it sometimes will not save.

    The only way I can get this sort of thing to work reliably is to do a Patch function instead of a Submit Form.  This allows for more control in any event.

    That said, here's how you could auto filter a lookup control on an OOTB form control.

    1. Select Department Head LookUp dropdown control.


    2. Unlock to edit:
     

    3. Check Items property
     
    Change it from "Choices..." to just "users" or whatever the lookup data source list is called.
     

    4. Edit the "DefaultSelectedItems" Property

    Replace the existing formula with something like this:
    With(
        {
            selDepartmentHeadId: LookUp(Department,ID = DataCardValue16.Selected.ID).'Department Head'.Id
            
        },
    LookUp(Users, ID = selDepartmentHeadId)
    )

    This formula just looks up the Id of the Department Head from the selected Department's record.  Then it saves that Id in an inline record ({selDepartmentHeadId: LookUp...}) and then we use that to lookup the User and set that User as the default selected item.

    Now whenever you select a department, the department head drop down will auto select the department head on the lookup of that department's record.
    Jim Bob auto populates Department Head after Operations is selected, because he is on the sharepoint record as Operation's Department Head.

  • WarrenBelz Profile Picture
    146,947 Most Valuable Professional on 12 Nov 2024 at 21:38:06
    Filter a lookup field
    Assuming you have Department and Head fields in the second list, it should be
    LookUp(
       SecondListName,
       DepartmentField = DataCardValue46.Selected.department,
       HeadField
    )
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)     Visit my blog Practical Power Apps    Buy me a coffee

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 89 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 60

#3
stampcoin Profile Picture

stampcoin 48

Overall leaderboard