Filtering Gallery on Two Tables

(0) Share

Report

Posted on by stephenkln

163

Hey all, I'm having trouble coming up with a solution for a gallery filtering problem I have.

I have a search box for my gallery and my gallery items property is set to: If(IsBlank(SearchInput.Text),Filter(Assignments,UserID='User List'.Selected.UserID),Filter(Offices, SearchInput.Text in OfficeName))

The gallery item text labels are set to LookUp(Offices,ID=ThisItem.OfficeID,OfficeName)

So the gallery is showing office assignments based on a selected user in another gallery and displaying the name of the office rather than it's ID. The problem I am having is in the first formula:

If(IsBlank(SearchInput.Text),Filter(Assignments,UserID='User List'.Selected.UserID),Filter(Offices, SearchInput.Text in OfficeName))

If the search box is blank, then show office assignments for the selected user, otherwise filter by whatever is in the search box. I need a way to include the Filter(Assignments,UserID='User List'.Selected.UserID) in the second half of this formula. Currently, when you do a search it will return all office names regardless of if they are assigned to the user. So I need to be able to filter the list based on the search term, but only show results if they are assigned to the userID. Hope that makes sense.

Any help is appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

JR-BejeweledOne 5,836 Moderator on at

Like (0)

Report

Try something like this.

If(
IsBlank(SearchInput.Text),Filter(Assignments,UserID='User List'.Selected.UserID),
Filter(Filter(Offices, SearchInput.Text in OfficeName), Assignments,UserID='User List'.Selected.UserID)
)

Was this reply helpful? Yes No
stephenkln 163 on at

Like (0)

Report

Hey @JR-BejeweledOne thanks for the response. That did not work unfortunately.

I'm getting an error on the source portion of the second filter function..
Filter(Filter(Offices, SearchInput.Text in OfficeName),Assignments,UserID='User List'.Selected.UserID))

"The function 'Filter' has some invalid arguments. Expected boolean. We expect a boolean (true/false) at this point in the formula."

I tried changing the formula to Filter(Filter(Offices, SearchInput.Text in OfficeName),Assignments.UserID='User List'.Selected.UserID)) but then I get an "invalid argument type" error on the "=".

Was this reply helpful? Yes No
JR-BejeweledOne 5,836 Moderator on at

Like (0)

Report

What is your data source for the gallery?

Was this reply helpful? Yes No
stephenkln 163 on at

Like (0)

Report

@JR-BejeweledOne Several excel spreadsheets in OneDrive.

Offices is basically:

OfficeID OfficeName
1 A
2 B

Assignments is:

AssignmentID UserID OfficeID
1 1 1
2 1 2
3 2 1

Was this reply helpful? Yes No
JR-BejeweledOne 5,836 Moderator on at

Like (0)

Report

I think you will need to do something like this.

First set a variable to the user id when it's selected from the User List. I have used varUser for that here.

then something like this.

If(
!IsBlank(SearchInput.Text), Filter(Offices, OfficeID in Filter(Assignments, varUser in UserID).OfficeName And SearchInput.Text in OfficeName),
Filter(Assignments,UserID='User List'.Selected.UserID)
)

I had to do something similar yesterday

Filtering items in a gallery where the category matched the assigned category for the current user in another list. Mine had some multi-selects, but it looks like this:

Was this reply helpful? Yes No
stephenkln 163 on at

Like (0)

Report

@JR-BejeweledOne I think this got me closer, but still having trouble...

If(IsBlank(SearchInput.Text),Filter(Assignments,UserID='User List'.Selected.UserID),Filter(Offices, OfficeID in Filter(Assignments, varUser in Assignments.UserID).OfficeName And SearchInput.Text in OfficeName)
)

OfficeName is not valid at that point in the formula, it's only letting me select from the Assignments table for the value at that point.

Was this reply helpful? Yes No
stephenkln 163 on at

Like (0)

Report

Bump. Anyone have any other suggestions on how to get this gallery to do what I need it to do? Currently, my items formula is:

If(IsBlank(SearchInput.Text),Filter(Assignments,UserID='User List'.Selected.UserID),Filter(Assignments,UserID='User List'.Selected.UserID && SearchInput.Text in Filter(Offices,OfficeID in Assignments.OfficeID).OfficeName))

I've also tried

If(IsBlank(SearchInput.Text),Filter(Assignments,UserID='User List'.Selected.UserID),Filter(Assignments,UserID='User List'.Selected.UserID && SearchInput.Text in LookUp(Offices,OfficeID in Assignments.OfficeID,OfficeName)))

Neither produces the behavior I need. I need the gallery to display all associated items for the selected user if there is nothing in the search box. Otherwise I need the gallery to filter to any items assigned to the selected user that contain the search text.

Was this reply helpful? Yes No
WarrenBelz 153,049 Most Valuable Professional on at

Like (0)

Report
Hi @stephenkln ,

This is only some syntax guidance to assist - you may need something like this

With( { wOfficeID: Filter( Offices, OfficeID in Assignments.OfficeID ).OfficeName }, Filter( Assignments, UserID='User List'.Selected.UserID && If( IsBlank(SearchInput.Text), Filter( Assignments, SearchInput.Text in wOfficeID ) ) ) )

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

stephenkln 163 on at

Like (0)

Report
I finally figured it out. I used Filter in combination with ShowColumns and AddColumns to basically create a new virtual table in PowerApps that had all three of my filter attributes (UserID, OfficeID, OfficeName) in the same place.

If(IsBlank(SearchInput.Text),Filter(Assignments, UserID='User List'.Selected.UserID), Filter(ShowColumns(AddColumns(Assignments,"OfficeNameLookup",LookUp(Offices,OfficeID=Assignments[@OfficeID],OfficeName)),"AssignmentID","UserID","OfficeID","OfficeName"),UserID='User List'.Selected.UserID && SearchInput.Text in OfficeName))

Was this reply helpful? Yes No