I am trying to calculate a YEAR_ID by concate...

[Roger_Brett]

I am trying to calculate a YEAR_ID by concatenating my current row’s YEAR with a count of how many of those years are in the table, +1 to add to the count.

So if I add 5 rows, and the first 2 are 2017, and the next and 3 are 2018, the Year_ID’s would be 2017_1, 2017_2, 2018_1, 2018_2, and 2018_3.

I just don’t understand how to write the select, list, and count.

=CONCATENATE(TEXT([Year]),"_",(COUNT(SELECT(LIST([Year]…???))+1)

Any help greatly appreciated.

[Grant_Stead]

So, you shouldn’t use the sequential numbers as

keys / ID… help.appsheet.com - Sequential Keys Sequential Keys help.appsheet.com

[Grant_Stead]

So, also consider if two people are entering records at the same time… Or what happens when one is deleted…

[Grant_Stead]

So, should it be a constant evolving virtual column calculating and reflowing the number… Turns out, sequence is hard… Easier to just not do it lol

[Roger_Brett]

Thanks Grant but the field is not a key. I have a UniqueID field for my key.

My YEAR_ID is just a generic ID that includes the number of given years in a table. It will link the data I enter to a physical sample being collected.

Also, there is no delete functionality with my app and I am the sole user. Just trying to learn the formatting for it.

[Aleksi_Alkio]

CONCATENATE([YEAR],"_",COUNT(SELECT(TableName[KeyColumn],[YEAR]=[_THISROW].[YEAR]))+1)

[Roger_Brett]

Thanks Aleksi, I was thinking I had to use LIST so the formatting was confusing. I think I get it now. This helps tremendously, thanks very much.

[Aleksi_Alkio]

You’re welcome