cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PavloKudryavtse
Frequent Visitor

Filtering data based on the data in related table

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.

1 ACCEPTED SOLUTION

Accepted Solutions
DylanSimons
Resolver III
Resolver III

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

 

View solution in original post

2 REPLIES 2
DylanSimons
Resolver III
Resolver III

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

 

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!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,036)