Filter gallery based on the content of another table

(0) Share

Report

Posted on by LapisLazuli

Hi,

Context:

Data1 based on Excel table (Excel connector),

Data2 based on Sharepoint List (SP connector).

They contain similar elements:

Data1.ID = Data2.ArchiID
Data1.Name should be the same as Data2.Title
Data1.Documentation should be the same as Data2.Description

MyGallery will be based on a subset of Data1 (see 'Goal' below)

Goal:

In order to sync Data1 and Data2, MyGallery will first display Data1 items which differ in Data2, that is:

Data1.ArchiID = Data2.ID (same item)

Data1.Name <> Data2.Title
OR
Data1.Documentation <> Data2.Description

Attempt:

Filter(
 Data1,
 With(
 {D: LookUp(Data2, ArchiID = ThisRecord.ID)},
 Name <> D.Title Or Documentation <> D.Description
 )
)

Please note; the delegation warnings can be ignored. There won't be more than 500 records in the tables.

Result:

All items get displayed, not only the ones where either Name/Title or Documentation/Description differ.

Any hints?

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Shaheer Ahmad 2,194 Moderator on at

Like (0)

Report
To filter a gallery based on the content of another table, you can use the Filter function in Power Apps. Here's an example of how you can modify your code:

Filter( Data1, LookUp( Data2, ArchiID = ID ).Title <> Name Or LookUp( Data2, ArchiID = ID ).Description <> Documentation )

Make sure to replace 'Data1' and 'Data2' with the actual names of your Excel table and SharePoint list, respectively. Also, ensure that 'ArchiID', 'Title', 'Description', 'ID', 'Name', and 'Documentation' correspond to the correct column names in your data sources.
The modified code will filter the 'Data1' table based on the condition where either the 'Title' or 'Description' column values in 'Data2' differ from the corresponding 'Name' or 'Documentation' values in 'Data1'. It uses the LookUp function to find the matching record in 'Data2' based on the 'ArchiID' and 'ID' fields.
Once you have this code, you can assign the filtered result to the Items property of your gallery control to display only the items that meet the filtering criteria.

Was this reply helpful? Yes No
Verified answer

developerAJ 4,628 on at

Like (1)

Report
@LapisLazuli

i tried to do same like your logic but its working strangely.
as you said lists are very small . I would suggest using collection instead of live datasource.

Clear(colNotMatching); ForAll(Data2 As q,Collect(colNotMatching,First(Filter(Data1,ID=q.ArchiID, Name <>q.Title || Documentation <> q.Description))));
You can use above code onstart of screen and use colNotmatching as datasource of gallery

Please click Accept as solution and Thumbs Up. if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
LapisLazuli 59 on at

Like (0)

Report

Hi,

@ShaheerAhmad: thanks for taking the time to answer.
Please note, your code has exactly the same logic as mine. I'm also using Fiter() and LookUp(). The only difference is that I'm adding the With() function for conciseness: it replaces both occurence of Lookup(XXX, YYY) by "D".
So, as it could be expected, your code gives the same - wrong, unfortunately 😞 - result as mine.

@developerAJ
Thanks, very helpful:
- Using the collection the way you did it, starting first with Data2, then parsing the Data1 to locate non-matches - incidentally using First(Filter()) rather than Lookup() - now works fine. It solves the issue.
- Confirming that my code works on your side, gives also valuable information. There should be something wrong then, on my side, in the way the data is retrieved from Data1 and Data2. So records wrongly appear as no-matches. I'll investigate. In the meantime, your proposal perfectly does the job, thanks!

Was this reply helpful? Yes No
Verified answer

LapisLazuli 59 on at

Like (0)

Report
After investigations, the formula just needed to desambiguate "ID". Since Sharepoint always add an ID Field in the lists, the formula was wrongly picking this one up, instead of the one in Data1.

This almost identical code works fine:

Filter( Data1 As DT, With( {D: LookUp(Data2, ArchiID = DT.ID)}, DT.Name <> D.Title Or DT.Documentation <> D.Description ) )

@developerAJ : I suspect it worked on your side, because you didn't use "ID" as the field name in Data1, or didn't use a Sharepoint list as Data2. Correct?

Was this reply helpful? Yes No