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.
So, you shouldn’t use the sequential numbers as
keys / ID… help.appsheet.com - Sequential Keys Sequential Keys help.appsheet.com
So, also consider if two people are entering records at the same time… Or what happens when one is deleted…
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
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.
CONCATENATE([YEAR],"_",COUNT(SELECT(TableName[KeyColumn],[YEAR]=[_THISROW].[YEAR]))+1)
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.
You’re welcome
User | Count |
---|---|
35 | |
30 | |
30 | |
20 | |
18 |