Power Platform Community Forum Thread Details

Hello everyone

I tried a gazillon of different approaches to solve my issue but alas, I seem unable to. Maybe somebody around here is kind enough to point me into the right direction. My issue is as follows:

I have a canvas app connected to two Sharepoint lists. In the app I have a gallery set up as editable grid (similar to the one described by Reza Dorrani here). The grid is filtered by two cascading dropdowns outside of it. Dropdown 1 (DD1) is connected to Sharepoint list 1 (L1) and dropdown 2 (DD2) as well as the gallery are connected to Sharepoint list 2 (L2). DD1 filters on value V1 and DD2 filters on value V2.

Unfortunately, connecting everything to the same Sharepoint list is not an option. This is because while V2 exists in both lists, while V1 exists only in L1. L1 is needed tough because it is an aggregation level to L2.

I was able to get everything to work as it should except for the case where a value is selected from DD1 while DD2 is left blank. In this case, I want all elements from L2 corresponding to the selected L1 to be shown in the gallery. But unfortunately, I cannot get the gallery to accept the filter on DD1.

My last approach on the gallery's items looks like this (German punctuation is used - hopefully not too confusing for US users):

Sort(
    Filter(
        Ungroup(
            AddColumns(
                L2;
                "V1_added";
                LookUp(
                    ShowColumns(
                        L1;
                        "V2";
                        "V1"
                    );
                    L2.V2= L1.V2
                ).V1
            );
            "V1_added"
        );
        V2 = DD2.Selected.V2 || (IsBlank(DD2.Selected.V2) && V1_added = DD1.Selected.Value);
        varReset17
    );
    V3;
    SortOrder.Ascending
)