Formula for automatic sorting by year?

Key column

Test-2020/001
Test-2020/002
Test-2020/003
.
.
.
Test-2020/756
Test-2021/001
Test-2021/002
.
.
.
How should I use a formula for automatic sorting in this way.

Can anyone help? Thanks

It looks like the last 8 characters of your key column data is what you wish to sort by?

Use a virtual column with a formula

Right([keycolumnname],8)

This should leave the last 8 characters of your key column in this new virtual field and you can then use it to sort

1 Like

I do not know how to do this order. How can I move from 2020/756 to 2021/001.

I think I have misunderstood your question.
Now I am not sure whether you are asking how to generate sequential numbers for each record added?

I want to create year and number of sequences
when the year ends it will start again from “1”

Hmmm, this is a tall order really. You’re not going to like the solution. I have one that involves Google Apps Script on the backend sheets, but I couldn’t possibly explain how to do this in a forum post.

However there is another post you might find useful

It explains the pitfalls and why it’s hard to do serial numbers in a multiuser system.

I am toying with the idea to make a video showing how I solve this problem, but it’s still in the thinking stage at the moment.

4 Likes

CONCATENATE(YEAR(TODAY()) ,MAX(Tasks[_RowNumber]))

I sort it out like this, but when the year ends, how can I restart from “1” I can’t do it.

I tried this but not what I wanted, is there any other solution?

_RowNumber is automatically assigned by Appsheet so you will never be able to have that value restart at 1 for each year.

Using _RowNumber in the key is a very bad idea. There is no guarantee the same value of _RowNumber will be assigned to the same row in the future - especially if you are ever deleting rows and removing the blank rows from your sheet or using a database.

To solve your problem AND make sure your app stays happy well into the future, I recommend the following:

1). Assign a dataless key value to your key column - use UNIQUEID().
2). Have separate columns for Year and the Sequence number.
3). If the values like “Test-2020/756” are displayed to users, then create a non-key column that builds this from Year and Sequence.

If you do make the changes above, go back to the sheet and retro-fit the existing rows to this pattern. You can use formulas to do it and should only take 15 minutes or so.


With the above changes you can still sort in the way you are accustomed.

Now to restart the Sequence number at 1 for each year, simply query for any existing rows of the current year. If none found then assign the value of “1”. Otherwise add 1 to the MAX() value. The expression in the Sequence column (assuming a number) would be something like this:

IF (COUNT(Tasks[Year], [Year] = YEAR(TODAY()) = 0,
     1,
     MAX(Tasks[Sequence]) + 1
)
3 Likes

You could use this expression in a virtual column in order to turn your key into a number, then you can use it to sort your table

LEFT(RIGHT([Key_column],8),4)*1000+RIGHT([Key_column],3)

2 Likes

Thank you very much for the detailed explanation. I’m trying right now

Let me know if you have questions. One tip: you can use the function in the article below to assign UNIQUEID-like values into the existing rows of your sheet - assuming you are using a sheet.

COUNT function is used incorrectly,
I’m getting the error, unfortunately, I couldn’t run

Feel free to fix my mistakes! :grinning:

There should have been a SELECT in there like so:

IF (COUNT(SELECT(Tasks[Year], [Year] = YEAR(TODAY()))) = 0,
     1,
     MAX(Tasks[Sequence]) + 1
)
2 Likes

Thank you, only when the year changes it does not start from 1

I don’t understand what you mean.

    IF (COUNT(SELECT(Tasks[Year], [Year] = YEAR(TODAY()))) = 0,
     1,
     MAX(Tasks[Sequence]) + 1
)

This formula sorts, but when the year changes, it doesn’t. for example…2020/756
2021/001… it’s not happening

INDEX(SORT(Tasks[Sequence], TRUE),1)

I did the last row like this and i use it for now

The expression was solely for the purpose of assigning your Sequence numbers. You will need to apply sorting separately

Apply the Sorting in a View

To sort your data for your View, you can use the Sort feature and set sorting by column Year first and then Sequence second…

OR…

If you have built the combine column, you can sort by that directly since you have Year + Sequence.

Apply Sorting as the result of an Expression

If you are returning your combined Year+Sequence column in a list, yo can simply surround the expression with SORT().

If you need to return the key values as in a Ref list, you will need to use the OrderBy() function. This allows you to sort the data based on OTHER columns within the table and can be a single column or multiple columns.


If you are still having trouble, provide the details of where you are trying to get a sorted list.

1 Like

Thanks for answer,

Key [Year] [Sequence] [Task Date] [concenate]
UNIQUEID() 2010 1 01.05.2010 Task 2010/1
UNIQUEID() 2012 1 02.05.2012 Task 2012/1
UNIQUEID() 2014 1 03.05.2014 Task 2014/1
UNIQUEID() 2021 1 04.05.2021 Task 2021/1
UNIQUEID() 2021 2 04.02.2021 Task 2021/2
UNIQUEID() 2014 2 04.05.2014 Task 2014/2
UNIQUEID() 2021 3 24.03.2021 Task 2021/3

my columns like this, what should i do. I made a mistake in the table