Auto-increment YY-XXX Project Number with Reset Each January 1st

App Building
,
1

Trying to automate our Project_Number generation this year as end users have gotten increasingly creative with suffixes and spaces and it's causing headaches in various forms across our various cloud products. We're trying to get everything tied together with APIs this year, so we need to make sure there's a locked format.

Should be 24-100, 24-101, 24-102, etc. Then next Jan 1st - 25-100, 25-101, etc. Our owner wants it to start at 100, but that's not critical.

If somehow we could code only the first annual create:
{{ moment().format('YY') + "-" + "100" }}

We're using MySQL database on the backend, but our auto-incrementing row IDs will remain separate from this endeavor. Found a few resources that use MySQL scheduler jobs for the reset, but it seems like overkill for 2023.
https://stackoverflow.com/questions/34587217/how-to-reset-sequence-number-every-last-day-of-the-ending-year-automatically
https://stackoverflow.com/questions/71247497/mysql-auto-increment-from-00001-99999-and-reset-the-value-every-year

Found for Javascript forum, but no reset:
https://answers.laserfiche.com/questions/162900/Javascript-to-generate-a-sequence-number

Also found this on Retool Forum, but doesn't account for a resetting.
https://community.retool.com/t/auto-increment-id-not-working/12249

During each calendar year, I figure we could use a MySQL query to get the highest current Project_Number (LIMIT 1), then add one in Javascript when we create the next project. We're not making millions of projects, so the chance of creating a duplicate number within a few minutes is nil.

After having typed all of that, is the easiest method to just manually create the first project every year, then have some Javascript to add one for each new project? Would like to automate if possible, but depends on the level of headache.

EDIT
I created an SQL query "getLatestProjectNumber" to get me the latest Project_Number:

SELECT
  `Project_Number`
FROM projects
ORDER BY `Project_Number` DESC
LIMIT 1;

= "23-750" string, for example

Then applied a transform on the query to slice it to only the last three digits and format them as a number:

return parseInt(String(data.Project_Number).slice(-3),10)

= 750 (number)

Then on the text input field, I disabled it and added this to the default value:

{{ moment().format('YY') + "-" + (getLatestProjectNumber.data + 1) }}

= 23-751

and finally, on the SQL write action I added an event handler to trigger an update to the getLatestProjectNumber query on success. This cycles the disabled input field to the next sequential number after the user submits.

Now that it works, how can I trigger an auto-reset to 24-100 on January 1st? Maybe a Javascript if/then on the transformer?

2

bump for the edit I added

3

Hey @nickroz1, you should be able to add some extra logic to the transformer on the query to get this desired result. Here's an example:

// get the current year 
const currentYear = moment().format('YY'); 

// get year and serial from latest project 
const latestProjectNumber = String(data.Project_Number); 
const latestYear = latestProjectNumber.slice(0, 2); 
const serial = parseInt(latestProjectNumber.slice(-3),10); 

if (currentYear !== latestYear) { 
   // if current year and latest year are not the same, reset the serial to 100 
   return currentYear + "-100"; } 
else { 
   // else, increment the serial by 1 
   return currentYear + "-" + (serial + 1); 
}

With this code snippet, the query itself should return the expected project number, so you can change the default value of the text input field to just reference getLatestProjectNumber.data

2 Likes
4

You can also write this into the insert query itself, which is handy if you don't otherwise have a reason to fetch the last project number.

with pn_query as (
select 
  -- Always inserts assuming the current year (based on database locale). Concat with hyphen
   date_part(getdate()) % 100 || '-' ||
  -- Check if the year from the subquery matches the current year. If so, add 1 to the number. Otherwise, start the new year at 100.
      case 
         when mpn.year = date_part(getdate()) % 100 
            then mpn.number + 1
         else 100 
      end as project_number
(select 
   -- Querying the max PN and splitting its parts for the year and project number.
   split_part(max(project_number), '-', 1)::int as year,
   split_part(max(project_number), '-', 2)::int as number
from projects) as mpn)

insert into table (columns... project_number)
values (values... (select * from pn_query.project_number))

The subquery here isn't necessary but it's a small performance hit to make it a little more readable.

Also, not sure how necessary it is for your application but this process could result in duplicated project numbers if multiple people are saving records at the same time. You'll probably want to set a unique index on project_number.

5

This works. Thanks.