Distinct Function not bringing correct result in dropdown data based on Sharepoint list

[MIA27]

I am using the below formula in the Items property of the dropdown 

Distinct(KomtraxImport,F3)

It has no error but the result is not correct.

The sharepoint list has 2356 records and should bring result of more than 12 rows

But it just bring the 1 row result only.

please guide

[BCBuizer]

Hi @MIA27 ,

Since the Distinct function is not delegable, this is probably the cause of your issue.

More info on possible solutions can be found here: https://powerusers.microsoft.com/t5/Building-Power-Apps/Filtering-distinct-value-and-delegation/td-p...

[MIA27]

Sir,

Tried the link contents given, not successful.

The one I tried is the below code :

With( { wLow: Sort( SPList, ID ), wHigh: Sort( SPList, ID, Descending ) }, ClearCollect( colMyCollection, wLow, Filter( wHigh, !(ID in wLow.ID) ) )

I changed SPList to my Sharepoint list name, ID did not change as I think its SharePoint List standard.

could not understand, what is the mistake in the above code I a making the bring the result which I wanted to bring via Distinct.

Pls. guide.

[BCBuizer]

Hi @MIA27 ,

THe above code should create a collection with up to 4000 items in it. To extract the unique values from this, you will still need to apply the Distinct function:

Distinct(colMyCollection, F3)

[MIA27]

sorry, confused how to add both the code together, very new in coding.

tried the code as below by the putting the actual List name and adding the distinct code together. But no result no error.

With( { wLow: Sort(KomtraxImport, ID ), wHigh: Sort(KomtraxImport, ID, Descending ) }, ClearCollect( colMyCollection, wLow, Filter( wHigh, !(ID in wLow.ID) ) );Distinct(colMyCollection, F3)

Pls. guide, what correction to do in the code.

Regards

[BCBuizer]

Hi @MIA27 ,

The two parts of the formula can't be combined as-is since the one (ClearCollect) is behavioural and the other (Distinct) is declarative. Behavioural means that a certain action is performed whereas declarative works more like a formula in Excel.

To make this work, you can add your ClearCollect formula to the OnVisible property of the screen where the dropdown is located. Like this the collection is built every time a user navigates to that screen:

With( 
   {wLow: Sort(KomtraxImport, ID ), wHigh: Sort(KomtraxImport, ID, Descending ) },
   ClearCollect( colMyCollection, wLow, Filter( wHigh, !(ID in wLow.ID) ) 
);

The Items property of the dropdown can then be set with the Distinct formula:

Distinct(colMyCollection, F3).Value

[MIA27]

Sir, 

Tried your advise method, its not working, no result no error

Below is the images showing how I implemented

please guide what mistake I am making.

Is the ID in the Collection and F3 field in the distinct is the issue, could not understand.

Note :    ID is the field auto made by sharepoint list while making the list and F3 is a field which has data of this list.

regards

[BCBuizer]

Hi @MIA27 ,

Can you please share the error texts you are receiving.

On another note, what is the column type of 'F3'?

[MIA27]

Sir,

Below is the error shown when I point the mouse near the red cross mark

of the screen Top red cross then

When I point on the Field the error is as below

F3 Field is a Text Field below is image I took from the share point list site

Kindly guide how to correct the mistake

regards

[BCBuizer]

Hi @MIA27 ,

It turns out there were some syntax errors in the formulate for the collection. Please try:

With( 
   {wLow: Sort(KomtraxImport, ID ), wHigh: Sort(KomtraxImport, ID, SortOrder.Descending ) },
   ClearCollect( colMyCollection, wLow, Filter( wHigh, !(ID in wLow.ID) ) )
);