Filter a lookup field

Like (0) Share

Report

Posted on 12 Nov 2024 20:35:49 by CatalinaBP

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.

Categories:

Using Apps Using Formulas

Connecting to Data

All responses (3)

Answers (0)

CatalinaBP 86 on 12 Nov 2024 at 23:39:07

Like (0)

Report

Filter a lookup field

WarrenBelz and Mark N

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 989 Super User 2025 Season 1 on 12 Nov 2024 at 22:16:40

Like (1)

Report
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 147,418 Most Valuable Professional on 12 Nov 2024 at 21:38:06

Like (1)

Report
Filter a lookup field

Hi CatalinaBP

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