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

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (23)

Answers (4)

BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report

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/1366228

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report
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)

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report
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

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report

Hi @MIA27 ,

Can you please share the error texts you are receiving.

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

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report
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) ) ) );

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report

Sir,
I copied the code as mentioned and paste, it has another error, below is the screen shot

Please guide, how to correct it.

Regards

Was this reply helpful? Yes No