How to Configure Sorting by Date Within Sub-categories in Glide?

[MaxDev]

Hello everyone,

I am developing a task management application that allows me to create tasks based on categories or subcategories. I have a screen that lets me change the list of tasks according to the category (like bicycles, cars, etc.), and so far, everything is going well. However, I am looking to configure the display of tasks in a collection that sorts data in the following way:

• Sub-category (from A to Z)
• Within each sub-category, sort tasks starting with the earliest date at the top, followed by the next date, and then tasks without a date.

I’m attaching an example of the current situation to better explain what I’m aiming for (if possible). (sort data from 1 to 5)

I believe this should be possible by configuring a new ‘if-then-else’ type column or similar in my task table, and then filtering the view conditions on my screen. However, I have no idea how to proceed.

Would anyone have any ideas or suggestions on how to set this up?

Thank you in advance for your help!

[slscustom.ru]

May be, it is just enough to use template column to combine category name and date and use it just to sorting.

[ThinhDinh]

Here’s my approach.

Add a “Date Numeric” column to convert the date to a numeric value (0 for empty dates).

-0+0+YEAR(D)*10^4+MONTH(D)*10^2+DAY(D)

Add a JavaScript column to have a sort value.

function calculateValue(subCategory, taskName, dateNumeric) {

    function getLetterNumericValue(letter) {
        const upperCaseLetter = letter.toUpperCase();
        return upperCaseLetter.charCodeAt(0) - 'A'.charCodeAt(0) + 1;
    }

    const subCategoryLetter = subCategory.charAt(0);
    const taskNameLetter = taskName.charAt(0);

    const subCategoryNumeric = getLetterNumericValue(subCategoryLetter);
    const taskNameNumeric = getLetterNumericValue(taskNameLetter);

    if (!dateNumeric) {
        dateNumeric = 99999999;
    }

    const result = (subCategoryNumeric * Math.pow(10, 10)) +
        (taskNameNumeric * Math.pow(10, 9)) +
        99999999 - dateNumeric;

    return result;
}

return calculateValue(p1, p2, p3)

I extract the first letters of both the category and the task name, then convert it to the corresponding number in the alphabet. Add them together, plus the complement compared to 99999999 of the date numeric value (so earliest gets pushed to first in each group).

[MaxDev]

Wow, that’s impressive—it works! It’s still a bit of a mystery to me, but I greatly appreciate your highly effective response. I look forward to studying this in depth (I didn’t know that scripting was possible in Glide).