Delegation Problems

[AmínAA]

Greetings!

I am having some trouble trying to make a sortable gallery . . . I have a header on the column that that will change a variable to alter the sort column and sort order for each column, for it's gallery bellow. However with the current formulas I'm using, it's causing me delegation errors, which I am not able to solve on my own for now . . . Here's as much as info as I can give on the situation!

What I'm trying to do

Column OnSelect Property (Changing "EID" for other values on each column)

If(
    varSort <> "EID";
    UpdateContext(
        {
            varSort: "EID";
            varSortOrder: "None"
        }
    )
);;
If(
    varSortOrder = SortOrder.Ascending;
    UpdateContext({varSortOrder: SortOrder.Descending});
    varSortOrder = SortOrder.Descending;
    UpdateContext({varSortOrder: "None"});
    varSortOrder = "None";
    UpdateContext({varSortOrder: SortOrder.Ascending})
)

Galler Items property

Sort(
    Filter(
        Search(
            'TableGE - Employees';
            compUltimateTextbox_1.value;
            EMPLOYEE_NAME
        );
        Or(
            IsBlank(compUltimateCombobox_1.selectedItems);
            EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(
                compUltimateCombobox_1.selectedItems;
                Value
            )
        )
    );
    Switch(
        varSort;
        "EID";
        EMPLOYEE_ID;
        "ENAME";
        EMPLOYEE_NAME;
        "ERESPONSIBLE";
        EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME
    );
    Switch(
        varSortOrder;
        "ascending";
        SortOrder.Ascending;
        "descending";
        SortOrder.Descending;
        "None";
        "None"
    )
)

Non-delegable part of the gallery code

If there is any other way to achieve what I'm doing while retaining the delegation, or any code-fix I can implement on this, I'll be very thankful . . . For now I'll keep trying to fix it on my own, but a little help never hurts!

[anandm08]

hi @AmínAA ,

try this:

SortByColumns(
    Filter(
        'TableGE - Employees',
        StartsWith(EMPLOYEE_NAME, compUltimateTextbox_1.Text) &&
        (
            IsBlank(compUltimateCombobox_1.SelectedItems) ||
            EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(compUltimateCombobox_1.SelectedItems, "Value")
        )
    ),
    Switch(
        varSort,
        "EID", "EMPLOYEE_ID",
        "ENAME", "EMPLOYEE_NAME",
        "ERESPONSIBLE", "EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME",
        "EMPLOYEE_ID" // Default sort column
    ),
    varSortOrder
)

[AmínAA]

Greetings @anandm08!

Thank you for the quick response, however, after adjunsting the code to my App and language I still have the same Delegation problem . . .

ADJUSTED CODE

SortByColumns(
    Filter(
        'TableGE - Employees';
        StartsWith(
            EMPLOYEE_NAME;
            compUltimateTextbox_1.value
        ) && (IsBlank(compUltimateCombobox_1.selectedItems) || EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(
            compUltimateCombobox_1.selectedItems;
            Value
        ))
    );
    Switch(
        varSort;
        "EID";
        "pfx_employee_id";
        "ENAME";
        "pfx_employee_name";
        "pfx_employee_id"// Default sort column
    );
    varSortOrder
)

DELEGATION PROBLEM

While I did try before using SortByColumns I did not use this sort of filtering, however it still doesn't do the job 😞

[DBO_DV]

Hey @AmínAA ,

Can you tell me what the database is?

[AmínAA]

[DUPLICATED]

[AmínAA]

[DUPLICATED]

[AmínAA · Accepted solution]

Greetings @DBO_DV, long time no speak!

Sure, I am currently using Dataverse with three columns, two of which are Text columns, those being EMPLOYEE_ID & EMPLOYEE_NAME, and one being a lookup column, that one being EMPLOYEE_RESPONSIBLE. From which I'll later derive the responsible name with EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME . . . Now I managed to fix the delegation problems (almost all of them) with a probably unnecesarily big and redundant code, but I still have one delegation problem . . .

ITEMS PROPERTY OF GALLERY

Switch(
    varSort;
    "EID";
    Sort(
        Filter(
            Search(
                'TableGE - Employees';
                selCompUltimateTextboxSearch.value;
                EMPLOYEE_NAME
            );
            Or(
                IsBlank(selCompUltimateComboboxResponsible.selectedItems);
                EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(
                    selCompUltimateComboboxResponsible.selectedItems;
                    Value
                )
            )
        );
        EMPLOYEE_ID;
        Switch(
            varSortOrder;
            "ascending";
            SortOrder.Ascending;
            "descending";
            SortOrder.Descending;
            "None";
            "None"
        )
    );
    "ENAME";
    Sort(
        Filter(
            Search(
                'TableGE - Employees';
                selCompUltimateTextboxSearch.value;
                EMPLOYEE_NAME
            );
            Or(
                IsBlank(selCompUltimateComboboxResponsible.selectedItems);
                EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(
                    selCompUltimateComboboxResponsible.selectedItems;
                    Value
                )
            )
        );
        EMPLOYEE_NAME;
        Switch(
            varSortOrder;
            "ascending";
            SortOrder.Ascending;
            "descending";
            SortOrder.Descending;
            "None";
            "None"
        )
    );
    "ERESPONSIBLE";
    Sort(
        Filter(
            Search(
                'TableGE - Employees';
                selCompUltimateTextboxSearch.value;
                EMPLOYEE_NAME
            );
            Or(
                IsBlank(selCompUltimateComboboxResponsible.selectedItems);
                EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(
                    selCompUltimateComboboxResponsible.selectedItems;
                    Value
                )
            )
        );
        EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME;
        Switch(
            varSortOrder;
            "ascending";
            SortOrder.Ascending;
            "descending";
            SortOrder.Descending;
            "None";
            "None"
        )
    );
    Sort(
        Filter(
            Search(
                'TableGE - Employees';
                selCompUltimateTextboxSearch.value;
                EMPLOYEE_NAME
            );
            Or(
                IsBlank(selCompUltimateComboboxResponsible.selectedItems);
                EMPLOYEE_RESPONSIBLE.RESPONSIBLE_NAME in ShowColumns(
                    selCompUltimateComboboxResponsible.selectedItems;
                    Value
                )
            )
        );
        EMPLOYEE_NAME;
        Switch(
            varSortOrder;
            "ascending";
            SortOrder.Ascending;
            "descending";
            SortOrder.Descending;
            "None";
            "None"
        )
    )
)

CURRENT DELEGATION PROBLEM

Should you, or anyone find a fix before me for the delegation, and or the probably unnecesarily big and redundant code, I'm all ears . . .

[DBO_DV · Accepted solution]

Hey @AmínAA, 

the Problem is the Switch here is the link. It is Not delegable with functions or multiple columns in it.

The switch needs to be outside just like you did.

for your lookup column you can basically use lookups or filters to simulate the relationships without breaking it in dataverse.

test it out and have a nice evening.