cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MEGA1
Helper I
Helper I

Combining multiple SharePoint Lists and displaying in a gallery

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
MEGA1
Helper I
Helper I

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

View solution in original post

3 REPLIES 3
WarrenBelz
Super User
Super User

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?

Hi again @WarrenBelz !

 

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

MEGA1
Helper I
Helper I

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (4,086)