Answered

Combining multiple SharePoint Lists and displaying in a gallery

(0) Share

Report

Posted on by 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!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

WarrenBelz 156,003 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
MEGA1 66 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi again @WarrenBelz !

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

Was this reply helpful? Yes No
Verified answer

MEGA1 66 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No