Custom Project Numbering

[Ange_Law]

Hello

I have only just started using Appsheet and I am looking for a bit of help. I am building a database which holds research project data. I want to give each project a number made up of Text, the current year and a number which progressively increases until the year-end and then starts again from 01 the next year.

For example, new projects from 2024 would be ABC2024_01, ABC2024_02 then new projects submitted in 2025 would be ABC2025_01, ABC2025_02 etc.

I am struggling to find an answer so any help would be appreciated. 

TIA

[jaichith]

It can be done if the column type is text.

Create a separate column with type as number and name it as serial number or something you want. Use this expression in initial value max(tablename[serial number])+1

Use this expression in initial value for column where you want the text to be written. 

Concatenate ("ABC", year(today()),"_",[thisrow].[serial number])

Note : if it didn't work let me know I will help you to achieve. 

[Denny774]

This doesn't start over with 01 in 2025

[jaichith]

In that case... Expression becomes longer where year should be taken into account.

But it is doable

[Scooter093]

I am in the same situation.  What stumped me was when we went from 2024 to 2025.  What would be the expression if we want to take into account going into the next year and resetting the counter\serial number.  Is there a way to do this without having some remember to reset a value at the end of the current year or the start of the new year?

I tried populating columns with the year and the count.  However, I cannot find a way to pull these values automatically when a new project is created.  A new entry will just erase those values.

[ChristhianMoren]

Para reiniciar el código al año siguiente debemos comparar la fecha del ultimo registro y si son diferente le asignamos 01 y la debería ser la siguiente:

if(YEAR(TODAY())<>YEAR([Fecha_Proyecto]),"01",ANY(SELECT(Mitabla[Cod_Proyecto],[_RowNumber]=MAX(Mitabla[_RowNumber])))
)

Espero le sirva

[Markus_Malessa]

Just provided a possible solution you can try out to see if it works for your use case.

[Denny774 · Accepted solution]

Hey man,

you're welcome 😉

CustomProjectID Field Initial Value or App Formula in Table Projects =

CONCATENATE(
  "ABC", 
  YEAR(TODAY()), 
  "_",
  SWITCH(LEN(TEXT((1 + NUMBER(EXTRACTNUMBERS(TEXT(SPLIT(INDEX(SORT(SELECT(
      Projects[CustomProjectID], CONTAINS([CustomProjectID], YEAR(TODAY()))), TRUE), 1), "_")))))))
      , 1, "000", 2, "00", 3, "0", ""),
  (1 + NUMBER(EXTRACTNUMBERS(TEXT(SPLIT(INDEX(SORT(SELECT(
      Projects[CustomProjectID], CONTAINS([CustomProjectID], YEAR(TODAY()))), TRUE), 1), "_")))))
)

Cheers

[Ange_Law]

Thanks, Denny774. It worked. 

[Scooter093]

Hi Denny774,

Thank you for the expression.

However, my counter does not increase by 1 when I new record is created.  Right now I get Request# Year_0001 for every entry I add.  I see in the expression where it looks at the Counter column, but I don't see anything that says to increase by 1 whenever a new request is added.  I tried adding a MAX function to increase the counter, but that value does not carry over into the expression you show.

Sorry for the silly questions,

[Scooter093]

Hello again.

I've playing with the solution offered and it's made my app unusable now 🙄

I tried substituting my table and column based on your solution and it still makes every new entry 0001.  I built a test app from scratch and implemented the solution and it worked fine.  However, when I changed the ABC to something more meaningful to my line of work things went off the rails and every new entry went back to 0001,

How can I implement the solution in my current App and it will pick up from the last entry?  In this case the last entry is Project#2024-0022.  When someone enters a new request, it will go to Proejct#2024-0023.

I appreciate the help and I hope I'm just missing something that will turn everything around.

Thanks

[Denny774]

Hey man,

I'm sorry for the inconvenience.
I shortened and changed it a bit and hope it works for you now.

"Project#" & TEXT(TODAY(), "yyyy") & "-" & 
  RIGHT("0000" & 
    (NUMBER(INDEX(EXTRACTNUMBERS(TEXT(SPLIT(INDEX(SORT(SELECT(
     Projects[CustomProjectID], CONTAINS(INDEX(EXTRACTHASHTAGS([CustomProjectID]), 1), "#"&TEXT(TODAY(), "yyyy"))), TRUE), 1), "-"))), 2)) + 1)
  , 4)

Cheers

[Scooter093]

Hi Denny774.

I got it to work!  Thank you!

My issue was that I was trying to put a space between the hashtag and the year. - Project# 2024-0001.  However that threw the expression off.  After I removed the space and changed the first to Project#2024-001, everything fell into place and the counter kicked in.

Thanks again for the help and patience - Sean

[Markus_Malessa]

Totally doable, but to make this work I would suggest the [KeyColumn] plus three other columns that combined make up the KeyColumn value. So probably [TextValue], [Year], and [IncrementNumber]. [KeyColumn] initial value should be:

[TextValue] & [Year] & [IncrementNumber]

Then your [IncrementNumber] column initial value should be as follows:

IF(
ISNOTBLANK(FILTER("YourTableName", [_THISROW].[Year] = [Year])),
MAX(SELECT(YourTableName[IncrementNumber], [_THISROW].[IncrementNumber] = [IncrementNumber])) + 1,
1
)

Do take note that key values that contain incrementing numbers are not a perfect solution in Appsheet, due to the nature of people adding data at the same time. One person's data entry might be successful on the screen, but get's rejected when the addition is handled server side because then Appsheet will find the matching key value.

Added an edit:

You may need tweak your [KeyColumn] initial value setting to this to match your formatting:

[TextColumn] & [Year] & "_" & IF([IncrementNumber] < 10, "0" & [IncrementNumber], [IncrementNumber])

[Denny774]

Hey mam,

here you go 😉

CustomProjectID Field Initial Value or App Formula in Table Projects =

CONCATENATE(
  "ABC", 
  YEAR(TODAY()), 
  "_",
  SWITCH(LEN(TEXT((1 + NUMBER(EXTRACTNUMBERS(TEXT(SPLIT(INDEX(SORT(SELECT(
      Projects[CustomProjectID], CONTAINS([CustomProjectID], YEAR(TODAY()))), TRUE), 1), "_")))))))
      , 1, "000", 2, "00", 3, "0", ""),
  (1 + NUMBER(EXTRACTNUMBERS(TEXT(SPLIT(INDEX(SORT(SELECT(
      Projects[CustomProjectID], CONTAINS([CustomProjectID], YEAR(TODAY()))), TRUE), 1), "_")))))
)

 Cheers