ORDERBY Basics

[Kelin111]

Hello!

I am extremely new to AppSheet and have started taking over some responsibilities from the creator of our app who has built it on a learn-as-you-go basis.  I believe ORDERBY() is what I am after here but I don't even know where this expression would be entered or if it will work for us.  I have looked over the documentation and watched some videos/read some answered posts but I am still unsure where to go to make this happen.

WHAT WE HAVE:   We have a table of tasks, each one assigned to an employee to be completed through a column in the table using their email as the data.  These tasks are currently successfully grouped by status [NOT STARTED, IN PROGRESS, COMPLETED].   We also have columns for Due Date, Date Completed, a Task Description and a UNIQUEID column that we use as the Key which uses the UNIQUEID() method as its value.

WHAT WE NEED:  We want these tasks to be ordered so that if the status is "Completed" then it will order the tasks only in the COMPLETED group to show the most recently completed task at the top and descend back in time with the oldest task at the bottom by using the "Date Completed" column.  The NOT STARTED and IN PROGRESS groups should still be sorted by soonest due date at the top and furthest due date at the bottom.  We want all three groups to be visible in the same View which is making it difficult for me to figure out how to do this.

Thank you for any assistance.

[Marc_Dillon · Accepted solution]

Ooooh, this is a fun one!

First, no, ORDERBY is not what you want, that's for ordering the results of an expression. Sorting records in a view is a different matter.

Having a different sort order, based on the value in another column, is a bit tricky. This might not be the only way to do it, but just what I first thought of.

You need to add another column, to hold a calculated result, and this is the column that you will set as the sort control in your view.

For this calculated result, we need a number that follows the order of dates for "Completed" status, but goes in the opposite direction for other statuses. My first thought here is just to find the difference between the date and some common epoch date, but in reverse ways. Jan 1st 1970 is a common date epoch, so we can use that. For the formula, we'll then use:

IF(
  [Status]="Complete" ,
  [date] - "1/1/1970" ,
  "1/1/1970" - [date]
)

 This will produce positive numbers for complete statuses that get larger as the dates get later, and negative number for non-complete statuses that get larger as the dates get earlier.

So then in the view, you'll set the sort by to [status] first, then this new column second, adjust the ascending vs descending as needed. You just don't show the new column anywhere, you only show the date.

[Kelin111]

That's actually quite clever if I'm understanding this correctly.  Let me reiterate this back to you to make sure I'm getting it.

We add a new, non-visible column to the table, say we call it "Sort Column" and this column will have positive and negative numbers which will be populated by the IF statement that either subtracts the 1-1-97 date from today (positive #) if the status is "Complete" OR subtracts today's date from 1-1-97 (negative #) otherwise.  We set that as the new sort by secondary to the Status column and it should always sort as we like.

[Marc_Dillon]

Sounds like you got it.

[Kelin111]

This worked perfectly Marc.  This was my final formula.

IF(
[Status]="Completed",
[Date Completed] - "1/1/1970", 
"1/1/1970" - [Due Date])