Formula for automatic sorting by year?

Pointer · 03-22-2021 02:15 PM

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.

Pointer

Can anyone help? Thanks

scott_robinson

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

Pointer

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

scott_robinson

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?

Pointer

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

scott_robinson

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.

Pointer

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.

WillowMobileSys

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

Pointer

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

WillowMobileSys

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.

Pointer

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

WillowMobileSys

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
)

Pointer

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

WillowMobileSys

I don’t understand what you mean.

Pointer

    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

WillowMobileSys

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.

Pointer

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

Rafael_ANEIC-PY

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)

Pointer

Arithmetic expression ‘(LEFT(RIGHT([Key ID],8),4)*1000)’ has inputs of an invalid type ‘Unknown’

I get an error, help me thanks

Pointer

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

WillowMobileSys

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

Make sure to set Initial Value of your Key column is set to the UNIQUEID() function.
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?

Pointer

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

WillowMobileSys

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.

Pointer

I made a mistake in the table, i fixed it

Pointer

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

I know what you said, thanks

Markus_Malessa

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
)

Pointer

Thanks a lot it works great

Pointer

[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.

Markus_Malessa

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.

Andres_Belmonte

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)

Andres_Belmonte

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