Auto Numbering Issue

Rushhenry - the easiest way to do this would be to use an If/Then structure to test whether or not next next record is a continuation of the current series or is it the first record in a new series.  In the example above, you would set a variable equal to the Project ID of the new record being entered.  You would then use the cnt (count) function to determine if any records already exist with that Project ID.  If the function returns a value greater than 0 (zero), then you simply increment the current highest numbered record by one and continue the sequence.  If, however, the function returns a zero, that would indicate that this is the first record in a new series for a new Project ID meaning you would then start over with the number 001 for the third segment (Submittal Auto Number).

Your code would look something like this:

Using the code block above, you might want to include a length test on the subAutoNo variable to determine how  many leading zeros are required to adhere to your YYYY-PID-### format.

@NinoxUS..   No length test needed  .. use a format(subAutoNo, "000")...   Now.. maybe a check to ensure you do not go over 999.. :) 

Thanks Mike.

A couple of problems at a glance...

let c := cnt(select Submittals where text('Work Order Master Code') = y);

is overwritten by the next line...

let c := cnt(select Submittals where year('Current Date') = z);

Also, the format you used will always have a "1" as the last digit regardless what the actual number is. It should be "0" in the format() function.

I'm going to make some assumptions...

1) 'Work Order Master Code' is stored as a number since you are using the text() function

2) Each Submittal has a unique date

3) Work Orders can span multiple years because you check for the current year

I don't know what your database structure is, but I don't know how this would work in "Trigger on create". At the very least you will need to enter a date in your Submittal table so that you can autogenerate your RFI#. I put this code in "Trigger after update" in the Date field...

let t := this;
let pId := t.'Project Id';
let curYear := year(today());
let subAutoNo := cnt(select RFI where 'Project Id' = pId and year(Date) = curYear);
t.('RFI #' := curYear + "-" + format(pId, "000") + "-" + format(subAutoNo, "000"))