Hi. One of my apps keeps a list of tasks. They have a due date and a priority. I want to sort them with those due sooner at the top. However if several tasks have the same due date then those with a higher priority should appear first i.e. a multi-level sort. Ideally I want to do this without using a formula in the Google Sheet since my entire app handles everything in Glide, however building the appropriate “sort key” in a Glide column is causing me an issue. All ideas welcome
What happens when you make a math column multiplying the date with the priority? I’m just asking how Glide interprets the date in a math and probably I can help a solution.
Hi @ThinhDinh I will look at the maths. Good idea. But I think it’ll be addition not multiplication. Bear with.
Alas - date values are seen as 0.00 in a maths column
Can you try duplicating the column in Sheets using a formula, then format it as a number column?
Addition is the right way here, you’re right. Multiplying two columns together and sort by it should give the right outcome.
Then it gives some “crazy” numbers that look like they’re based on the “char values” of the textual representation of the date. Looks as though I may have to revert to the Google Sheet and an array column. I hate that.
You just need an arrayformula to duplicate the date in the Sheets, then the rest can happen in Glide I imagine.
Yup. That’s exactly it. In the Google Sheet I create a “Sort Key” string which is YYYY + MM + DD + “0” or “1” based on priority. Then sort on that inside Glide. It works but a shame
Can you try formatting the date in the Sheets in the original column as yyyy/mm/dd then combine it with the priority in a template column inside Glide? I think it would be the same, albeit you need to forfeit whatever format you used to display the date before.
- Create a copy of the date column in GS.
- Change the data type of the new date column to a numeric value.
- Assign a number to the priority: High - 3, Medium - 2, Low - 1.
- Concatenate Priority and Numeric Date value with a hyphen in between using the Template column.
- Use this new field to sort.
In theory, this works. In practice, let us know if it works.