2 Replies Latest reply on Aug 14, 2015 8:18 AM by StephaneBourgeois

    Updating unique incremental ID in a linked table when importing new records

    StephaneBourgeois

      Title

      Updating unique incremental ID in a linked table when importing new records

      Post

      Hi,

      sorry if my questions seems trivial, but I'm a complete newbie in FMPro.

      I've attached a picture of the different tables I set up and their relationships.

      I will have to import sample manifests to populate the Samples table, among others; in each manifest there is a field with a Sample ID, which will populate the Samples-Sample ID field, as well as Previous submission, which is empty if it is the first time a sample is sent to us, or contains an already existing Sample ID is this is a resubmission (thought this new aliquot will have a separate and new Sample ID itself).

      To be able to link all the records, be it physical samples, haplotypes etc, for a specific individual, I have created a table called UID. In this table there is a field with the same name. I defined it a a number, incremental and unique.

      My question is, when I import new records into the Samples table, how do I get FMPro to automatically generate a new UID when the Previous submission field is empty (and add it to the UID table, as well as the UID field in the Samples table), but when it is not empty, go fetch the UID associated with the Sample ID in this Previous ID record, populate the UID field in the Samples table with it, and of course not create a new UID (i.e. no incrementation) in the UID table.

      Example of a manifest with two records:

                                                                                                                                                                                               
      Sample IDPrevious ID
      DSF001 
      RSF034RSF02

       

       

       

       

       

       

      So, this is the first time we receive the first sample, DSF001, but we already have records for the second individual, RSF034, and its Sample ID in the Samples table is RSF02. The UID of RSF02 is 56, and the current highest UID in the UID table is 70.

      When I import this manifest into Samples, I need to get the following:

                                                                                                                                                                                                                                               
      UIDSample IDPrevious ID
      71DSF001 
      56RSF034RSF02

       

       

       

       

       

       

      and of course 71 added to UID in the UID table.

      I hope this makes sense. Is it possible to do that?

      Thanks in advance for your help,

       

      Kind regards,

       

      Stephane

      database_structure.png

        • 1. Re: Updating unique incremental ID in a linked table when importing new records
          philmodjunk

          I so no obvious purpose for FID nor setting up a two part relationship linking records by FID and UID in the same relationship. You also do not explain the purpose of this added match field in your initial post. If UID is a unique value in the UID table, there is no need for an additional field in the UID to Samples relationship that I can see here.  Leaving that observation aside for the purpose of answering your specific question...

          You can add a second occurrence of the samples table and link samples::Previous sample to Samples|Previous::SampleID. You can define the UID field in Sample to look up the UID field in Samples|Previous. If there is no matching record in Samples|Previous, UID will be blank.

          If you then enable "allow creation of records via this relationship" for UID in the samples to UID relationship, You can run a script immediately after you import this data to generate a new record in UID and copy over the UID value in this new field into only those records that do not already have a matching value in the UID field.

          The script is quite simple:

          Go to Record/Request/page [first]
          Loop
               Set Field [UID::Dummy ; 1 ]
               Go to record/request/page [next ; exit after last]
          End Loop

          For records that already have a matching record in UID, this script simply sets the Dummy field (a field that you add to UID for this specific purpose), to the value of 1. For records that do not yet have a matching record, FileMaker will create a new UID record, generate the serial number in UID and copy it across into the UID field in Samples--thereby linking the new UID record to the sample record. But this will not work with your current relationship using both UID and FID unless FID is a field that also auto-enters a value in the UID table.

          • 2. Re: Updating unique incremental ID in a linked table when importing new records
            StephaneBourgeois

            Hi,

            thanks so much for your prompt answer, it works perfectly!

             

            Kind regards,

             

            Stephane