FirstSortedValue using a non numeric field to sort

jmbenedetto — Tue, 13 Dec 2022 15:24:35 GMT

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.

Re: FirstSortedValue using a non numeric field to sort

Or — Tue, 13 Dec 2022 15:34:55 GMT

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

topic Re: FirstSortedValue using a non numeric field to sort in App Development

FirstSortedValue using a non numeric field to sort

Re: FirstSortedValue using a non numeric field to sort