Forum Discussion

RyanParr's avatar
RyanParr
Qrew Assistant Captain
6 years ago

Sequential Number Formula - Add Number to Record ID



We have a legacy ID field. I'm trying to come up with a formula that would assign a new ID based off of the record ID. However it would need to add three zero's to it, to ensure no duplicates. I think i'm on the right path below but I'm missing something:

If ([Legacy Pop Log ID]=null,([Record ID#]+1000),[Legacy Pop Log ID])

------------------------------
Ryan Parr
------------------------------
Formulas and functions
  • BrandonDrake's avatar
    BrandonDrake
    Qrew Cadet
    6 years ago
    Hi Ryan, 

    A couple of questions that could make a big difference in the solution.  Are you only using this to create a new ID for existing records, or do you plan on using this formula for the creation of new records as well?  What is being used as the key field for the table? Also, if this was an legacy ID field, why are we checking for null? If it was an ID field, they should all have a value in them.  Simply adding 3 0's would be as easy as making this field a formula text field and using this formula [Record ID#] & "000".  However, if this is going to be a primary key field or is going to be used to create ID's for new records, there is a lot more set up required because you cannot base a formula on the Record ID for new records since the ID is not known until after the record is saved.

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------
  • DouglasFolk's avatar
    DouglasFolk
    Qrew Cadet
    5 years ago
    Ryan,
    Did you ever get to a solution?

    ------------------------------
    Douglas Folk
    ------------------------------