6 Replies Latest reply on Oct 1, 2012 12:39 AM by alanfair

    Creating Related Records From A script

    alanfair

      Title

      Creating Related Records From A script

      Post

           I am still new to scripting, but seem to have an issue where i need to create two related records when i create a record in the main table and then present the data for editing by the user.

           I have broken the issue down to its basics. I have two tables as below

      Table = Inspections

           Fields

      in_id  - numeric, serial numbered Auto created

      in_description  , text, required value

      Table = Data

           d_id - numeric, serial numbered, Auto created

           d_inspection_id, numeric - holds the inspection number from in_id

           d_data - text

           The tables are related by in_id and d_inspection_id

           So I have started to created a script to create a new inspection record then the data records, for this example i will attempt to create just one, data record. My script is

           go to layout ["Inspections" (Inspections)]

           New Record/Request

           Set Variable [$$InsNumber; Value:Inspections::in_id]

           go to layout ["Data" (Data)]

           New record/request

           Set Field [Data::d_Inspection;$$InsNumber]

           go to layout ["Inspections" (Inspections)]

           However the script goes all wrong, when i attempt to go to the data layout, as it will not let me move off, the inspection layout, as the in_description field is a required field, so i get prompted to enter something in this field.

           Now i know this must be a common requirement, but my looks around the forum etc have just let me confused as to how to do this.

           So the question is, how do i go about creating a number or related records, ahead of presenting the data to the user to enter.  I have to create the inspection record to get its unique number to then supply this to the related records. But I cannot move away from the inspection record until all the necessary required data has been entered, which at this point it time it has not.

           Perhaps I am coming at this from the wrong angle, - So Help !!

            

        • 1. Re: Creating Related Records From A script
          schamblee

               You would create the secondary record(s) in the data table only when you need the record and would be after the main record in inspections was created and completed.  Basically you would have a button to create a new record as needed after the in_description field is filled out.   The purpose to use a relationship like this is that you would have more than one d_data link to the main record. (One to many relationship)  If your not going to have more than one d_data then you wouldn't need the second table at all, everything would be in one table.  The way your script is written it would create a new record in each table every time which is not what you want to happen.   For Example if you had an invoice with multipe line items you would not want to create the customer information for every line item on the invoice, you just want a new line.

                

          • 2. Re: Creating Related Records From A script
            alanfair

                 Thanks but the answer does not address the issue i have. I need to create these releated records at the time of creating the master record. Its not an invoice header/ invoice line situation, where you can create a line as needed

                 The master records contanings some static info to be completed, and the child records each represent a response to a set of questions, all of which need to be displayed to the user , as the start., and they cannot be in the same file, as the number of question can be dynamic for each master record.

                 I am currently reading about the "magic key" which seems to be the way to do this. But this seems a lot of work , for what I would have thought would be a normal requirment.  That of creating a number of records in the child without moving from the parent.

                 Thanks howver for your response

            • 3. Re: Creating Related Records From A script
              JimMac

                   Did you set up the relationship on the Table tab in Database to allow creation of "Related Records"?

                   Plus you have to be in a Layout of the Related Record [not necessary to be visable to user]

                   Jim...

              • 4. Re: Creating Related Records From A script
                JimMac

                     Ohhh i forgot... 

                      

                     in_description  , text, required value
                      
                     Validation is the reason you can leave the layout.  Validation checks occur when a record is committed.
                      
                     Since you can prevent the user from interrupting your script, remove the validation from that field and You can set your own validation check after your  script runs.
                     Jim...
                • 5. Re: Creating Related Records From A script
                  schamblee

                       Usually in most databases you create just one record related to the parent at time when needed.  Given that it looks like you are creating a test with all answer records related to the question and you are creating  the record at the beginning of the test I would suggest unchecking the "is not empty" validation and validate from your layout.  Create your own navigation buttons (Next/Previous...) and don't let the record navigate off until the answer field is not empty. So you could run your script and create all the blank records you wanted and then validate when the user answer the questions.

                       I understand what you are trying to do.  Here is a sample app that may help.

                  https://www.sugarsync.com/pf/D9559058_790_722500621

                  • 6. Re: Creating Related Records From A script
                    alanfair

                         many thanks guys