cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roli_
Frequent Visitor

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
CarlosFigueira
Power Apps
Power Apps

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
CarlosFigueira
Power Apps
Power Apps

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (82,258)