8 Replies Latest reply on Dec 20, 2012 6:26 PM by philmodjunk

    Script to add a record in a different table

    Fryx

      Title

      Script to add a record in a different table

      Post

           Real novice question here, sorry. I want to make a script that will create a new record in a different (related) table, filling in the match field from the original table in the new record. Here's what I want to have happen:

           1. From a customer record in the Customers table, click "Add Order" button to run the script. Let's say we're on the Acme customer record, which is customer ID#27.

           2. The script then creates a new record in the (related) Orders table and fills in 27 in the customer ID match field, so the order is automatically related to Acme.

           I see this done in starter solutions but can't quite backtrack to apply those scripts to my database...

        • 1. Re: Script to add a record in a different table
          philmodjunk

               Set Variable [$ID ; Customer::CustomerID]
               Go to Layout [Orders]
               new Record/Request
               Set field [Orders::CustomerID ; $ID ]

               This leaves you on the orders layout with a new orders record linked to the customer record from which you started.

          • 2. Re: Script to add a record in a different table
            sgljungholm

                 Variables are the key. Using the post above I will expand.

                 You may want to bring a bunch of data over from the customer record to the order record. Maybe you want the customer address "pasted" on the order in case they move. The order is still linked to the customer by ID but some of the fields on the order stay static even if you change the customer info in the customer database.

            Set Variable [$ID ; Customer::CustomerID]

            Set Variable [$Company Name ; Customer::Company Name]

            etc.

                 Then set field in the order record.

                 You can also create a portal in the customer layout that displays orders. If you edit the link between tables you can allow records in the orders table to be created from the customers table. Then you can just type order info into a portal and it is linked automatically.

                  

            • 3. Re: Script to add a record in a different table
              Fryx

                   These are just what I was looking for--thanks for saving me hours of trial and error! Simplest solution is the portal, editing the relationship, as it gives me a nice layout summarizing orders and another summarizing payments, both of which can also be used to add new records. Thanks!

              • 4. Re: Script to add a record in a different table
                PandoMormon

                     Hi, I´m having a problem following the above posted script, whe I get to 

                Set field [Orders::CustomerID ; $ID ]

                I click on "Set Field" then "Move" then on "Specify" then the field where I want to put the value of the variable, then "Specify" but after that I haven´t been able to find where is the name of my variable.

                      

                Please help

                • 5. Re: Script to add a record in a different table
                  philmodjunk

                       When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                  • 6. Re: Script to add a record in a different table
                    PandoMormon

                         It worked like a charm!!!! Thank you so much!!! I wasted my time for three days before yesterday. Thank you again. 

                    • 7. Re: Script to add a record in a different table
                      RickWhitelaw

                           I'm missing something. Why not have a layout based on the "one" side of the relationship with a portal from the "many" side of the relationship? Why script?

                      • 8. Re: Script to add a record in a different table
                        philmodjunk

                             There are a number of reasons why you might do that. One reason might be to avoid having to scroll a portal of 50+ items to the bottom in order to add that related record. With a script like this, and a properly sorted portal, you can click a button to run the script and a new blank record will appear as the first row of the portal. There are other situations where this type of script is useful as well.

                             I will comment on a previous post where the poster showed using multiple variables to copy over multiple items of data. This should not be necessary, you should only need to do this with the ID number from the parent records primary key field. The other data either should not be copied--you can simply reference the field from the related parent record, or an auto-enter field option such as a looked up value or a calculation can copy values on each of the other fields.