cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hvaughan98
Level: Powered On

Filtering joined tables

I am trying to join to SharePoint lists into a gallery to show a list of devices that are available for checkout and add a search box at the top to avoid having to scroll through the list. It is connecting a list of inventory and a list of checkouts. I have the syntax correct for the functions individually, search box,

SortByColumns(Filter('Help Desk Checkouts', StartsWith(Name, SearchBox_1.Text)), "Title",
If(SortDescending1, Descending, Ascending)

joining,

ShowColumns(AddColumns('Help Desk Inventory ', "id", LookUp('Help Desk Checkouts ', Availability.Value = "Available" && 'Service Tag # ' = 'Help Desk Inventory '[@ServiceTag_x0023_] , ID)),"Title","Condition","id", "ServiceTag_x0023_", "DeviceType")

and filtering.

Filter('Help Desk Checkouts ', Availablility.Value = "Checked Out")

However, when I try to join the tables, add the filter and search box I am having trouble. I tried to put an & or + between the formulas but that gave me an error. Any help would be much appreciated. I can provide more information if needed. 

1 ACCEPTED SOLUTION

Accepted Solutions
EricLott
Level 10

Re: Filtering joined tables

To make life easier, try collecting the join like this:

ClearCollect(MyJoinedTables,
ShowColumns(AddColumns('Help Desk Inventory ', "id", LookUp('Help Desk Checkouts ', Availability.Value = "Available" && 'Service Tag # ' = 'Help Desk Inventory '[@ServiceTag_x0023_] , ID)),"Title","Condition","id", "ServiceTag_x0023_", "DeviceType")
)

Then you can filter it and search it easier

Filter(MyJoinedTables,Availability.Value="Checked Out")
SortByColumns(Filter(MyJoinedTables, StartsWith(Name, SearchBox_1.Text)), "Title",
If(SortDescending1, Descending, Ascending)

 Remember when you Refresh() your data sources, you'll need to ClearCollect() the MyJoinedTables again.

View solution in original post

6 REPLIES 6
EricLott
Level 10

Re: Filtering joined tables

To make life easier, try collecting the join like this:

ClearCollect(MyJoinedTables,
ShowColumns(AddColumns('Help Desk Inventory ', "id", LookUp('Help Desk Checkouts ', Availability.Value = "Available" && 'Service Tag # ' = 'Help Desk Inventory '[@ServiceTag_x0023_] , ID)),"Title","Condition","id", "ServiceTag_x0023_", "DeviceType")
)

Then you can filter it and search it easier

Filter(MyJoinedTables,Availability.Value="Checked Out")
SortByColumns(Filter(MyJoinedTables, StartsWith(Name, SearchBox_1.Text)), "Title",
If(SortDescending1, Descending, Ascending)

 Remember when you Refresh() your data sources, you'll need to ClearCollect() the MyJoinedTables again.

View solution in original post

hvaughan98
Level: Powered On

Re: Filtering joined tables

Do I do the formula to create the join and set the value of the ClearCollect in the gallery under items? It gives me an error message that says to move the behavior function to a behavior-based property, im not sure where else to put it. 

hvaughan98
Level: Powered On

Re: Filtering joined tables

@EricLott 

Okay, I got the clear collect put into the OnVisable box for the screen. But when I am trying to do the filter, it's not pulling the items from one of the lists. I'm not sure what I am doing wrong. 

EricLott
Level 10

Re: Filtering joined tables

What items are being left off? Items from the join, or from the collection?

hvaughan98
Level: Powered On

Re: Filtering joined tables

@EricLott I figured it out, it was a dumb error on my part. The filtered items weren't showing but it was because the collection wouldn't refresh unless I refreshed the page. Thank you so much for helping! 

EricLott
Level 10

Re: Filtering joined tables

You're welcome! If you're good to go, please mark your thread as solved.

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,831)