Cannot sort a date array into Descending in Lotusscript

Question

Seeking for your help. I'm trying to sort a Date array to descending order but to no avail.

Here is the snippet of my code:

Dim sortValue as Variant

Dim sortValueResult as Variant

Dim r1 as String

SortValue = entry.Columnvalues(1)

' The values are (0) 19/5/2018 and (1) 26/5/2018

r1 = Join(sortValue, ";")

sortValueResult = Evaluate ( {@Sort("} & r1 & {";[DESCENDING])}) `

I'm getting 19/5/2018;26/5/2018

26/5/2018 must come first before 19/5/2018.

Can anybody help me?

Thank you.

Answer 1

@Sort can work on date lists, but you are not passing in a list. You are passing in a string.

In Notes formula language syntax, a list is represented by elements separated by ":" characters; the string representation with ";" characters is not actually a list. You can go back and forth between string and list formats with @Explode and @Implode, but the output of @Explode is always a text list, not a date list. You could deal with that with @TextToTime, but you really might as well just avoid that by directly creating list notation. You need to surround each data string with brackets to turn it into a date constant, and separate them with colon characters - putting them all within a LotusScript string.

Something like this (not tested) should get it into proper list notation before you make your Evaluate call:

forall r in r1
  r = "[" + r + "]"         ' [19/5/2018] is proper date constant notation
end forall
r1 = Join(sortValue, ":")   ' colon is list separator
' result here should be r1 = "[9/5/2018] : [26/5/2018]"