Power Apps: SharePoint | Delegation + AddColumn()

/ Charles

So yeah, this won’t work for everyone, but it was a creative delegation workaround for me. In my scenario, I used several SharePoint Online (SPO) lists as data sources and needed to filter on a multi-line column of employee Names, separated by a semi-colon:

Figure 1 - SharePoint Online new item form.
Figure 1 – SharePoint Online new item form.

This example SPO list tracks upcoming events and once registered, attendees have their first names concatenated as a long string:

Figure 2 - SharePoint Online list view of items.
Figure 2 – SharePoint Online list view of items.

Now, the canvas app needs to filter the list of events using a registered attendee’s name, so I’ll insert a dropdown control, hardcode a few dropdown choices, then connect my SPO list to a vertical gallery control:

Figure 3 - Power Apps canvas app dropdown options.
Figure 3 – Power Apps canvas app dropdown options.

Summarizing the Items data source logic of the vertical gallery, the Power Fx snippet filters the source data, Attendees, using the Filter() function and dynamically creates a new column called “SearchForNameFound”. Using the LookUp() function, check if the dropdown value, ddlFilterOnNames, is found in the dynamic array of names. If yes, then the length of the returned value is greater than 0, so set the new column’s value to TRUE. Otherwise, set the value to FALSE. Finally, only return records with a “SearchForNameFound” value of TRUE:

Filter(
    AddColumns(
        Attendees, 
        "SearchForNameFound", 
            Not(
                Len(
                    LookUp(
                        Split(Names, ";"), 
                        Value = ddlFilterOnNames.SelectedText.Value
                    ).Value
                ) = 0
            )
    ),
    SearchForNameFound = true ||
    If(IsBlank(ddlFilterOnNames.SelectedText.Value),
        true,
        false
    )
)
Figure 4 - Canvas app dropdown with no selection and all gallery items.
Figure 4 – Canvas app dropdown with no selection and all gallery items.
Figure 5 - Canvas app dropdown with 'Tori' selected and gallery items filtered.
Figure 5 – Canvas app dropdown with ‘Tori’ selected and gallery items filtered.
Figure 6 - Canvas app dropdown with 'Charles' selected and gallery items filtered.
Figure 6 – Canvas app dropdown with ‘Charles’ selected and gallery items filtered.

Conclusion:
Again, this likely won’t work for all multiple line column delegation warnings. Still, with a bit of creativity I was able to sidestep this delegation warning message.

“Slavery is a kind of social leprosy: it has often been abolished by legislators and restored by education under various aspects.”

Luís Gama

#BlackLivesMatter

Leave a comment