Custom sort order for dataview table

[themixx]

Hi,

I’m trying to sort a dataview table by a custom order.

I have a sequence of statuses for actions (at the moment tags, but could change later to key:: value pairs): #as/now → #as/next → #as/later → #as/someday and would like to sort my actions table based on these statuses - however not alphabetically, but by the status sequence above.

Of course I could prefix each status with a number, but that isn’t ideal.

At the moment I’m using 4x tables, one per status, but that too isn’t ideal or the neatest solution either due to the repeating heading of the table.

For reference here is my query:

TABLE WITHOUT ID action-status as Status, file.link as Action
FROM #as/now  
WHERE project = this.file.link

I’m hoping that there is a way to map my statuses to an order with dataviewjs.

Thanks!

[mnvwvnm]

Yes, I think the best way is via JS… but I don’t know js.
So, I give you a tricky solution to dql.
Let’s say your field-pairs are:

kyky:: now
kyky:: next
kyky:: later
kyky:: someday

And you want to sort the results in this order.
You can try something like this

...
SORT choice(kyky = "now", 1, choice(kyky = "next", 2, choice(kyky = "later", 3, choice(kyky = "someday", 4, "other")))) ASC

The alternative: wait for a js expert answer.

[themixx]

This is awesome - thank you! Works perfectly

[themixx]

hm looks like I spoke too soon. I adapted your script to this:

SORT choice(action-staus = "#as/now", 1, choice(action-staus = "#as/next", 2, choice(action-staus = "#as/later", 3, choice(action-staus = "#as/someday", 4, "other")))) ASC

… and the order of “someday” and “next” are wrong. Any ideas? or am I missing something obvious?

Edit: looks like the sort is applying to the name of the file

[mnvwvnm]

action-staus or action-status?
and, I’m not sure about the behavior with tags. that’s why I suggested non-tags.

[themixx]

ugh I’m dumb - thank you & it works perfectly