2 Replies Latest reply on May 18, 2014 7:28 PM by ronaldmonasterio

    Newbie trying to concatenate a field



      Newbie trying to concatenate a field



           I'm trying to create a project number for time-lapse sequences. I want it to start with creation date field, then if the date already exists then I wanted to add a one. i.e. 00 mean only one, 01 = two sequences, etc. This will give each a unique number. So it need to search for any other records with that date. The final part is camera, that data is a pull down menu with four types of camera i.e. canon = 1, GoPro = 2.

           I added a graphic to try to make it clear.





        • 1. Re: Newbie trying to concatenate a field

               Please do not use this value as the primary key for linking records in this table to other tables. A simple auto-entered serial number will work for that.

               I am assuming that if you have one sequence recorded from camera 1 and another by camera type 2 on the same day that both will be part of the same number series.

               Set up a self join relationship that matches records by date:

               YourTable::Date = YourTable 2::Date

               Define a number field for the same date sequence (red digits)

               Use this calculation (via script or auto-enter calculaiton) to assign a value to it:

               Max ( YourTable 2::Sequence ) + 1

               If you use an auto-enter calculation, clear the "do not evaluate if all referenced fields are empty" and "do not replace existing value..." check boxes.

               Then a calculation field can combine the three values into a single string: (I am assuming that the first value is for the date may 19, 2014)

               Left ( Year ( date ) ; 2 ) & Right ( "0" & Month ( date ) ; 2 ) & Right ( "0" & Sequence ; 2 ) & CameraType


          • 2. Re: Newbie trying to concatenate a field

                 Thank you I will try that.

                 I was going use it as my main number, but I could change that.