6 Replies Latest reply on Nov 15, 2014 8:15 AM by FredH

    Create multiple records at once in linked table?

    FredH

      Title

      Create multiple records at once in linked table?

      Post

      Hi experts,

      I have table A that is linked to table B that is linked to table C.  A to B is 1to1 and B to C is 1toMany.  When creating a new record in table A, I would like to also automatically create the corresponding record in B and 6 records in table C.

      For the moment, I have a simple button in my layout that creates a new record for table A which also creates the corresponding record in table B.  If I want now also to create the 5 records in table C, I suppose that my "simple button" will have to become a script?  
      Any idea what is the best way to set this up?

      Based on a date/year that is specified in table B (using data entry), I would also like to have 5 years populated in a column of table C (Y1 = year of table B, Y2 = B+1, etc.).  What would also be your approach for that?

      Thanks in advance for your input!
      Kind regards,

      _Fred_

        • 1. Re: Create multiple records at once in linked table?
          philmodjunk

          You have specified relationships similar to these:

          A----B-----<C

          A::__pkAID = B::_fkAID

          B::__BID = C::_fkBID

          On the one hand, you want to create 7 new records and on the other, you want to enter a year into the new record in B and then use that to compute values in the new Records in C. There's bit of conflict there....

          I suggest that the user enter a year for B first in a a field either in A or in a global field. then run this script from a Layout based on A:

          (Enable "Allow Creation of records via this relationship" for B in the A to B relationship.)

          Set Field [B::Year ; A::Year ] ---> a global field can be used in place of A::Year
          Set Variable [$Year ; A::Year ]
          Set Variable [$BID ; B::__pkBID ]
          Go to Layout ["C" (C) ]
          Loop
             Set Variable [$K ; Value: $K + 1 ]
             Exit Loop if [$K > 5 ]
              New Record/Request
              Set Field [C::_fkBID ; $BID ]
              Set Field [C::Year ; $Year ]
              Set Variable [$Year ; value: $Year + 1]
          End Loop
          Go to layout [original layout] ----> you may want to change to some other layout here.

          • 2. Re: Create multiple records at once in linked table?
            FredH

            Hi PhilModJunk, 

            Thanks a lot for your input! 
            In fact, I have tried to simplify my example but I see that this was not a good idea because I have issues expanding your example to my case :-[ 

            In fact, I have following relationships :
            A----B----C----D/E

            A::__pkAID = B::_fkAID
            B::__pkBID = C::_fkBID
            C
            ::__pkCID = D::_fkCID
            C::__pkCID = E::_fkCID
            For all tables, I have enables the option "
            Allow Creation of records via this relationship".  All tables have 1-to-1 relationships except for C & D --> 1 record for C should be linked to 5 records in D

            Regarding the year, my explanation was not totally correct.  In fact, I have created a field called "Year_code" in table D and this field should have values from 1 to 5 for each linked record in table C.

            Knowing this, what should be the script I have to create?
            Also, do you think it is possible to make a script that does not need to change layout as I would like to hide most of them except the active layout based on table A.

            Thanks in advance for your additional input!
            KR,

            _Fred_

            • 3. Re: Create multiple records at once in linked table?
              philmodjunk

              The fact that the script changes layouts does not mean that the user will see those layouts. The layouts involved can even be kept out of the layouts drop down list in the status area toolbar if you wish.

              The script you would create would be nearly identical to the one already posted, though you haven't explained what is needed for table E, if anything.

              But it seems fairly unusual to have so many tables linked in one to one relationships. When you have tables linked in one to one relationships, they function 99% of the time as though all the fields in both tables are all fields defined in the same record so there isn't a huge reason to separate them into two fields if the only linkage is a one to one relationship in the first place.

              #Run this script from a layout based on A
              Set Field [C::SomeField ; "anyValueYouwanthere" ] -->this creates new records in B and C
              Commit records
              Set Variable [$CID ; C::__pkCID ]
              Go to Layout ["D" (D) ]
              Loop
                 Set Variable [$K ; Value: $K + 1 ]
                 Exit Loop if [$K > 5 ]
                  New Record/Request
                  Set Field [D::_fkCID ; $CID ]
                  Set Field [D::Year ; $K ]
              End Loop
              Go to layout [original layout]

              • 4. Re: Create multiple records at once in linked table?
                FredH

                Hi PhilModJunk,

                First of all, sorry for my late reply...
                Thanks a lot for your very clear example, it worked perfectly! :-)
                You are correct that the C-D & C-E relation makes no sense so I grouped all info of D & E into one table.  For the other 1to1 relationships, they make more sense as I have the very strong intuition that they will evolve to a 1toN relationship once I will have worked out everything ;-)

                I now have another related question : 
                What kind of script should I use/create if I want to (nearly) duplicate the record in C but also its 5 related records in table D?
                Purpose is to use these records as a basis and duplicate these in order to do simulations on the new records.

                Thanks in advance for your additional input!
                Kind regards,

                _Fred_

                • 6. Re: Create multiple records at once in linked table?
                  FredH

                  Hi PhilModJunk,


                  I tried your script as described in the other thread but it did not really worked...  It duplicated 1000+ times a single record (instead of once) but did not duplicate the related records.

                  What I want to do is 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).  These records (in table B & C) have been created using the script as you explained previously in this post.

                  What I would like to do now is creating alternative scenarios with different financial outcomes. 
                  I have therefore created a layout linked to table A and I thought using different portals on this layout in order to show both the additional records in table B as the additional records in table C.

                  I am not sure this is exactly the same as your BOM example but if it is, I really struggle to implement it :-s
                  What script do you think I should use?

                  Thanks in advance for your input!
                  Kind regards,

                  _Fred_