Filtered gallery of a collection with two lookup functions

(0) Share

Report

Posted on by Santiago241

Hi all,

I have 2 important collections,

base_collection

Base Collection
Material	System	Quantity
Material-1	System-1	40
Material-2	System-2	40
Material-3	System-2	40
Material-4	System-3	40
Material-1	System-2	40
Material-6	Equipo-7	40

which is where I get all the data, search_collection,

Search Collection
Material	System	Quantity
Material-1	System-1	35
Material-2	System-2	35

a collection that the user inputs and contains the data they are looking for in the base_collection.

What do I need? I need a gallery that filters the combination of material and system from the search_collection and searches for it in the base_collection, and then displays the filtered gallery:

Filtered Gallery
Material	System	Quantity
Material-1	System-1	40
Material-2	System-2	40

I tried using this code:

Filter(
 base_collection,
 And(
 MasterID.ECAPP_Hdr_opt1 = GalleryHdrDetails_2.Selected.ECAPP_Hdr_opt1,
 item_material = LookUp(
 search_collection,
 Row_CDMaterial = base_collection[@cr56a_material],
 item_material
 ),
 item_system = LookUp(
 search_collection,
 Row_system = base_collection[@cr56a_equipo],
 item_system
 )
 )
)

, but it only gives me this gallery:

Actual Gallery
Material	System	Quantity
Material-1	System-1	40
Material-2	System-2	40
Material-3	System-2	40
Material-1	System-2	40

What I understand is that it searches for either the same material or the same system, but what I need is for it to search for the unique combination of material and system.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

RogierE 875 Moderator on at

Like (0)

Report
I guess the approach with filling a Collection would get you a better outcome:

Clear(colOutcome);
ForAll (colSearchCollection,
Collect(colOutcome,Filter(colBaseCollection,Material=colSearchCollection[@Material],System=colSearchCollection[@System])))

Please LIKE the reply and ACCEPT it as the solution if the answer is what you were looking for.
(to help me grow in community rank and help others find the solution)

Was this reply helpful? Yes No
Santiago241 30 on at

Like (0)

Report

Hi @RogierE

This is colBaseCollection
This one is colSearchCollection

and this one is colOutcome
I created the collection using the code you recommended, but I'm getting the same result :(.
I don't know how to solve this problem.

Was this reply helpful? Yes No
Verified answer

RogierE 875 Moderator on at

Like (1)

Report
Hard for me to check what you did wrong if you only share the outcome 🙂

Before I sent you the suggestion, I checked to see if it worked. I created a mockup App with only a single button, and this piece of code in the OnSelect:

ClearCollect(colBaseCollection,
[{Material:1, System:1, Quantity:40},
{Material:2, System:2, Quantity:40},
{Material:3, System:2, Quantity:40},
{Material:4, System:3, Quantity:40},
{Material:1, System:2, Quantity:40},
{Material:6, System:7, Quantity:40}]);

ClearCollect(colSearchCollection,
[{Material:1, System:1, Quantity:35},
{Material:2, System:2, Quantity:35}]);

Clear(colOutcome);

ForAll (colSearchCollection,
Collect(colOutcome,Filter(colBaseCollection,Material=colSearchCollection[@Material],System=colSearchCollection[@System])))

Works like a bliz, and gives the expected outcome.

Would it be an idea to add this full piece of code to a button, press it and see that it works as requested? Then maybe you can compare that with what you did exactly...

Please LIKE the reply and ACCEPT it as the solution if the answer is what you were looking for.
(to help me grow in community rank and help others find the solution)

Was this reply helpful? Yes No
Santiago241 30 on at

Like (1)

Report

Ty @RogierE 😁

Was this reply helpful? Yes No
RogierE 875 Moderator on at

Like (0)

Report

Did you find the difference?

Was this reply helpful? Yes No
Santiago241 30 on at

Like (1)

Report

In the end it was my own mistake because I didn't pass my collections correctly, but it's working now, thanks :).

Was this reply helpful? Yes No