Filtered gallery of a collection with two lookup functions

[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.

[RogierE]

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)

[Santiago241]

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.

[RogierE · Accepted solution]

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)

[Santiago241]

Ty @RogierE  😁

[RogierE]

Did you find the difference? 

[Santiago241]

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