Filtering data based on the data in related table

[PavloKudryavtse]

Hi,

I have 3 tables: Products, OrderItems, and Orders. Product is the base entity. OrderItem includes foreign key (ProductId) to Products table. And it also includes foreign key for Order as the Order can have many OrderItems (OrderId).

I would like to create the search functionality where upon search input, the gallery, which has Orders table as a data source, is being filtered to entries that include order items with a specified product id (search input) that are contained in OrderItems table.

For example,

OrderItem 1 is: (Id: 1, ProductName: "Book, OrderId = 1, ProductId = 1) 

OrderItem 2 is (Id: 2, ProductName: "Journal", OrderId = 1, ProductId = 2)

OrderItem 3 is (Id: 3, ProductName: "Journal", OrderId = 2, ProductId = 2)

Let's say 1 is entered in search: Only Order with Id = 1 shall show. If 2 is entered, both Order with Id = 1 and Id = 2 shall be shown since they both contain OrderItems with ProductId = 2.

Logically, the code would like something like this:

Filter(Orders, Id = LookUp(OrderItems, ProductId = SearchInput.Text).OrderId)

And it works, but LookUp takes only the first element. Therefore, the question is: how can I make it work to display not just the first item but all of them?

Using Filter() instead of LookUp() doesn't work because in that case the comparison happens between particular Id and whole table which causes an error, of course.

[DylanSimons · Accepted solution]

Since you are using a gallery for this, you can change the Lookup() to a Filter() and use this as the datasource.

Gallery Items: Filter(OrderItems, ProductId = SearchInput.Text)

Then, in the gallery you can lookup the "OrderId", say you put a Label in the gallery, the Label Text value would be:

Label Text: LookUp(Orders,ID=ThisItem.OrderId).YourField

you can replace "YourField" with whatever you want to display in the text field (customer, order ID, amount, etc.).

alternatively, you can do all of this work in the datasource of the gallery with a ForAll():

Gallery Items: ForAll(Filter(OrderItems, ProductId = SearchInput.Text), LookUp(Orders, ID=Id))

Then it would be a normal gallery, inserting a label with

ThisItem.YourField

[PavloKudryavtse]

Thanks man, I thought of using ForAll, but could not find a wat to do it properly. It all works, and I am using the second option since I have additional filters.

The only thing that I had to change is ForAll(Filter(OrderItems, ProductId = SearchInput.Text), LookUp(Orders, Id = OrderId)), but that is logical. 

Much appreciated!