I've got a table bringing in JSON data with a foot race lap times. So the incoming format is minutes:seconds.milliseconds but if there's no format that seems to work other than using TEXT. But then it doesn't sort properly. Is it possible to get a duration format?
Also using the Position column, I can't get it to sort correctly either. if it comes in like 1. - 15. it sorts like this:
1.
10.
11.
.....
15.
2.
3.
and so on.
Hello and welcome,
Since it's a duration format with max unit of minute, meaning the minute value can go beyond 59, so datetime nor time will not work. Text will not work as you've seen it.
The work around is to split your duration format into 3 separate data points: minutes, seconds, and milliseconds. Each is essentially integer value. With the new data points, you can sort using the combination of all 3 values. You can use combination of transformer and/or Query JSON with SQL to achieve it. Keep in mind, this most likely is a static sort, meaning sorting with in code and disable sorting on the table - unless you provide a mean for user to trigger this special sort.
Hey @Jason_Dupree,
I read over my first reply and I just want to clear up this sentence:
...so datetime nor time will not work...
I missed "not" in there.
Hey Jason!
Text columns would sort numbers like 1, 10, 11, 12, etc. so you would definitely need to use a different column type (e.g. Number) once you're able to get your duration.
I believe you just need to set your column mapper to something like:
{{ item.split(":").reduce((acc, val) => 60 * acc + parseFloat(val), 0) * 1000}}
to turn all of your timestamp strings to milliseconds! Or
{{ item.split(":").reduce((acc, val) => 60 * acc + parseFloat(val), 0) * 1000}}
for seconds. Let me know how that works for you 
Here's an example with a hardcoded timestamp:
