Filtering Gallery on Two Tables

[stephenkln]

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!

[JR-BejeweledOne]

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)

)

[stephenkln]

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 "=".

[JR-BejeweledOne]

What is your data source for the gallery?

[stephenkln]

@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

[JR-BejeweledOne]

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:

[stephenkln]

@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.

[stephenkln]

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.

[WarrenBelz]

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.

[stephenkln · Accepted solution]

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))