Answered

Distinct and sort values from a SP multiselect choice column

(1) Share

Report

Posted on by jauntysever

Hello

I'm trying to use the below code to give me values that I can select in a combo box. The Credential Domain(s) column is from a SP multiselect choice field, and has now been pulled into a collection in my app.

Sort(Distinct(colCredentials,'Credential Domain(s)'.Value),SortOrder.Descending)

The formula is not sowing any errors, but I'm also not seeing any values appear in my combo box either.

Any help would be greatly appreciated :)

Categories:

Using Apps Using Formulas

General Questions

All responses (4)

Answers (1)

Verified answer

jauntysever 23 on at

Like (1)

Report
Distinct and sort values from a SP multiselect choice column

In the end I was able to get the values using this

Distinct( Filter( Split( Concat( ForAll( colCredentials, Concat( 'Credential Domain(s)', Value & ";" ) ), Value & ";" ), ";" ), Len(Value) > 0 ), Value )
mstavra 247 on at

Like (1)

Report

Distinct and sort values from a SP multiselect choice column

I don't think that Distinct works on a multi select choice field. I would propose to create a 2nd combo box with single select values and test your formula. If it works you need to find a way to export the multi values in order to be taken as if there are singe values.

I hope this makes sense :)
jauntysever 23 on at

Like (1)

Report

Distinct and sort values from a SP multiselect choice column

Hi SoPatt

I tried that approach and still get an error -

"The expression was intended to sort the distinct values of the 'Value' column in the 'colCredentials' table in descending order. However, the errors in the expression are that the 'Sort' function cannot sort on the expression type, and there are invalid arguments provided to the 'Sort' function."
SoPatt Microsoft Employee on at

Like (3)

Report
Distinct and sort values from a SP multiselect choice column

Looking at the documentation for the Sort function from Sort and SortByColumns functions - Power Platform | Microsoft Learn, I see "Sort( Table, Formula [, SortOrder ] )"

What you have is two arguments to Sort, where one is a single-column table (because that's what Distinct returns), and the second is a SortOrder. So that doesn't match what the Sort function is expecting.

Possibly you want:
Sort(Distinct(colCredentials,'Credential Domain(s)'.Value),Value,SortOrder.Descending)

If not, perhaps you could share some sample data of what is in colCredentials or what Distinct() is returning.