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

 

View solution in original post

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,754)