GroupBy a column inside of a Gallery which is filtered for items in a document set

[CarlHRVA]

Hello,

I have a SharePoint Document Library connected to a PowerApp where users submit forms with a flow that then creates Word documents and places them inside of a new or existing document set. From within the canvas app, on my gallery screen, I have two different galleries. One which displays all of the document sets in the library and another which displays the items inside of the selected document set. Here's how I achieve this:

First I got the content type ID for a document set from within SharePoint. Then I set the "Items" property of Gallery 1 to be:

Sort(Filter(IDIQ, 'Content type'.Id = "0x0120D5200014309F77AEBA6E408F5250D282F52EE400967F3B61EBB6814280D77FB6356E7A47"),Descending)

Then I set the OnSelect property for the Icon and Label of Gallery 1 to be:

Set(fullPath,Concatenate(ThisItem.'Folder path',ThisItem.'File name with extension',"/"))

Then I take this "fullPath" variable and use it in Gallery 2. Here I set the Items property to be:

Filter(IDIQ, 'Folder path' = fullPath)

This is working great and I am able to see all of the documents relative to each separate document set. What I am struggling with is I would like to group the items in Gallery 2 based on a column called "Form Type" and essentially mimic the way that I am able to group items within a document set in SharePoint. Instead of the user just seeing a list of all of the documents in a particular document set, they will see them grouped by form type and be able to expand or contract each and see any documents tagged as such, something very similar to the below picture. Thanks as always for any and all help. @wyotim any idea?

[wyotim · Accepted solution]

Hey @CarlHRVA, sounds like a great document solution! I do have an idea for how to accomplish what you are wanting. What I would suggest is using nested galleries and some formatting trickery. Essentially, nest a gallery within a gallery and make it show the relevant items when their type is selected.

For Gallery 2 (which will need to be a flexible height gallery), I would set the items to show the distinct list of the values in Form Type, which I am assuming is a text field. The aptly named Distinct function will do this wonderfully. Something like this should do the trick:

Distinct(
    Filter(
        IDIQ, 
        'Folder path' = fullPath
    ), 
    'Form Type'
)

 You can apply a sort to this to make the list alphabetical if you like. One thing to know about the Distinct function is that it creates a single-column table with the column being named Result. Just a good thing to know if you aren't familiar with this function. 

To create the clickable label like in your picture, you could put in the arrow icon and a text label with:

"Form : " & ThisItem.Result

We'll add the counter a bit later. 

Next, I would insert a gallery into Gallery 2. This gallery will be a filtered list, using the Result value like so:

Filter(
    IDIQ, 
    'Folder path' = fullPath,
    'Form Type' = ThisItem.Result
)

 Again, you can apply a sort to this as needed. 

To get a count like in your example, you could count the rows in that gallery (we'll call it Gallery 3) and append that to the label from above, like so:

"Form : " & ThisItem.Result & " (" & CountRows(Gallery3.AllItems) & ")"

You would then need to show and hide Gallery 3 based on the selected item in Gallery 2. The easiest way to do this would be to have a local variable that holds the selected Form Type and show Gallery 3 if it matches. Doing this basically makes it so only one list can be expanded at a time. You can also make it so if the selected Form Type is selected again it clears out the value, collapsing the list. Something like this:

UpdateContext(
    {
        varSelectedFormType: If( 
            varSelectedFormType = ThisItem.Result,
            "",
            ThisItem.Result
        )
    }
)

Then set the height of Gallery 3 to something like:

If(
    varSelectedFormType = ThisItem.Result,
    CountRows(Gallery3.AllItems) * Gallery3.TemplateHeight,
    0
)

You can also make the visibility of Gallery 3 dependant on varSelectedFormType = ThisItem.Result for extra measure. This height formula coupled with the flexible height gallery for Gallery 2 will give the expand/contract effect. 

That should hopefully flesh out the idea but if not let me know! Nested galleries can be a pretty useful way to get functionality like this, though there can be issues that pop up. Again, let me know if anything needs clarification or if I messed up the code examples. They were all from memory so I expect that I borked a few things in there! Cheers!

[CarlHRVA]

This is beautiful, thank you so much. I will say that I didn't get the Height part of it working but I used the visible property instead and it seems to be working great so I gave up rather quickly on it. Thank you again! 

[wyotim]

Glad it worked out despite the Height issue and that you were able to work around it! Always happy to try to help out! 👍