One gallery with multiple list data

[kriggo15]

I have multiple SharePoint lists that I'm trying to wrangle into a single gallery. I've tried to lay out what I'm looking for in the included screenshot.

I have "parent" list called All Requests which houses a unique id - Request ID. This unique id is carried over to other "child" lists - Service Request and Rental Request. I also have a completely separate list called Consultation.

On one screen I want to display the "open" requests for the user from both the All Request and Consultation lists. When they click the arrow for a specific request, they would be directed to another screen where all related child requests would be displayed.

Not sure how to best tackle this one.

[mdevaney · Accepted solution]

@kriggo15 

To combine the All Requests and Consultation tables you would need to use a collection.  Put this code in the OnVisible property of your Screen 1.

ClearCollect(
    colAllRequestsConsultation,
    AddColumns('All Requests',"Source" "All Requests")
);
Collect(
    colAllRequestsConsultation,
    AddColumns('Consultation',"Source, "Consultation")
)

Similar code would go in the OnVisible property of Screen 2.

ClearCollect(
    colServiceRentalRequests,
    AddColumns('Service Requests',"Source" "Service Requests")
);
Collect(
    colServiceRentalRequests,
    AddColumns('Rental Requests',"Source", "Rental Requests")
)

Then put this code in these properties of the gallery in Screen 1.

Items: colAllRequestsConsultation
OnSelect: Navigate(Screen2)

And put this code in the Items properties of the gallery in Screen 2

Filter(colServiceRentalRequests, 'Request ID' = Gallery1.Selected.'Request ID')

Let me know if you have any questions.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[kriggo15 · Accepted solution]

For the first code:

ClearCollect(
    colAllRequestsConsultation,
    AddColumns('All Requests',"Source" "All Requests")
);
Collect(
    colAllRequestsConsultation,
    AddColumns('Consultation',"Source, "Consultation")
)

 I'm getting an error which doesn't appear until I start entering the Collect code.

[mdevaney]

@kriggo15 

Could you please share a screenshot of what the code looks like in the formula bar?  I want to see what is underlined in red.

[kriggo15]

'Service Request' and Consultation are my SharePoint Lists. I get the same error if I put single quotes around Consultation.

Note:I created a dummy version in my original post to anonymize some information so the names may be a little different.

[mdevaney]

@kriggo15 

Your SharePoint lists must have exactly all the same columns and column names for this to work.  I took your example quite literally 🙂

Please check this and let me know.

[kriggo15]

Sorry about the mix-up. They most definitely will not have the same columns. Some may have 5 fields where others may have 20. Column names may be shared across lists, but it will not be a 1:1.

Edit: The second Collection code for screen 2 worked fine even though those two SharePoint lists do not contain the same columns.

[mdevaney]

@kriggo15 

Some additional transforming of the collection for Table1 might solve the issue.  If not, I wonder if the columns have different data types in All Requests and Consultation.

ClearCollect(
    colAllRequestsConsultation,
    AddColumns(
        RenameColumns(
            ShowColumns('All Requests', 
                "Request ID", "Submitter", "Contact", "Notes", "Status"
            ),
            "Request ID", "ID"
        ),
    "Source" "All Requests")
);
Collect(
    colAllRequestsConsultation,
    AddColumns(
        RenameColumns(
            ShowColumns('All Requests', 
                "Request ID", "Submitter", "Contact", "Notes", "Status"
            ),
            "Consult ID", "ID"
        ),
    "Source" "Consultantion")
);

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[kriggo15]

I was able to get it to work without the additional transformation. I went back and made sure that all of my lists did not have required fields enabled. After I refreshed my data sources, it worked.

One last question before I wrap this up. Since I will have more than 2 lists on screen 2 that I'm pulling from, would I just the additional lists like below:

ClearCollect(
    colServiceRentalRequests,
    AddColumns('Service Requests',"Source" "Service Requests")
);
Collect(
    colServiceRentalRequests,
    AddColumns('Rental Requests',"Source", "Rental Requests"),
    AddColumns('Next Source1', "Source", "Source1 Requests"),
    AddColumns('Next Source2', "Source", "Source2 Requests")
)

[mdevaney]

@kriggo15 

Yes, your code would work just fine.  Your usage of multiple table in a single COLLECT statement shows a good understanding of the core concepts  🙂

I've created a guide to using collections that is downloadable for free from the community gallery.  There are several examples that might help you with future problems there.

Link To Collections Cookbook:

https://powerusers.microsoft.com/t5/Community-App-Samples/Collections-Cookbook-50-Visual-Examples-am...

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."