Formula for automatic sorting by year?

Pointer
Participant V

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.

0 31 666
31 REPLIES 31

Pointer
Participant V

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

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.

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.

_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
)

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!

There should have been a SELECT in there like so:

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

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.

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

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)

Arithmetic expression โ€˜(LEFT(RIGHT([Key ID],8),4)*1000)โ€™ has inputs of an invalid type โ€˜Unknownโ€™

I get an error, help me thanks

Pointer
Participant V

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

Firstly, you need to update UNIQUEID() with an ID value. You need to do 2 things:

  1. Make sure to set Initial Value of your Key column is set to the UNIQUEID() function.
  2. To update the existing rows in your sheet, follow the instructions in this article:

Secondโ€ฆ
Where in your app are you trying to see/use the data in sorted form?

My goal is to give sequential number to my reports on a yearly basis.

Ok but that doesnโ€™t help to understand where it is you need to see the data sorted. So lets try thisโ€ฆ

If you are wanting the data sorted inโ€ฆ

  • โ€ฆthe Sheet. You would simply need to apply sorting to the sheet. You canโ€™t control that from Appsheet.
  • โ€ฆa View in the app. You would use the Sort option in that view, setting the columns you wish to sort by.
  • โ€ฆan expression. You would use Sort() function or ORDERBY() function depending on your sorting needs.

I made a mistake in the table, i fixed it

To give an automatic task number to the tasks I want, for each year,

I know what you said, thanks

I use a similar system in one of my applications and I use an expression within the โ€˜Initial Valueโ€™ setting for a column like this:

I believe you can get the same accomplished by going to the โ€˜Initial Valueโ€™ of your Column called [Sequence] and type in the following expression:

IF(
ISNOTBLANK(FILTER(TableName, [_THISROW].[Year] = [Year])),
MAX(SELECT(TableName[Sequence], [_THISROW].[Year] = [Year])) + 1,
1
)

Thanks a lot it works great

[Key] [Year] [Sequence] [Equipment] [Task Date] [concenate]
UNIQUEID() 2010 1 a1 1.05.2010 Task 2010/1
UNIQUEID() 2011 2 b 10.10.2011 Task 2011/1
UNIQUEID() 2011 2 b 12.11.2011 Task 2011/2
UNIQUEID() 2014 3 c 10.02.2014 Task 2014/1
UNIQUEID() 2018 4 a1 2.09.2018 Task 2018/1
UNIQUEID() 2018 4 a2 2.09.2018 Task 2018/1
UNIQUEID() 2018 4 b 2.09.2018 Task 2018/2
UNIQUEID() 2021 5 a1 27.03.2021 Task 2021/1
UNIQUEID() 2021 5 a2 27.03.2021 Task 2021/1
    IF(
ISNOTBLANK(FILTER(TableName, [_THISROW].[Year] = [Year])),
MAX(SELECT(TableName[Sequence], [_THISROW].[Year] = [Year])) + 1,
1
)

This formula works very well, I want to set the row number according to the equipment column, how can I do it.
Different equipment the same number on the same day the next number on the different day.
Thanks.

Not quiet sure what you mean with your follow up question. Also, just a heads up, _RowNumber cannot be changed because this is based on how data is stored in your table.

Hola.

Despuรฉs de probar la soluciรณn de Markus_Malessa he comprobado que solo sirve cuando estรกs trabajando con valores numรฉricos.

He modificado la fรณrmula para trabajar con caracteres alfanumรฉricos y utilizar solo una columna.

IF(ISNOTBLANK(FILTER("ACTIVIDAD", LEFT([COD],2)=RIGHT(YEAR(TODAY()),2))),
RIGHT(YEAR(TODAY()),2) & TEXT(NUMBER(RIGHT(INDEX(ORDERBY(ACTIVIDAD[COD], [COD], TRUE),1), LEN(INDEX(ORDERBY(ACTIVIDAD[COD], [COD], TRUE),1))-2)) +1),
CONCATENATE(RIGHT(YEAR(TODAY()),2), "1")
)

En este caso, la columna clave contiene los 2 dรญgitos del aรฑo seguidos del nรบmero de fila correlativo.

El resultado es este:

201
202
203

......

20756
211
212

Se pueden utilizar los 4 dรญgitos del aรฑo eliminando RIGHT(YEAR(TODAY()),2) 

Esta es la fรณrmula para incluir "0" antes del nรบmero de lรญnea

IF(ISNOTBLANK(FILTER("ACTIVIDAD", LEFT([COD],2)=RIGHT(YEAR(TODAY()),2))),
RIGHT(YEAR(TODAY()),2) & RIGHT("0000" & TEXT(NUMBER(RIGHT(INDEX(ORDERBY(ACTIVIDAD[COD], [COD], TRUE),1), LEN(INDEX(ORDERBY(ACTIVIDAD[COD], [COD], TRUE),1))-2)) +1), 4),
CONCATENATE(RIGHT(YEAR(TODAY()),2), "0001")
)

El resultado es:
200001
200002
200003
......
200015
......

200756
210001
210002

Top Labels in this Space