cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Power Apps Staff CarlosFigueira
Power Apps 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.

View solution in original post

2 REPLIES 2
Power Apps Staff CarlosFigueira
Power Apps 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.

View solution in original post

Highlighted
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
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,578)