4 Replies Latest reply: May 15, 2014 10:03 AM by trsutton _ RSS

    Unique Record Number

    trsutton _

      I need to add a calculated field that will be unique for all records (similar to what you would find for a record number).  I can't use the record number because this would be duplicated next month with a new file.  I am importing GL transactions from pdf file for import into a sql database.  The last field required must be unique and be 8 characters long.  This field should increment, be stored for the next file retrieval and continue incrementing.

       

      I was thinking of combining the period field (3-4 character, ex 209, 1209) and also have date records converted to a unix numeric sequence.  Does anyone know how to join these two records?  Concatenate doesn't work because of the date being numeric. 

       

      If you have a better solution to add this field, I would really appreciate any help you could give me.

        • Unique Record Number
          Grant Perkins

          Hello trsutton and welcome to the forum.

           

          You can concatenate the date of you wish by converting it to a character string as part of the formula.

           

          You want the DtoC (DatetoCharacter) function for that. Check the Help reference for a complete description. (There is also a CtoD which may be worth reading about at the same time for future reference.

           

          For other numeric fields (non date format) there is the STR() function. Its converse is the VAL() function.

           

          The formula

           

          +DtoC()

           

          should give you your concatenated field.

           

          HTH.

           

           

          Grant