Unique Project Number Formula Help

Ask the community Formulas
#1

Hey all, we are trying to get unique project numbers for our airtable. (We are managing video projects). Last year, we just did an auto number and had a formula to add 20000 to that number. 20001, 20002, 20003, etc. This year, we want the unique project number to be P21001, P21002, P21003… P for our name, 21 for Year, and 001 for the unique project number. We also want 2020 projects to be P20001, etc.

Screen Shot 2021-01-08 at 9.47.49 AM
Screen Shot 2021-01-08 at 9.47.49 AM1046×428 14.6 KB

I found in the forum the formula for getting the YY: MOD(YEAR(CREATED_TIME()), 100)

Can someone help me figure out how to make the formula that will work for any year in this way?

Thanks so much in advance!!
Austin

#2

Hi @Austin_Adesso and welcome to the community!

Couldn’t you just use the formula DATETIME_FORMAT(CREATED_TIME(),"YY") ?

"P"&DATETIME_FORMAT(CREATED_TIME(),"YY")&ID (where ID = auto number field) should work, but in format “1” in stead off “001”.

#3

Many people have asked about resetting an Autonumber field per some time-interval, and to my knowledge, a solution has not been found that utilizes only Formula Fields. It’s just one of those things that Fields alone can’t handle.

The outcome you are wanting is definitely possible with an Automation using a Script action, though. The basic setup for that would be:

  • A trigger of “New record created”
  • A script action that queries all your project records from a view that filters to show only current year projects, counts how many there are, then creates a project number that is YY & count + 1 and updates the newly created record with that project number
1 Like
#4

Could conditional formatting be another solution? If YY = 20, then xyz, if YY = 21, then abc?

#5

The problem with that is how to generate “001”, and then “002”, and so on, as the “abc” you allude to. Sure, you can create a formula with that conditional statement, but where are you getting “abc” from? You have “xyz” (ie, “393”), sure, but how are you going to generate a new automatic sequence starting with “001”?

Unless I am mistaken (which happens more often than I’d like), the only way to generate the new numbering sequence for each new year, within Airtable’s system, is via a Script.

#6

It’s not perfect, but you could create a manual rule every year that would subtract the previous years total from the automatic number generated for this years projects.

#7

Ya, you could do that, although it would require you to adjust your formula annually. Another wrench in that, which I’ve seen people complain about, is that deleted records do not reset their autonumber count – the number is not recovered if you delete a record, such as an accidentally created record. This can leave gaps in the sequence. The automation/scripting option overcomes this issue by making a fresh count every time a new record is added.

I don’t seem to have the same kind of resistance as you do to using automation/scripting, but if you need this to be done in formulas, then I think what you describe here is the way to do it. Do you still need help producing that formula, or do you have it?

#8

Hey thanks for clarifying, Jeremy. I just haven’t done automation/scripting before so it will take some research, but I appreciate the info!

1 Like
#9

There are plenty of helpful people here on the forums that are happy to help you along if you choose to struggle through learning how to script. And I would contend that it is well worth your time, anybody’s time, really, to learn how to script in Airtable’s environment for the shear power and wealth of options it opens up for you. And (opinion, not fact, coming) it’s not as daunting as it seems from where you are. I recently taught my wife, who has no coding experience, how to do some scripting with JavaScript. Together, we built a fairly complex script that creates new records based on multiple conditions. She remarked that once you pick up the syntax for most things, it’s just implementing basic logic from there.

FWIW

1 Like
#10

What would the formula be for the option I came up with? Just in case someone else wants to give that a try?

#11

Going off of the foundation @Databaser laid, where {YY Created} = DATETIME_FORMAT(CREATED_TIME(),"YY"):

SWITCH(
   {YY Created},
   "20", {PROJECTs},
   "21", {PROJECTs} - 392
)

That SWITCH() function can be added to in future years by adding a comma at the end of the last statement, and starting a new statement with the "YY", followed by a comma, and then the math required to get the Project # as a 1 for the first project of that year.

#12

Ah – looked back and was reminded you want leading zeros on the # part of the Project ID. I’ll post back in a few with that accommodation. I also didn’t actually render the year in there :man_facepalming:

#13 
SWITCH(
   {YY Created},
   "20", {YY Created} & REPT('0', 4 - LEN({PROJECTs} & '')) & {PROJECTs},
   "21", {YY Created} & REPT('0', 4 - LEN(({PROJECTs} - 392) & '')) & {PROJECTs} - 392
)

@Austin_Adesso - I didn’t actually test that - would you mind plugging it in and verifying if it works?

1 Like
#14

It helps when you got someone at home who can teach you in a relevant way I guess :slight_smile: I still haven’t found my way into it really.

#15

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.