13 Replies Latest reply on Dec 22, 2016 6:36 AM by dgarmsd63

    Script Creating 2 records?


      Hello All


      I've got an odd issue, I have a little script I created that is supposed to create an order record in a related table. For some reason my script is creating 2 records. Also my "set fields" in my script are not working. I just end up with blank fields. This is all being done in FileMaker Pro 11. Please see the attached script & relationship graph images.




      D Garms

        • 1. Re: Script Creating 2 records?
          David Moyer


          I see the images you pasted from your clipboard.

          That script will not create two records (unless it's corrupted - unlikely).  After it does create the one new record, it's trying to populate it with data from an "unrelated" table.  That is, until you populate the Theme NO, no relationship exists to pull data from.

          More:  When you create the new Theme record, it probably receives a new THEME NO serial number.  The Products table needs a record with that same THEME NO in order to match and pull data from.

          • 2. Re: Script Creating 2 records?

            In addition to David's comments, you'll also want to take a look at how to use the GetField ( ) function. Assuming you have a relationship to get data from, you would want to just use the field on its own without using GetField ( ).

            • 3. Re: Script Creating 2 records?
              David Moyer

              looking further, your Show Custom Dialog will show and potentially replace the data in the current Theme record, not the new record.  If you want to collect data for the new record, place your custom dialog step after the new record step (there are many ways to handle the user data entry).  If the user clicks Cancel, you need to handle that with Get(LastMessageChoice) = 2.

              • 4. Re: Script Creating 2 records?

                David Moyer wrote:

                That script will not create two records (unless it's corrupted - unlikely).

                Or there is an OnLayoutEnter script trigger ... that's the problem with only seeing a very small portion of a solution.


                @ dgarms: using a dialog box for input brings a number of problems - correct data formats, plausibility - that you could prevent by using a layout and a number of global fields.


                Or you could present the user eg a portal into a Cartesian (= all records are related) relationship to Products to let them choose the product from a list, as opposed to having to remember their exact name.


                Finally, GetField( ) does not what I think you believe it does ..

                • 5. Re: Script Creating 2 records?

                  Hello Gentleman


                  thank you you for the tips. I don't want to come across as argumentative David, but that script exactly as it's written in that image is in fact creating 2 new records in a complerely blank table. The first record contains the theme number, order quantity & order date info from the custom dialog box and the second record is completely blank. I only want 1 new record added that's why I'm scratching my head thinking WTH? How is that creating 2 new records?


                  i originally had the relationship graph set to stock#=stock# but I was getting the same results. 1 partially filled in record and 1 completely blank record. Also thanks for the heads up on the placement of the custom dialog because these order quantities & dates are meant to be shown in a portal in my products table so my users can see a complete history of orders made for each product.




                  D Garms

                  • 6. Re: Script Creating 2 records?

                    Do you have the create via relationship turned on in the relationship graph?

                    • 7. Re: Script Creating 2 records?

                      I have some trouble bending my mind around your setup - not the least because one cannot have both images open simultaneously. Maybe add them inline? That can even be done in the regular editor.


                      Depending on your context when the script is started, you act of writing the dialog box input could well create a new related record, to which you then add a second one. (Or, as David suspects, you overwrite the fields of an already existing one.)


                      You don't happen to use FM Pro Advanced 11? Alternatively, pepper your script with some Show Dialog steps (so you can watch it "step by step"), open a second window with the target layout and note at what point the first record is added.


                      In general, if you do use Show Custom Dialog, post any input into purpose-built global fields, then reference those.

                      • 8. Re: Script Creating 2 records?
                        David Moyer

                        I made a bad assumption in my testing.  nickchapin  asked about the "allow creation of new records" - that must be it.  When I adjusted my relationship, the Show Dialog started creating new child records.

                        • 9. Re: Script Creating 2 records?

                          Yes Nick I do have the allow creation of new records turned on in my relationshi.


                          unfortunately I'm only using pro 11 erolist. It's funny just before I left the office I thought about deleting "new record/request" cause I wondered if the custom dialog could somehow be creating the additional record. Whatevers causing it, I'm 99.99% sure it's some setting I have wrong  

                          • 10. Re: Script Creating 2 records?
                            David Moyer

                            ... as I understand it better, the Show Custom Dialog will only create a new record when that setting is on, and when no related records already exists, and you are not already in the table context of the new record.  Otherwise, you overwrite existing data.

                            • 11. Re: Script Creating 2 records?

                              There are just SO many things wrong with this setup; plus you are really not providing enough information.

                              A clone of the file would be really helpful.

                              Or more detail about the relationship setup.


                              As written, the script will ALWAY create a new (empty) record if you cancel.

                              It will always over-write any existing order.

                              You're using getField but you should not use it at all.

                              You should probably use globals to capture the three dialog field values, then create the new record using those values IF the user has not clicked Cancel.

                              You need an if statement that deals with whether the use has said OK or Cancel.

                              It isn't clear how Theme No. relates to Stock# for a product. Can their be duplicate theme numbers? Duplicate Stock Numbers? Is one of these the primary key? Can multiple products be ordered under the same theme?

                              (also: this is a reply to the OP, not to David)

                              • 12. Re: Script Creating 2 records?

                                As mentioned; there are SO many things wrong with the setup you describe.

                                I believe I have recreated what you describe with the attached file.

                                It does what you describe: creates a new record AND an empty new record.

                                Of course it does; see previous reply.

                                In this file, I have also created a different, fixed, New Order script.

                                • 13. Re: Script Creating 2 records?

                                  Wow! Thanks for the sample file Bruce. I'll get that downloaded!


                                  To answer some some of the questions from your first post. There are 12000 stock numbers...all Christmas ornaments. There are between 275 to 300 theme numbers, each theme number represents a tree to be decorated with ornaments for sale to the public. There are a handful of duplicate stock#'s, but those will be set apart by assigning each item an original product ID ( occasionally 2 different vendors use the same stock# and the products are completely different ). Sometimes the decorator who assigns the theme numbers will use 142a, 142b, 142c as an example, but other than that there are no duplicate theme numbers. Multiple orders are made for each item throughout the season to keep the trees full of ornaments for sale.


                                  thanks again to everyone for all of the tips and suggestions.




                                  D Garms