5 Replies Latest reply on Aug 23, 2011 10:00 AM by philmodjunk

    how to create new records in multiple tables

      Title

      how to create new records in multiple tables

      Post

      I have 3 tables(simplified for this example) for a data input form

      Demographics table:

      personID (auto), name

      Questions table:

      questionID, question

      Answer table:

      personID, questionID, answer

      I am completely stumpted as to how to display all the questions for each new person.  If I create a form with the deomgraphics table as the primary choice for the layout, then add a portal for the questions and answer table I can't get any of the questions to show on the form.  If I turn it around and create a form with the questions table as the primary choice for the layout, then add a portal for the demograpics table I can see the questions but clicking on new record creates a record for the QUESTION not a person.

      Do I need to create a new record in demographics, then expicitly, through a script, create a record for EACH question with autoID value?  or does filemaker somehow do this for you?

        • 1. Re: how to create new records in multiple tables
          Abhaya

          Hi

          Take Demographics table as a main Layout and enter all requirment field.

          On this layout call add question and answer table Layout.

          When click on "add question/answer" just go to that layout and make sure the person's ID must copied to that table and paste it by creating new record  .

          relationship should be

          person'ID=fpersonID(InQuestion Table)

          questionID=fkquestionID(in Answer table)

           

          From the both relation ship just bring 2 portal and

          I think u must see all the values for one related record in person table.

           

          • 2. Re: how to create new records in multiple tables
            philmodjunk

            Here's a simple script for creating a new related record. It does not use copy and paste as that can destroy data your user may have previously copied to the clipboard for other uses:

            Set variable [$key ; YourParentTable::PrimaryKey ]
            Go To layout [Child Table Layout]
            New Record/Request
            Set field [Child Table::ForeignKey ; $Key ]
            Go To Layout [original layout]

            Copy and paste script steps also silently fail to work if the fields they reference are not present on the current layout. Set field will still work as long as the correct layout is current at the time it executes.

            • 3. Re: how to create new records in multiple tables

              Thank you both! 

              The basic concept that one needs to explicitly, through a script, create related fields was a big point I needed to understand.

              I was hoping for some further clarification (which speaks to your point Atuf). It seems to me that my intermediate table (Answer table) would be more clear if it was set up the way I first described it.  Three columns-the first two are simply id numbers (foreign keys) that refer to the person and the question, and the third column to the answer. 

              Why are you putting the foreign keys in DIFFERENT tables? 

              Was that a typo , or am I missing the point....again?

              • 4. Re: how to create new records in multiple tables
                Abhaya

                Hii

                i am here describing the whole concept.

                You need to enter person information in person table.

                For each person you want to set up question and for each question there should be answer .

                So  to relate all the above three table

                1>person must be relate to question  by ID

                2> answer must be relate to question and person

                Another thing you need to set the ID field in other table it may be  by foreign Key or anything

                but foreign key is good for understand.

                 

                I think you want this so pls proceed ..........

                Thanks

                • 5. Re: how to create new records in multiple tables
                  philmodjunk

                  The basic concept that one needs to explicitly, through a script, create related fields [Records] was a big point I needed to understand.

                  You don't always have to do so explicitly, nor do you have to use a script in every case. It depends on the design of your system.

                  If you have "Allow creation of records via this relationship" enabled for the relationship between two tables, a single script step:

                  Set Field [RelatedTable::field ; //put expression here ]

                  Can create a related record if a related record does not already exist.

                  Even without a script, manually entering/selecting data in an empty field can create a new related record if this option is enabled for the relationship.