Filter a gallery based on the result of the Filter on another table

[DavideMitidieri]

• I have a table Table1 with the key column CP.
• I have another table Table2 with the key column ID and two more columns, CP and Code.
• I have a main gallery in the app with the data source from Table1 table.

I need to filter the gallery with these conditions:

• user input sInput string is part of the Code field of the Table2 table (this filter will output a list of rows from which I need only the CP values):
• filter the records of Table1 based on the records found in the condition above, matching the CP of the rows found with the CP field of the Table1 table.

I tried with this code in the OnChange event of the text bar:

   ClearCollect(
        FilterRange;
        Distinct(
            Filter(
                'Table2';
                sInput in Code
            );
            CP
        )
    );;

And then setting the Items of the gallery this way:

Filter(
   Table1;	
   CP in FilterRange
); 

The main issue with this code is that I lose the delegation (even though Power Apps isn't warning me), since the ClearCollect function is not delegable. This is really bad, since I can only find the first n records of Table2, where n is the delegation limit (and I have more than 2000 records which is the maximum delegation limit allowed by Power Apps).

Is there a way to achieve the same result and maintain the delegation at the same time?

Thank you in advance.

[DavideMitidieri]

I've also tried this code directly in the Items property of the gallery:

Filter(
   Table1;
   CP in Filter(
      'Table2';
      sInput in Code
   ).CP
);

but I get the error that the Table2 is not accessible in this context.

[mmbr1606]

hey @DavideMitidieri 

try this in the items property of the gallery:

Filter(
    Table1,
    LookUp(
        Table2,
        Code = sInput && CP = Table1[@CP]
    ) <> Blank()
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[DavideMitidieri]

I get the error "The specified column is not accessible in this context", when hovering on the line starting with "Code". It's the same error that I got with the code I posted in the second message.

I'm using Dataverse tables as data sources, I don't know if this could be causing these errors.

[mmbr1606]

Assuming CP in Table2 is a direct column (not a lookup), and you have the correct logical names, the expression might look something like this:

Filter( Table1, LookUp( Table2, 'Code' = sInput && 'CP' = Table1[@'CP'] ) <> Blank() )

Replace 'Code' and 'CP' with the actual logical names of these columns in Dataverse.

[DavideMitidieri]

I was already using the logical names in Dataverse to reference the columns, but I'm still getting that error.