Compare 2 Sharepoint lists and show in a gallery in power apps.

(1) Share

Report

Posted on by besthannie

Hello everyone! I have a gallery with records from a list called Details and when performing an analysis, it is necessary that the records of this list are displayed based on the records of another list called Inventory, both lists have the No_Reg column in common but the records in this column must match the month and year, which is a problem since in the Details list there are two columns Month and Year in addition to the month being abbreviated (JUL) and in Inventory the column is called Period and is displayed this way (JULY 2024), finally for the records to appear in the gallery they must comply that this record appears in both lists and that if in the Inventory column called Total it has a value then that record appears otherwise it should not be displayed.
I have tried many things but they have not worked and I would greatly appreciate your help.

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Michael E. Gernaey 53,969 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi

So what I am hearing is

1. You have to match the No_Reg AND match the month and year

2. But the month and year are wacko in both tables (love to meet who decided that Data Model).

In one list, you have 2 columns month and year and the column type is what... Number? or String?

In the other you have Friendly Format Date (without the day) so is this a string column????

Here is a question before we go about writing some wacko stuff

1. Why would the month and date NOT match between the lines that match the No_Reg

If it were me, I would either

1) create a column in the first list, so it has the same Format as the second List

Do a one time, Flow run to populate it all

2) This is better IF the list 1 month/year are number (for delegation and performance reasons)

Add 2 rows to the second list, for month, year

Have a flow run and do a 1 time setting of the value.

Then change either (for whichever list that gets updated)

1) how the data gets in there to set these 2 values also

OR

Create a triggered Flow based on when a row gets created or modified (and we would filter it to the Month and Year changing columns)

and it would set the new column for you, so NO ui or other data intake changes have to happen

But now you have using numbers, that are wonderfully delegatable and fast.

Anything else I tell you would be a bad suggestion in the scheme of things and just something to tell you how to make it work. I believe in give you the right way to do it.

Was this reply helpful? Yes No
Suggested answer

venturemavenwill 1,193 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
The easiest way I would do is to create new collections using the AddColumns, and DateValue commands to add a unified date column before passing the collection into the galleries

Based on your description, this is what the transformations would look like

For the Details list:

ClearCollect( newDetailsList, AddColumns( oldDetailsList, 'DateField1', DateValue("01-"&Month&"-"&Year) ) )

For the Inventory list:

ClearCollect( newInventoryList, AddColumns( oldInventoryList, 'DateField2', DateValue("01-"&Substitute(Period, " ", "-")) ) )

You can then take the 'Total' column for the newInventoryList in the newDetailsList, and filter it like this

Filter( AddColumns( newDetailsList, 'Total', Sum(Filter(newInventoryList, DateField2 = DateField1), Total) ), Total>0 )

Let me know if this solves your issue.

Was this reply helpful? Yes No
besthannie 18 on at

Like (1)

Report
Copy link

Link copied!

Hi

@FLMike Unfortunately I can't make changes to the SharePoint lists and the columns that bring the dates are string type, and the lists don't match because one has more records than the other.

Was this reply helpful? Yes No