14 Replies Latest reply on Jul 28, 2011 11:20 AM by philmodjunk

    Problem creating multiple records in Table B from Table A

    jgm_1

      Title

      Problem creating multiple records in Table B from Table A

      Post

      I am trying to generate multiple records in a table from a related table. The tables are related by TableA::tablebID which is just a numeric field to TableB::tablebID which is an Auto-enter Serial, Unique numeric.

      In table A I have a button which runs the following script:

      Perform Script ["New Record" from file: "TableB"]

      Set Variable [$var1; Value:TableA::Var1]

      Go to Layout ["TableB::Layout 2"]

      Set Field [TableB::Var1; $var1]

      Set Variable [$var2; Value:TableA::Var2]

      Go to Layout ["TableB::Layout 2"]

      Set Field [TableB::Var2; $var2]

      In table B the script "New Record" is:

      New Record/Request

      My problem is that I can only ever seem to get the variables into TableB once.

        • 1. Re: Problem creating multiple records in Table B from Table A
          ninja

          So it looks like you start in Table A, then want to make mulitple records in TableB which has an autoenter serial#.  Why is the autoenter on the child side of the relationship?  Your script makes a new record, it gets an autoenter value, then you overwrite it...what is the purpose of this?

          What does the (Perform Script ["New Record" from file: "TableB"]) do?

          Keep your script simple:
          from Table A, trap all of the info you want on the TableB record
          Go to a layout based on Table B
          Create the new record in TableB
          Setfield the info into that new record in Table B
          done.

          Note also that you have a setVariable { Set Variable [$var2; Value:TableA::Var2] } referencing a field in Table A after you've moved to a layout in Table B...you should trap that value while you are still on the Table A record before you switch layouts.

          Aside from this, you might consider making the Table B records through a portal in the TableA layout.  Then all of the relationship info would be transferred automatically...

          • 2. Re: Problem creating multiple records in Table B from Table A
            mgores

            If the two tables are related, why do you need to duplicate the fields in each table?  What you are trying to do may be easier through a portal.  If you place a portal to TableB on the TableA layout and have "allow creation of records via this relationship" checked on the TableB side of the relationship, You should be able to create new records in TableB by entering the data in the blank bottom portal row.  The TableB::tableID will be automatically entered from the TableA::tableID as you create the record.  You shouldn't need to duplicate fields on the two tables as you can show the related values from TableA on the TableB layout and vice versa.

            • 3. Re: Problem creating multiple records in Table B from Table A
              jgm_1

              I don't want to use a portal, as I'm trying to keep things simple for the users. Table A is part of an invoicing system for a costume hire business. It also has a portal to link in hire items from a separate table. Table A is fairly complicated, and at present has too many fields that users miss. We are trying to replace our existing cash register with a cash drawer (Table B) operated through Filemaker which incorporates an audit trail, & can also fix some of the issues with Table A.

              The Table A script now looks like:

              Perform Script ["New Record" from file: "TableB"]

              Set Variable [$var1; Value:TableA::Var1]

              Set Variable [$var2; Value:TableA::Var2]

              Go to Layout ["TableB::Layout 2"]

              Set Field [TableB::Var1; $var1]

              Set Field [TableB::Var2; $var2]

              Commit Records/Request

              If I don't perform the Table B script, I don't get a new record created. Now the data does get into Table B, and a new record is created there, but everything goes into record 1, not the last record. 

              • 4. Re: Problem creating multiple records in Table B from Table A
                philmodjunk

                Do it this way:

                Set Variable [$var1; Value:TableA::Var1]
                Set Variable [$var2; Value:TableA::Var2]
                Go to Layout ["TableB::Layout 2"]
                New Record/Requst
                Set Field [TableB::Var1; $var1]
                Set Field [TableB::Var2; $var2]


                The new record request will create your new record in Table B and leave it as the current record so that the subsequent set field steps modify the newly added record instead of some other record in the same table.

                • 5. Re: Problem creating multiple records in Table B from Table A
                  jgm_1

                  I've tried this, but it creates a new record in Table A not Table B. In the meantime, I've come up with a work around - I now run a script in Table B which gets the data from Record 1 in Table B and puts it in the new record.

                  • 6. Re: Problem creating multiple records in Table B from Table A
                    RickWhitelaw
                    "I don't want to use a portal, as I'm trying to keep things simple for the users" Your users shouldn't ever know whether or not they're dealing with a portal. A portal doesn't necessarily complicate your UI. RW
                    • 7. Re: Problem creating multiple records in Table B from Table A
                      jgm_1

                      I've only ever encountered portals where you can see at least some of the data on it. I only want the users to see a button.

                      Also, when I use the script debugger, I get an error 105 on the line after the Go to layout statement. It appears that Table A doesn't know about the layout name in Table B, & I don't know how to get around this.

                      • 8. Re: Problem creating multiple records in Table B from Table A
                        philmodjunk

                        I've tried this, but it creates a new record in Table A not Table B.

                        Then you need to check what layout your script is selecting in step 3. If this layout is based on Table B. The script will create a new record in Table B, not A. Using Go To Layout in this fashion as a way to select a specific table occurrence to work with is a very common scripted technique in FileMaker and one I make frequent use of.

                        "I get an error 105 (layout is missing)"

                        There's your main problem and why you are getting a new record in the wrong table.

                        Return to this script step in your script editor.

                        click the Go To Layout step to select it.

                        Select Layout... from the specify drop down.

                        Select a layout based on Table B from the list of layouts that appear.

                        • 9. Re: Problem creating multiple records in Table B from Table A
                          jgm_1

                          click the Go To Layout step to select it.

                          Select Layout... from the specify drop down.

                          Select a layout based on Table B from the list of layouts that appear.

                          I only see Table A layouts. 

                          • 10. Re: Problem creating multiple records in Table B from Table A
                            philmodjunk

                            Then you need to create a layout for table B. For the needs of this script, the layout can be a simple blank layout as long as it refers to Table B.

                            Once you've created a Table B based layout, return to the script editor and select it for this script step.

                            • 11. Re: Problem creating multiple records in Table B from Table A
                              jgm_1

                              I'm not sure I understand what you mean. Table B has a number of layouts already, but I can't see them in Table A scripts.

                              • 12. Re: Problem creating multiple records in Table B from Table A
                                philmodjunk

                                If you have Table B layouts in your current file, you'll find them listed when you go to select a layout in the Go To Layout script step.

                                Are Tables A and B defined in the same file or in separate files?

                                • 13. Re: Problem creating multiple records in Table B from Table A
                                  jgm_1

                                  They are in separate files.

                                  • 14. Re: Problem creating multiple records in Table B from Table A
                                    philmodjunk

                                    That explains it. Letting us know that the tables are in separate files would have saved a lot of posts back and forth here.

                                    My answer here assumes that you are using at least FileMaker 7 or newer. A different script will be needed if you are using an even older version of FileMaker.

                                    You need a layout to Table B in your Table A file.

                                    Open manage | Database | Relationships and use the button on the very far left, bottom of this window to create a new table occurrence box. In the dialog that pops up, select "Add Filemaker data source" from the data source drop down and select the file containing Table A.

                                    Now click Ok to exit manage | database and create a layout for this new table occurrence and you will have a layout that your Script can refer to.