cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
roli_
Level: Powered On

Filtering nested galleries

I have 3 Sharepoint datasources linked with a lookup ID field and struggling to find a way to link the sub gallery to the ID.

 

tbHeaders (id)

tbLines (id, FKtbHeaderID)

tbLineDetails (id, FKtbLineID)

 

EditForm1 bound to tbHeaders

Gallery - GalleryLines bound to CollectionLines

Subgallery - GalleryDetails bound to CollectionDetails

 

The button taking me into this screen looks like this: (note I have a variable CurrentHeaderID defined already)

EditForm(EditForm1); ClearCollect(CollectionLines, Filter(tbLines, FKtbHeaderID.value = CurrentHeaderID)); ClearCollect(MainCategoryDetails, GroupBy(LineDetails, "MainCategory" ,"Details"));

ClearCollect(CollectionDetails, AddColumns(MainCategoryDetails, "Hours", Sum(Details, Value)));

Navigate(EditScreen1, ScreenTransition.None)

 

Using this method, the header and gallery is filtered correctly with IDs. The subgallery shows the grouped data, but I can't get my head around a way to get this subgallery filtered to the parent ID. One question is how this should be structured. Another question is why the ID lookup fields from Sharepoint seems to be accessible in some functions but not in others.

 

For clarification, I'm trying to create a screen where the header is non editable, the gallery is editable running lines with a nested sub-gallery a number of details for each line.

 

I've been struggling with this for 20 hours now, and hope why explanation makes sense and that someone can point me in the right direction.

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filtering nested galleries

Based on what I understand on your scenario, you have a 3-level nested structure. For example, for header id 1, you could have the following lines:

 

H1 Line 1
H1 Line 2
H1 Line 3

Each of which has details, grouped by category (with a sum of the values of the details):

 

 

H1 Line 1
    H1L1 Cat 1 (Hours = sum of H1L1C1 line detail values)
    H1L1 Cat 2 (Hours = sum of H1L1C2 line detail values)
    H1L1 Cat 3 (Hours = sum of H1L1C3 line detail values)
H1 Line 2
    H1L2 Cat 1 (Hours = sum of H1L2C1 line detail values)
    H1L2 Cat 2 (Hours = sum of H1L2C2 line detail values)
H1 Line 3
    H1L3 Cat 1 (Hours = sum of H1L3C1 line detail values)
    H1L3 Cat 2 (Hours = sum of H1L3C2 line detail values)

And in each category you have specific line details:

 

 

H1 Line 1
    H1L1 Cat 1 (Hours = sum of H1L1C1 line detail values)
        H1L1C1 Detail 1
        H1L1C1 Detail 2
        H1L1C1 Detail 3
    H1L1 Cat 2 (Hours = sum of H1L1C2 line detail values)
        H1L1C2 Detail 1
        H1L1C2 Detail 2
    H1L1 Cat 3 (Hours = sum of H1L1C3 line detail values)
        H1L3C1 Detail 1
        H1L3C1 Detail 2
        H1L3C1 Detail 3
...

If that's what you need, you won't be able to represent all line details for a single line in a gallery, grouped by category, since you can only have a 2-level gallery nesting. The formula for that would be something along the lines of the expression below (notice the usage of the disambiguation operator @ to be able to differentiate between the Id of the tbLineDetails and the Id of the CollectionLines):

ClearCollect(
    CollectionLines,
    Filter(tbLines, FKtbHeaderID = CurrentHeaderID));
ClearCollect(
    CollectionLinesWithDetails,
    AddColumns(
        CollectionLines,
        "LineDetails",
        AddColumns(
            GroupBy(
                Filter(tbLineDetails, FKtbLineID = CollectionLines[@Id]),
                "Category",
                "ByCategory"),
            "Hours",
            Sum(ByCategory, Value))))

Since you cannot use a 3-level gallery nesting, you can consider using side-by-side galleries, where the selection of one gallery (L1) changes the selection of the items in the next level of the galleries.

2 REPLIES 2
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filtering nested galleries

Based on what I understand on your scenario, you have a 3-level nested structure. For example, for header id 1, you could have the following lines:

 

H1 Line 1
H1 Line 2
H1 Line 3

Each of which has details, grouped by category (with a sum of the values of the details):

 

 

H1 Line 1
    H1L1 Cat 1 (Hours = sum of H1L1C1 line detail values)
    H1L1 Cat 2 (Hours = sum of H1L1C2 line detail values)
    H1L1 Cat 3 (Hours = sum of H1L1C3 line detail values)
H1 Line 2
    H1L2 Cat 1 (Hours = sum of H1L2C1 line detail values)
    H1L2 Cat 2 (Hours = sum of H1L2C2 line detail values)
H1 Line 3
    H1L3 Cat 1 (Hours = sum of H1L3C1 line detail values)
    H1L3 Cat 2 (Hours = sum of H1L3C2 line detail values)

And in each category you have specific line details:

 

 

H1 Line 1
    H1L1 Cat 1 (Hours = sum of H1L1C1 line detail values)
        H1L1C1 Detail 1
        H1L1C1 Detail 2
        H1L1C1 Detail 3
    H1L1 Cat 2 (Hours = sum of H1L1C2 line detail values)
        H1L1C2 Detail 1
        H1L1C2 Detail 2
    H1L1 Cat 3 (Hours = sum of H1L1C3 line detail values)
        H1L3C1 Detail 1
        H1L3C1 Detail 2
        H1L3C1 Detail 3
...

If that's what you need, you won't be able to represent all line details for a single line in a gallery, grouped by category, since you can only have a 2-level gallery nesting. The formula for that would be something along the lines of the expression below (notice the usage of the disambiguation operator @ to be able to differentiate between the Id of the tbLineDetails and the Id of the CollectionLines):

ClearCollect(
    CollectionLines,
    Filter(tbLines, FKtbHeaderID = CurrentHeaderID));
ClearCollect(
    CollectionLinesWithDetails,
    AddColumns(
        CollectionLines,
        "LineDetails",
        AddColumns(
            GroupBy(
                Filter(tbLineDetails, FKtbLineID = CollectionLines[@Id]),
                "Category",
                "ByCategory"),
            "Hours",
            Sum(ByCategory, Value))))

Since you cannot use a 3-level gallery nesting, you can consider using side-by-side galleries, where the selection of one gallery (L1) changes the selection of the items in the next level of the galleries.

roli_
Level: Powered On

Re: Filtering nested galleries

Thanks Carlos.

 

Ended up using your suggested scenario with another gallery. Not the optimal choise as this is a mobile app and adds an extra layer of clicks, but it works.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 184 members 4,010 guests
Please welcome our newest community members: