FirstSortedValue using a non numeric field to sort

[jmbenedetto]

Hello,

I have a table with 3 fields:

Base data

For each Category, I need to retrieve the value corresponding to the lowest Sort. The chart function FirstSortedValue seems to be the answer. However, I could not make it work. It always returned Null.

I searched for an answer on the Community, but could not find. Then I asked for @rafaelattux help, and he made it work by transforming the Sort field into a number. See below both attempts:

FirstSortedValue

Original formula returning Null:

=FirstSortedValue([Value],[Sort])

Attux's formula returning the right answer:

=FirstSortedValue([Value],PurgeChar([Sort],'M'))

Can we conclude that FirstSortedValue can only be used with numeric values or dates(I found some discussions on the forum for dates) in the sort parameters?

Thanks a lot for your help.

[Or · Accepted solution]

I believe that FirstSortedValue() requires a numeric input for the sort_order parameter, so you can input anything as long as it has a numeric representation - a number, a date, a dual() field, etc.

In the above example, you could have created Dual(Sort,KeepChar('0123456789')) and used that for both purposes (Note that this is the same as the purgechar but allows for characters other than M to be removed). If those characters are additionally necessary, you could do some creative string/math manipulation, such as:

Ord(Left(Sort,1))*100 + KeepChar('0123456789') // This would sort based on the first letter alphabetically and then based on the remaining numeric value