4 Replies Latest reply on Sep 27, 2013 7:14 AM by pinchvalve

    Copy ID from one table to a field in a related table

    pinchvalve

      Title

      Copy ID from one table to a field in a related table

      Post

           I am trying to do something that seems really simple, but I have searched and searched and cannot find an any solution that is spelled out step by step, for a real beginner.  

             
      •           I have a Layout associated with a table, "orders".    
      •      
      •            
      •      
      •           When I create a new record, it automatically assigns a unique Order ID using UUID.  
      •      
      •            
      •      
      •           I have a related table "units".  OrderID is related to for_OrderID.
      •      
      •            
      •      
      •           Each unit record also has a UUID.  
      •      
      •            
      •      
      •           Multiple units can be attached to an order, so I need to link many Units to my Orders table.
      •      
      •            
      •      
      •           I want to add units through a portal. 
      •      
      •            
      •      
      •           I am not using "allow creation of records" because I do not want the automatic new portal row to be created. 
      •      
      •            
      •      
      •           I need to write a script, tied to a button labeled "Add New Unit to Quote" 
      •      
      •            
      •      
      •           This script needs to go to the units table, create a new record, and put the current Quote ID into the appropriate field in the Units table, then return to where I started.  

            

           Copy and Paste will certainly work, but that may cause other issues for the user, so I am trying to avoid.  

           The other option is to force Filemaker not to create a new portal row when I tab past the first record.  

           Any help would be most appreciated!  

            

            

        • 1. Re: Copy ID from one table to a field in a related table
          philmodjunk
               

                    The other option is to force Filemaker not to create a new portal row when I tab past the first record.

               Tabbing past the first record should not create a new portal row. If "allow creation" is enabled, what looks like a blank new record will appear after the last real record in the portal, but no new record is actually created until data is entered into at least one field in the blank "add" portal row.

               Nonetheless many solutions use a new portal record script instead of allow creation. You can find examples of this script in the starter solutions that come with FileMaker Pro.

               Freeze Window
               Set Variable [$OrderID ; value: Orders::Order ID ] ---> this takes the place of a "copy" script step
               Go to layout ["Units" (Units) ]
               New Record/Request
               Set field [ Units::for_orderID ; $OrderID ] -----> takes the place of a "paste" script step
               Go to Layout [Original Layout ]

               One work of caution, the change in layouts can trip a great many script triggers on both the Orders and Units layouts. Sometimes these scripts have to be kept from doing anything in order for the above script to function correctly.

          • 2. Re: Copy ID from one table to a field in a related table
            pinchvalve

                 This works perfectly, and more importantly, I understand WHY it works.  That allows me to solve other issues in the future.  I can't thank you enough!  Seriously, how do I buy you a beer?

            • 3. Re: Copy ID from one table to a field in a related table
              philmodjunk

                   Being something of a Teetotaler, I only drink "virtual beers". wink

                   (but a few people have PM'd me to arrange a way to say "thank you".)

              • 4. Re: Copy ID from one table to a field in a related table
                pinchvalve

                     Oh good, Im not a drinker and have no idea about beer.  You've got mail.