Filter gallery based on the content of another table

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

[ShaheerAhmad]

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.

[developerAJ · Accepted solution]

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

[LapisLazuli]

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!

[LapisLazuli · Accepted solution]

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?