Forum Discussion

  • SeanConnaughto1's avatar
    SeanConnaughto1
    Qrew Cadet
    3 years ago
    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
    ------------------------------