3 Replies Latest reply on Nov 20, 2014 9:47 AM by FredH

    How to "increment" a number in a 1toN relationship

    FredH

      Title

      How to "increment" a number in a 1toN relationship

      Post

      Hi experts, 

      I have two tables (A & B) with a 1 to N relationship.
      I would like to have an "incremental code" in table B grouped by its linked record in table A.  For each new record in table A, the code in table B should start at "1".  

      What is the best way, using a script, to do this knowing that I will not create all records in table B at the same time?
      I.e. :
      A1 -> B1 : 1
      A1 -> B2 : 2
      A2 -> B3 : 1
      A1 -> B4 : 3

      Thanks in advance for your input!
      Kind regards,

      _Fred_
       

        • 1. Re: How to "increment" a number in a 1toN relationship
          SteveMartino

          By "1toN" you mean "1 to many"?

          Of course you are not using these for primary keys.  What do you need to do with the data afterwards?

          Here's a file with 2 ideas, a merge field and a calculation.  I don't think you need a script unless you need to do further calculations

          https://dl.dropboxusercontent.com/u/71328624/OneToMany.zip

          • 2. Re: How to "increment" a number in a 1toN relationship
            FredH

            Hi Steve Martino,


            I indeed mean "1 to many". 
            In fact, I want to do the following :

            I have a "project" (record in table A) and for this project, I have created a "base scenario" (record in table B) which includes financial info (per record in table B, there are 5 records in table C).  

            What I would like to do now is creating alternative scenarios with different financial outcomes.  This means that for each project, I would like to have a script that creates a new record in table B (called "Simulation 1", etc) + 5 records again in table C.

            For the ease of usage, it would be ok if the original "base scenario" and its 5 records in table C just get duplicated.  
            I tried several forms of scripts but the only thing I achieve is an endless loop that duplicates thousands of times my record in table B or one of the records in table C... :-s

            Any idea what I could use here?
            Thanks in advance for your input!
            Kind regards,

            _Fred_

            • 3. Re: How to "increment" a number in a 1toN relationship
              FredH

              Hi, 

              Just an update to explain how I manage to solve this.

              I added a new record to my TableB called "code" and as my first record for TableB is created together with the creation of the record in TableA, I added to the creation script that the "code" for this record should be 0.
              When I create a new record for TableB, my script checks for the max "code" and adds 1 to this number.

              KR,

              _Fred_