Filter gallery based on lookup (combobox)

[Anonymous]

Hi all, 

Similar questions to this have been asked many times but I can't find anybody who has this exact problem. 

The short version: 

I have a gallery that is pulling from one datasource (story table) with a few fields that are a lookup on another datasource (customers table). 

I want to be able filter this gallery on BOTH fields from the story table AND fields from the customer table and I can't figure out how.

Long version:

I have, to simplify, two tables -- a story table and a customer table. One customer can have multiple stories.

They are connected as follows (there are more fields than this but I have removed them for simplicity): 

I have successfully put together a "View all stories" page where you can see information on each story, including customer name and industry (pulled in from the customer table with a LookUp).

It looks like this: 

I'm using test data so hopefully there are some fellow Harry Potter fans out there. 

For everything in the gallery on the lefthand side, so far so good. 

The gallery Items property currently filters the stories table, using the two comboboxes on the bottom right that give the values for lifecycle step and admin status (active/archived).

Filter('[dbo].[tblStory]', 
CurrentLifeCycleStep in StoriesLifecyleComboBox.SelectedItems && 
AdminStatus in StoriesStatusComboBox.SelectedItems)

Those two comboboxes, whose values are fields in the stories table, work great -- hooray! 

For the two fields in the gallery that are stored in the customer table (customer name and industry -- code below is for customer name), I have updated that line's Text property to: 

LookUp('[dbo].[tblCustomer]', CustomerID = ThisItem.CustomerID, CustomerName)

Here's where I'm having troubles: how do I filter my gallery on customer name and industry (the 2 top right combo boxes), which are stored in the customers table, given that my gallery Items property is pulling from the story table? 

Assuming this is possible, I figure I would need to do some kind of lookup like I did in my Text properties to get the customer name and industry. 

Here's my best guess so far trying to get the customer name filter to work: 

Filter('[dbo].[tblStory]',  
Filter('[dbo].[tblCustomer]', CustomerID in '[dbo].[tblStory]'.CustomerID).CustomerName in StoriesCustomerComboBox.SelectedItems)

Unfortunately, it gives me an error that says "Invalid argument type. Cannot use Table values in this context." 

It seems to be objecting to my StoriesCustomerComboBox but that doesn't make any sense to me since the combo boxes work when I am not trying to mix fields from different tables. 

Does anybody have advice on how to go about this? Do you know if it's even possible to do what I'm asking? 

Thanks in advance for your help!

[Drrickryp]

Hi @Anonymous,

I think you can accomplish your goal using the GroupBy function and use a Flexible height subGallery inside of a flexible height gallery.

• Add a button and make its OnSelect property:  ClearCollect(colStoryTable,Groupby(StoryTable, "Customer_Name","Restofthedata"). 
• Add a Dropbox control with the items property: Customers, set the value property to Customer_Name. 
• Add a Flexible height Gallery with the Items property: Filter(colStoryTable,Customer_ID=Dropdown.Selected.ID. // This will be the primary filter. 
• Add a text box inside the gallery with the Text property: Customer_Name
• Add a second flexible height Gallery inside of the first gallery and adjust the size as appropriate to show the rest of the items  and set its Items property to Restofthedata.
• You can now filter the second gallery based using other controls as you would any other gallery.

[Anonymous]

hi @Anonymous can you please advise if the above response was helpful so we can review this as an accepted solution? 

Thank you.

@Anonymous

[Anonymous]

I've been busy on other parts of my app but I'll come back to this and let you know. 

[Anonymous · Accepted solution]

@Drrickryp, thanks for the suggestion!

I think I understand what you were suggesting, but I ended up doing it another way. 

In the end, I added a view to my SQL backend that joined my customer and story tables.

Once all the columns were in the same table, it was easy peasy to set up the gallery. 

This solution would be a little trickier if it were in an editform or something (views are read only -- you'd have to use Patch and a LookUp to write the edited values to the correct place) but for a simple gallery, it was very straightforward.