Combining multiple SharePoint Lists and displaying in a gallery

[MEGA1]

Hi!

I have 7 SharePoint Lists that do not have the same fields (only the first few fields are the same), I have a category in each SP list that identifies or segregates each list.

I have to create a display of the data grouped by state / region.  

I tried a collection OnVisible, I see that the collection is created:

ClearCollect(AllSPLists,'2021_PART_C_GRV','2021_PART_C_ODR','2021_PART_C_P2P','2021_PART_C_REWARDS','2021_PART_C_SNP','2021_PART_D_CDR','2021_PART_D_GRV')

Then I created a gallery that groups the data and filters it by one of the states/regions I would like to see:  Doesn't return any rows.

Filter(SortByColumns(GroupBy(AllspLists, "SOURCE_ORG","REGION","CONTRACT_NUMBER","ListResults"), "REGION", Ascending, "CONTRACT_NUMBER", Ascending, "SOURCE_ORG", Ascending),"REGION"="CA")

My text doesn't display anything, assuming because something is wrong with my collection statement:  AllspLists.REGION & " (" & AllspLists.CONTRACT_NUMBER & ") - " & AllspLists.SOURCE_ORG

Help please!

[WarrenBelz]

Hi @MEGA1 ,

My first questions are how many records are in these lists in total and if you put CountRows(AllSPLists) on a label, what total do you get?

[MEGA1]

Hi again @WarrenBelz !

I have a total of 272 rows in the lists.  The CountRows(AllSPLists) returns more rows (341).

[MEGA1 · Accepted solution]

I think I may have figured it out.  Not clear on why it works but it does.

I changed the gallery to filter by REGION for a specific value to test: 

SortByColumns(Filter(GroupBy(AllspLists, "REPORT_SECTION","SOURCE_ORG","REGION","CONTRACT_NUMBER","ListResults"),REGION="CA"), "REPORT_SECTION", Ascending, "REGION", Ascending, "CONTRACT_NUMBER", Ascending, "SOURCE_ORG", Ascending)

And then I updated the text this way (without using the AllSPLists and it works, it displays data as expected:

ThisItem.REPORT_SECTION & " - " & ThisItem.REGION & " (" & ThisItem.CONTRACT_NUMBER & ") - " & ThisItem.SOURCE_ORG