Sharepoint Choice List to Gallery

[araelius86]

My List has employees and their departments and other information.

Each employee may be part of the same department, therefore I use distinct to return only one result for each department. I want the Gallery to display the Departments in the same order that is in the List.

I currently have this Formula:

Distinct(Filter('SharepointList', StartsWith(Departments.Value, TextSearchBox1.Text)),Title)

This formula works fine however it returns the departments with One department at the top that is out of the order. The rest are in order.

I also have a ! on the items which states the "startswith" part of the formula may not work on large data sets <--- any other suggestions on this part.

The text value is: ThisItem.Result.

Help would be appreciated 

Thanks in advance!

[v-monli-msft · Accepted solution]

Hi @araelius86,

Your formula should filter the items with "The text value: ThisItem.Result.". Please explain what is the content of your "ThisItem.Result" part.  "ThisItem.Result" is the text to search for at the beginning of "Departments.Value".  

The ! is for delegation warning. The warning shows because that the choice column(Departments) is a complex field type and isn't delegable in SharePoint. In short, if you have more than 500 items, since this formula cannot be delegated, only the first 500 records are scanned.

Actually if you have items less than 500, then you do not need to worry about this warning. But if you did, I suggest that you whether using other column types like text/number to filter, or you can change this 500 number to 2000 by going to the File tab, selecting the App Settingsin the left hand navigation pane, and looking under Experimental features. Here you will find the "Data row limit for non-delegable queries"setting which you can change from 1 to 2000. This setting is app wide. With care, you can increase this number to fit your scenario. Be aware that as you increase this number your app's performance may degrade, especially for wide tables with lots of columns.

Another workaround is that you can collect your SharePoint list to a collection and then use the Department column in this collection as the data source of your Distinct formula.

About delegation:

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

Please see this post which lists all the supported delegatable predicates and functions for SharePoint.

https://powerapps.microsoft.com/en-us/tutorials/delegation-list/

Regards,

Mona

[rebeccas]

Try:

SortByColumns(Search('SharepointList',TextSearchBox1.Text,"Field1","Field2"),"Field3",Descending)

Field 1 and 2 are the search items and Field 3 is what order it is in.

[araelius86 · Accepted solution]

This has worked and the gallery is displaying correctly now.

The final code was: 

Distinct(Filter('SharepointList', StartsWith(Department.Value, TextSearchBox1.Text)),Department.Value)

in the Text field I have:

ThisItem.Result.

The Gallery1 shows the items (one of each unique value) and in the same order as my list.

My second query is to do with the second page which pulls the names from the list associated with whatever department.value I selected on the previous gallery.

So on gallery one I select the a Department and it goes to the screen "NameList"

The item field for "Namelist" looks like this: 

Filter('eHealth Customer Services Contact List', Department=Gallery1.Selected.Result)

This works, however as a test I added another Name (and the details for this person) to the sharepointlist but it is not showing up in the "NameList" Screen.

[Drrickryp]

You need to refresh the lists. To be certain I would refresh the browser in SharePoint and then refresh the datasource in Powerapps.

[araelius86]

Yeah i did both of these, even attempted to shut down the browser and restarted it.