2 Replies Latest reply on Feb 20, 2012 6:18 PM by BarbaraSevde

    populating data from one table to another using set field



      populating data from one table to another using set field


      This is for a solution for a farrier.  I originally had the barn contact fields as part of the owner contact table (like you might have a shipping/billing address,) and showing in a tab in the owner contact layout.  I successfully used a script to populate the barn info identical to the owner info using only set fields in the script. 


      However, a situation has come up that an owner has several  horses at 3 different barns. So I decided to make a separate table named Barns, and related barns to horses (one to many.)  Horse are already related to owners (many to one.)  

      I would like the option, during data entry, to use the owner contact info to make a new record in the barn contact layout (as some owners simply have horses on their own property.)  I don’t need the relationship  between owner and barn for reporting purposes.  

            I tried modifying the same script I used in the original solution, but added

      Go to layout ( Barns)

      New Record /Request

            then continued with set fields 

      Set Field[Barns::Name_Barn; Owners::Name_Full_First_First_Name]

      Set Field[Barns::Address_Barn_Street; Owners::Address_Street]


      When I run the script, it goes to the layout, enters a new record, but the fields don’t populate. 

      I thought maybe I need a direct relationship between Barn ID and Owner ID, so I added another field occurrence Owners 2, with _kp_owner_ID related to _kf_owner_ID and changed the Owners to Owners 2 table as the source in the set fields.  That also didn’t work.  

      Anyone know what why this is not working? What am I missing?

        • 1. Re: populating data from one table to another using set field

          Set Field [Table1::Field ; Table2::Field]

          won't work unless there is a valid relationship between the current record in Table1 linking it to the correct related record in Table2. Since you are creating a new record. All such foreignkey fields are empty until you enter data into them and thus it is not yet linked to any related record and the set field steps fail to copy any data.

          The way to get around this is to use set variable to copy the relevant data into variables before creating the new record, and before changing layouts to a layout based on the second table. Then you use set field to copy the data from the variables into the fields of the new record.

          • 2. Re: populating data from one table to another using set field

            Thank you - I had to attend to some other business when I received this answer, but when I came back to it, I used your advice and it worked perfectly and simply. I appreciate your prompt and kind attention to help me.