cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
araelius86
Level: Powered On

Sharepoint Choice List to Gallery

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Sharepoint Choice List to Gallery

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

 

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

araelius86
Level: Powered On

Re: Sharepoint Choice List to Gallery

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.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Sharepoint Choice List to Gallery

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

 

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: Sharepoint Choice List to Gallery

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
Level: Powered On

Re: Sharepoint Choice List to Gallery

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.

View solution in original post

Super User
Super User

Re: Sharepoint Choice List to Gallery

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

araelius86
Level: Powered On

Re: Sharepoint Choice List to Gallery

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

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,490)