Sorting and avoiding <> delegation in PowerApps gallery

[Anonymous]

Hey All,

Hope you are all doing good.

I am working on a creating matching application in PowerApps where I am using SharePoint List as my backend dataset.

I have created a dropdown "SelectInvidividual" which pulls all the records from the SharePoint list by using "Name" column.  Selecting any value in this dropdown will populate a View Form "IndividualDetails" and also populates gallery list "MatchingGallery". In the Gallery, all folks in the SharePoint data set are displayed EXCEPT the one selected in dropdown. To do this I am using Name <> DataCardValue13.Text (DataCardValue13.Text is part of IndividualDetails view form). When I try to do this I am getting delegation warning of beyond 2000 records dataset might not get you correct results. Is there a different way I can avoid this and still achieve the desired output??

Next question.

After populating the Gallery (MatchingGallery), I am using a ton of formulas to arrive at a number for each individual. This number varies from 0 to 100 and is being displayed in a label. This value being calculated is dynamic based on selection done in dropdown "SelectInvidividual" and not stored in backend SharePoint dataset. Is there a way I can sort the Gallery based on this calculated value? Right now I am sorting alphabetically but that is giving me hapazard sort results when I compare the calculated value. For e.g. 1st Name -  43, 2nd Name -29, 3rd name - 86, 4th name - 11 and so on.

Any help is highly appreciated.

Thanks,

AKB

[Anonymous]

I have now updated my App code to pull the entire SharePoint list dataset as a collection using ClearCollect method and remapped all the galleries and forms to point to this collection. The delegation warning is gone but my query is will this give accurate filter results for records count >2000. I am expecting minimum of 300-400 entries to SharePoint list per week once I go live with this app. 

[v-siky-msft]

Hi @Anonymous ,

No, It is not a perfect resolution to collects data to local collection, since ClearCollect is not a delegable function, so it can only collect 2000 records from the SharePoint list.

'<>' operator is only delegable for Number column in SharePoint list, so my workaround is to filter the gallery based on the Number column value of corresponding selected record from dropdown.

1. add a Number type column which should be unique identifier to SharePoint list,

2. save the Number value of selected Name record to variable when select the dropdown.

DropDown.OnChange: Set(MyVar,LookUp('SP list',Name=Dropdown1.Selected.Name).NumberColumn)

3. filter the gallery based on that variable(Number value).

Gallery.Items: Filter('SP list', NumberColumn<>MyVar)

To your second question, I think you use AddColumns function to add a new column to store the calculated number in Gallery.Items property, then you can sort the gallery based on that new column.

Hope this helps.
Sik

[Anonymous]

Thanks for responding @v-siky-msft . 

I tried the number trick you suggested but it is also showcasing delegation warning.

This is the code I entered:

Items: Sort(ShowColumns(Survey,"Name", "ID"),Name,Ascending)

ID is the number column which is automatically generated in SharePoint list.

On change property: Set(selectionID,Value(LookUp(Survey,Name=NameSelect.Selected.Name).ID))

selectionID is my number variable

Gallery Items formula:

Filter(
Survey,
ID <> selectionID,
other filters

)

Let me know if you require any additional details?

On the AddColumns function, I had tried it out but I was getting circular reference error. Below highlighted is where I am getting the circular reference error.

Sort(AddColumns(Survey,"Score",FinalScoreValue),"Score",Ascending)

All calculations adding up to FinalScoreValue is calculated dynamically for each item being displayed in the gallery. 

Thanks,

AKB

[v-siky-msft · Accepted solution]

Hi @Anonymous ,

Sorry, I should have reminded you earlier that the SharePoint ID field for a table is a number field in Power Apps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field.

Reference: https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint 

You need to try with a Custom Number Column.

To the second question, could you please share more details with some codes and screenshots?

How do you define the FinalScoreValue? Please share us how is the value calculated?

Sik

[Anonymous]

Hey there again @v-siky-msft,

So the suggestion of creating a new dynamic number column worked. I used FLOW to populate the newly created number column with ID value and delegation warnings have now gone away! Thanks so much for that 👍

For the circular reference error, the scenario is something like this.

Based on the dropdown selection value (on which I was getting delegation error), let's call this "A", I am populating the gallery details by displaying data of SharePoint list rows but filtering the one selected in dropdown. There is multiple selection checkbox column in SharePoint which is based on 15 different records.  As it is multiple selection checkbox value, I am first converting the value using CONCAT condition into a label and then searching the 15 items in the new label individually using IN function. Each find gives user a value of 1 and not selected gives them value 0. Total value of each row is thus dynamically calculated to get a "Value B" out of total 15 which I am calculating using SUM and VALUE(label.text) function.

Now the same process is followed for the dropdown selection and based on which "VALUE A" is calculated.

Calculation done for each gallery row = (Value B/Value A) * 100 --> Lets call this Value C

Now when I try to sort the gallery items based on Value C, I am getting circular reference error. Let me know if you require any additional info.

[v-siky-msft]

Hi @Anonymous ,

Are there any screenshots and codes? It can help us understand.

In general, you should first to save the calculated value to a new column by AddColumns function and generate a new Collection, then sort on the new collection based on that calculated column.

Please step by step, generate a new collection and then sort it in Gallery.Items property.

Hope this helps.

Sik

[Anonymous]

Sorry @v-siky-msft for not replying earlier as I got caught up in other stuff.

This is how the formulas work

I select employee details in dropdown 1. It filters the value from sharepoint list. Formula -  Sort(ShowColumns(Survey,"Name", "ID"),Name,Ascending)

Each employee has filled up a multiple choice survey questionnaire. These multiple choice answers are used to get the calculate value in vertical gallery also displayed from the same SharePoint list.

To convert multiple choice into a label inside gallery I am using following formula

lblTopicsSelected - Concat(ThisItem.TopicsSelected.Value,Concatenate(Text(Value),""))

As there are 18 multiple choice options, I am using following formula to calculate if any of the following value is present

lblItem1 = If("ABC" in lblTopicsSelected .Text,1,0)

lblItem2 = If("DEF" in lblTopicsSelected .Text,1,0)

lblItem3 = If("GHI" in lblTopicsSelected .Text,1,0)

.

.

.

lblItem16 = If("XYZ" in lblTopicsSelected .Text,1,0)

I am calculating the sum of these individual lblItems by using

lblTotalSum = Sum(Value(lblItem1.Text), Value(lblItem2.Text),..........,Value(lblItem16.Text)

From employeedropdown selection, I am calculating a similar value (not inside gallery) lblTotalEmployeeValue

Now the final matching value is calculated as in another label.

lblFinalValue = (Value(lblTotalSum.Text)/Value(lblTotalEmployeeValue.Text)) *100. This is calculated for each gallery row. I want to sort the gallery based on this lblFinalValue being displayed but getting circular reference error.

[v-siky-msft · Accepted solution]

Hi @Anonymous ,

It is not supported to sort gallery by the inside label value directly, it can only be sort by the data in the backend source.\

As an alternative workaround, you can create a new gallery, set items source to potentialEmployeeGallery.AllItems to take all gallery items as backend source of new gallery, then you are able to sort it by lblFinalValue.

Set new Gallery.Items property:

Sort(potentialEmployeeGallery.AllItems,lblFinalValue.Text,Descending)

Hope this helps,

Sik

[Anonymous]

Thanks for the tip @v-siky-msft. Worked great.

Only change which I did was instead of Sort(Gallery1.AllItems,Label1.Text,Descending) I used  Sort(Gallery1.AllItems,Value(Label1.Text),Descending)

Cheers!