Auto number If statement

Hi Gary,

You can solve this with formula queries.

Step 1 may not be necessary, but this is how I solved it:

Step 1: Create a "Year" field (Ended up being my field number 173)

Year(ToDate([Date Created]))​

Step 2: Formula Query​

var number prevRec = Size(GetRecords("{3.LT." & [Record ID#]& "}AND{'173'.EX." & [Year] & "}", [_DBID_YOUR_DBID_HERE]));
var text yearSplit = Right(ToText([Year]), 2);

var text finalRec = 
If($prevRec < 10, "00" & $prevRec,
    If($prevRec < 100, "0" & $prevrec,
    ToText($prevRec)    

));

$yearSplit & "-"& $finalrec​

If you'd prefer it to start at 1, just add 1 to $prevRec.

Hopefully that helps!

Thanks,

Sean

------------------------------
Sean Connaughton
------------------------------