I have a list of profiles in the caseDB which have a case property called profile_creation_date_time = double(now()) this is just a hidden value when a profile is created.
When showing a list of profiles from the caseDB I would like the latest created profile to display as the first item in the list. The problem is that the Sort field by default sorts by ascending and I need it to sort descending. Is there any way to do this?
second argument set to false() will return the list in desc order
Hiya @Mazz,
Thanks so much for the response. I gave that a try see pic below and it didnt like that.
Doesnt seem to like that second parameter in the sort field.
Was that what you were thinking of or did you have something different in mind? Wondering if Im following your idea properly.
Tried the sort function on the query expression as well. Didn't like that either.
ah I see now
you probably want to do something else then
you want to
1- make a hidden calc called something like profile_creation_sort
2- put something like this int(profile_creation_datetime property ) * -1
3- store as a property
4- use that property as a sort
basically convert the date to a number
multiple by -1 to make it a negative value
use it in sort - sorting from a negative value will look like descending
aaaaaa @Mazz that was such a brilliant idea! I really thought that was going to work. Unfortunately it doesnt.
So here are my test results along with the case properties.
Order created
name: SortValue
01 Tazz Test: -19832.552888703704
02 Tazz Test: -19832.553663425926
03 Tazz Test: -19832.554162939814
Order sorted
name: SortValue
01 Tazz Test: -19832.552888703704
02 Tazz Test: -19832.553663425926
03 Tazz Test: -19832.554162939814
How I need it to be sorted
name: SortValue
03 Tazz Test: -19832.554162939814
02 Tazz Test: -19832.553663425926
01 Tazz Test: -19832.552888703704
I think the reason its not sorting it how we expect it to is because its not reading the sort value case property as a number but rather as a string, so the "-" negative sign has no affect.
Its not ideal, but what I have done is this in the calc sort value: 900000 - count(instance('casedb')/casedb/case[@case_type = 'profile'])
When devices sync though its not going to be 100% correct.
that doesn't look like an integer and it looks like it's sorting it like a text field
Im using the double function to store the time as well from the date, so that items that are created on the same day will be sorted correctly.
profile_creation_date_time = double(now()).
profile_creation_sort = profile_creation_date_time * -1
If I int(19832.554162939814) wont the result be 19832.
Are you thinking that if it is an int value it will sort correctly?
Maybe I should remove the decimal dot . with a string function and convert to int?
I see. I think that for some reason it is not recognizing that these are numbers.
you are correct, it would round those values and you would lose the time resolution
double should also work in theory but it might be treating them differently internally - double as string perhaps. I have sorted negative numbers correctly before I'm pretty sure. so it must be treating it as a string some how
Cool thanks @Mazz, its definitely worth a try then. I wont get there today. Will try on Monday and let you know if it works.
Heya @Mazz,
I gave it a try but no luck. Ready to give up on this now. haha
So I tried to remove the decimal point with a replace function and the convert the string to an int. What I ended up with was an empty case property.
19835.37405318287 became '' [Only thing I can think of is it cant handle an int that size, unless my function is not working correctly.]
Function I used below..
int(replace(replace(#form/profile_creation_date_time, '.', ''), ',', ''))
I am still convinced that it's treating those numbers as text.
try passing that property through the "number()" function instead of int or double.
if that doesn't work then...
honestly, I would ask support for any tips to change the sort order in lookup table multiple choice questions.
if not available, try this
calculate the difference in days
calculate the difference in minutes from "now"
now you have two smaller figures to work with.
stick them together
sort by those
you want to have the smallest number first, giving you the latest case on top.
remember to pad zeros so that 1 and 10 don't show up right after each other
