Sort and Distinct in ComboBox from a SharePoint list works in canvas editor but not in web player

[dachou]

Hi! This seems to be a common question but I haven't been able to find the answer. Help please! 😊

I have a simple contact list 'Customer Contacts' in SharePoint connected as the data source to a Power App. In a ComboBox I'd like to display distinct customer names (like organization names) from the column "Customer", and using this formula:

Sort(Distinct('Customer Contacts',Customer),Result,Ascending)

This works fine in the canvas app editor such as when I use Alt to test it, and Preview (F5). However, when I publish the app and try to play it (e.g., from the apps.preview.powerapps.com URL), the app is displayed with a continuous loading indicator and the ComboBox is empty. It never finishes and the ComboBox never gets populated.

Other things on the app works fine, such as the Gallery that I have showing the list of items, and the ComboBox is intended to filter the Gallery to show contacts from the selected Customer (which is a common use case). I must be missing something; any advice would be greatly appreciated. Thank you!

[Drrickryp]

@dachou 

As a test, put Set(cbitems,Sort(Distinct('Customer Contacts',Customer),Result,Ascending)) in the OnVisible property of your screen and put the Items property of the combobox: cbitems and see if that works.

[dachou]

Thanks for the suggestion! I tried that too and still the same behavior. It works in the editor but not in the published app. I have also tried toggling on/off settings such as Explicit column selection and Formula-level prefetching as I read through many articles/posts, but didn't see any changes.

I just tried to remove both Distinct and Sort functions, and left just the table column itself, like this:

'Customer Contacts'.Customer

Even this exhibits the same behavior - works in the editor but not in the player. Furthermore, I added more controls, such as a drop-down list, a listbox, etc. to use the same formula for their list items, all on the same screen at the same time. Same behavior. Hence this doesn't seem to be an issue with Sort or Distinct functions; it seems almost as if my app can only run 1 formula when opened in the player. What am I missing?

[dachou]

I found the problem. My formula for the Gallery had included a Search function. Once I commented it out and pasted in the default generated formula without Search, then the ComboBox worked fine in the player even with Sort and Distinct both. The table is small, with only 11 records.

SortByColumns(Filter([@'Customer Contacts'], StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

/*
SortByColumns(
    Search(
        Filter(
            'Customer Contacts',
            IsBlank(ComboBox_Customer.Selected) || Customer = ComboBox_Customer.Selected.Result
        ),
        TextSearchBox1.Text,
        "Customer"
    ),
    "Record Date",
    If(SortDescending1, Ascending, Descending)
) */

Is there some limitation with using Search this way, that'd result in a difference in behaviors between the editor and the player? Or, can I use Search this way (or what is the right way to use Search)? I basically followed the example from https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-filter-lookup.

In addition, I'd see the same warning in the editor (pasted below) if I try to use the IN operator as well (e.g., TextSearchBox1.Text in Customer) as part of the Filter criteria. I understand the warning, but if this introduces an issue in the player but not in the editor, then I wish it'd either be fixed or be more transparent about the behavior.

Lastly, this is something that I can only troubleshoot and verify by publishing the app, as the issue occurs in the player but not the editor.

[WarrenBelz · Accepted solution]

Hi @dachou ,

Both Search and the in Filter are not Delegable - the below will not show a warning, but will be limited to your Delegation limit with the output of the top formula.

With(
   {
      wList:
      Sort(
         Filter(
            'Customer Contacts',
            Len(ComboBox_Customer.Selected.Result) || 
            Customer = ComboBox_Customer.Selected.Result
         ) 
      ),
      Record Date,
      If(
         SortDescending1, 
         Ascending, 
         Descending
      )
   },
   Filter(
      wList,
      Len(TextSearchBox1.Text) = 0 ||
      TextSearchBox1.Text in Customer
   )
)

[dachou]

Thanks @WarrenBelz! This worked, and I learned about the newbie mistake I made. 🙂  So this was purely a delegation limit issue. I read through the documentation with more patience and have a better understanding now.

Just FYI in case anyone come across this thread having similar thoughts as I did:

https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview

https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/performance-tips#use-delegation

And specifically for the SharePoint List data source I was working with (to confirm which functions are delegated, etc.):
https://docs.microsoft.com/en-us/connectors/sharepointonline/

And, thank you for the suggested solution! Using "With" to separate the parts that can be delegated and not is very useful. It worked around the delegation limit and allowed me to improve the search experience (I was able to add more "IN" clauses to the Filter). Thanks!!

[WarrenBelz]

Hi @dachou ,

I have two blogs that may also assist you - Delegation and the With Statement