Discussions

Expand all | Collapse all

Sequential Number Formula - Add Number to Record ID

  • 1.  Sequential Number Formula - Add Number to Record ID

    Posted 01-24-2020 12:12
    Edited by Ryan Parr 01-24-2020 12:20


    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
    ------------------------------


  • 2.  RE: Sequential Number Formula - Add Number to Record ID

    Posted 01-24-2020 14:52
    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
    ------------------------------