Unanswered

Filter Choices in dropdown based of column in another table in a form

(0) Share

Report

Posted on by kylerw

I have a drop down in my form that looks up and ID from a sharepoint list (Employee Info) and then concatenates the name associated with the ID and puts it in the drop down. What I would like to do filter the names and id in the drop down to only people in the employee info list that have a status of active.

This is my current working code

Sort(AddColumns(Choices('Attendance Tracker List'.TeamMemberID), "Employee Name", (LookUp('Employee Info', ID=Id, TeamMemberName & " | " & TeamMemberID))), 'Employee Name')

If It could be like this somehow that would be good, not sure where to exactly put the filter

Sort(AddColumns(Filter(Choices('Attendance Tracker List'.TeamMemberID),'Employee Info'.Status = "Active"), "Employee Name", (LookUp('Employee Info', ID=Id, TeamMemberName & " | " & TeamMemberID))), 'Employee Name')

Any help is appreciated thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Shaheer Ahmad 2,194 Moderator on at

Like (1)

Report
Copy link

Link copied!
To filter the choices in your dropdown based on the "Status" column in the "Employee Info" SharePoint list, you can modify your formula as follows:

Sort( AddColumns( Filter( Choices('Attendance Tracker List'.TeamMemberID), LookUp('Employee Info', ID = Result).Status = "Active" ), "Employee Name", Concatenate('Employee Info'.TeamMemberName, " | ", 'Employee Info'.TeamMemberID) ), "Employee Name" )

In this formula:
The Filter function is used to filter the choices based on the "Status" column in the "Employee Info" list. It retrieves only the choices where the associated employee's status is "Active".
The AddColumns function adds a new column named "Employee Name" to the filtered choices. The column value is generated by concatenating the employee's name and ID.
The Sort function sorts the choices based on the "Employee Name" column.
Make sure to replace Result in the formula with the appropriate identifier that represents the selected ID in your form.
By using this modified formula, the dropdown will only display the names and IDs of active employees from the "Employee Info" list.

Was this reply helpful? Yes No
kylerw 28 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your response, when I try this I get an error when using the filter.
Any reason this might be happening and what could be the workaround?

Was this reply helpful? Yes No