Filter table with another table

(0) Share

Report

Posted on by darry3ni

I have two tables that I need help with filtering a gallery.

Table 1 has a column called Employee Name with a list of records, example "Nicky Thomas".

Table 2 has a similar column called fullname and with almost all the records being the same, except example "Nicky Thomas-Jones".

Filtering Table 1 by Table 2 does not return "Nicky Thomas" with an in operator.

Is there any way to get the filter to work?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

DylanSimons 326 on at

Like (1)

Report

How large are your tables? In general this will be a resource intensive operation and will take a long time, it essentially needs to go through every item in table 2 and use an "in" operator for each and every item in table 1. Can this be done in Power Automate and stored in another table instead of being done in Power Apps?

I can try to come up with a solution if this is necessary in the App.

Was this reply helpful? Yes No
darry3ni 16 on at

Like (0)

Report

As of now the tables have at most 20 records in each and yes it needs to be done in the app because the gallery items shown is based on the user.

Was this reply helpful? Yes No
DylanSimons 326 on at

Like (0)

Report

Maybe I misunderstood your question. You want to filter Table 2 with only 1 item from Table 1? As in you have a gallery with items from Table 1 and when selected this filters Table 2 with the selected item from Table 1?

In that case you can use the "in" operator like this:
Filter(Table2, Gallery.Selected.EmployeeName in FullName)

Did you try this and it doesn't work?

Was this reply helpful? Yes No
darry3ni 16 on at

Like (0)

Report

Oh no, I think you had the right idea the first time.
At the moment I'm using:
Filter(Table1, 'Employee Name' in Table2.fullname)

I'm only using the gallery to display the items and not for selection of an item.

- The issue is that in Table2 some entries have hyphenated names and are not showing the Table1 matching record in the gallery.

Was this reply helpful? Yes No
Verified answer

DylanSimons 326 on at

Like (2)

Report
Okay, so this function should work if I understand what the question.

ForAll(Table1, Collect(NameCollection, Filter(Table2, EmployeeName in FullName).FullName))

It goes through the EmployeeName column on table 1 and uses that to filter EmployeeName on Table 2. Then, it collects the results in a collection(NameCollection here).

This is in the OnVisible property of the screen. This works for me with having multiple matches as well as the example you listed.

Was this reply helpful? Yes No