9 Replies Latest reply on Apr 23, 2012 4:43 PM by philmodjunk

    Script Trigger to create multiple records from one record in different tables

    PhotoBuffoon

      Title

      Script Trigger to create multiple records from one record in different tables

      Post

      Hi I'm trying to automate the creation of some records in a table from the fields of one record in another table.  

      Using FileMakerPro 11 Advanced on a Mac

      Scenario:

      In Table "Orders" I have imported many files into a container field (1 for each record).  Each record will then have a barcode scanned into a Customer ID# field so each file will match back to a related table with the customers ID#.  I then populate an order field.  The order field then populates various calculated fields based on the order type.  Each record/order can represent multiple pieces of work for completion that I need to have as individual line items that match the order# in another table.

      e.g.

      Table "Orders" Record 1

      Container Field: xyz

      File Path: user/xyz/filename

      ID#:1001

      Opt1 (OrderType):1

      Opt1_1:[If(IsEmpty(Option1)=0;FilePath;"False")]

      Opt1_2:[If(IsEmpty(Option1)=0;Filepath;"False")]

      Opt1_3:[If(IsEmpty(Option1)=0;Filepath;"False")]

              There are many other options with calculated fields as well.

      Table 2 - Requires each True calculated field above to be a separate record in another table but with the same ID#

      e.g.

      Record 1

      ID#1001

      Opt1_1User/folder1/filename

      Record 2

      ID#:1001

      Opt2_2: User/folder1/filename

         Record 3

      ID#:1001

      Opt2_3:User/folder1/filename

       

      And so on and so forth.  The actual number of potential fields is quite large (40+).

       

      I would like to trigger the creation of the records in Table 2 when I hit a submit button in a layout based on the "Orders" Table.

       

      Any ideas would be helpful.  I'm new to scripts and have not found anything on the forum that seems to be similar to what I'm trying to do.

       

      Look forward to your comments.

       

       

        • 1. Re: Script Trigger to create multiple records from one record in different tables
          philmodjunk

          Can you explain the purpose of those "40+ fields"?

          From here, they look like an unecessary complication. It would seem much simpler just to make selections in a portal to the related line items table.

          and please review your sample calculations to see if they were posted accurately in your last post:

          Opt1_1:[If(IsEmpty(Option1)=0;FilePath;"False")]

          Opt1_2:[If(IsEmpty(Option1)=0;Filepath;"False")]

          Opt1_3:[If(IsEmpty(Option1)=0;Filepath;"False")]

          All return exactly the same value either the contents of Filepath or the text string "False" depending on whether there is or is not a value in the Option1 field.

          • 2. Re: Script Trigger to create multiple records from one record in different tables
            PhotoBuffoon

            Hi Phill,

            Ok so I have followed your advice and have created a Portal;

            Tables are now ::PortraitImages, ::PrintOrder, and PrintType::

             

            I have created a portal in ::PortraitImages to show the fields: Print Type which is related to the PrintType Table, Print_ID from the PrintOrder Table and Subject_ID whic is related to the PortraitImages Table.

            When I click on a new row in the portal everything works great and it is creating my separate rows in the other table.  Question?  How can I automate the population of the portal rows based on the condition in one field in the PortraitImages Table.  

            For Example:  Say there are two types of order available 1 requiring two prints, and one requiring 3 prints, lets call them option one, and option two.  When I select Option 2, I need it to populate three rows in the portal with the appropriate line items to be printed.  I need two rows populated for Option one.  I've tried using a few script steps but whenever I try to create the second record it simply stops the script and does not populate the data in the second record.  I'm new to scripting so I'm not quite sure where to go from here?

            Any advice would be appreciated. 

            • 3. Re: Script Trigger to create multiple records from one record in different tables
              philmodjunk

              What purpose is served by a separate portal record for each print? Could you get this to work by having just one portal record, but with a number field that stores the number of prints?

              That's much simpler to implement so if it works for you, it makes a much better option.

              If not, a script trigger could take the number of prints in that number field and duplicate the record in a loop to produce the additional records. Let me know if that's really what you need here...

              • 4. Re: Script Trigger to create multiple records from one record in different tables
                PhotoBuffoon

                Hi Phil,

                The additional fields are required because they are variables that are seen by another application outside of Filemaker.  I'm sure you are familiar with variable data printing VDP?  I wish all I had to do was set the qty that would be quite simple.

                I seem to have resolved my issue by using a

                go to first portal

                field set field

                Commit record

                IF(if the next option is present then go to next portal)

                go to next portal field ....etc  I'm still ironging out the bugs but I think it will work.

                If End

                Sorry but my scripting syntax is not the best being relatively new to it.

                I'm going to muddle through with this unless you can think of something simpler?

                Cheers, PB.

                • 5. Re: Script Trigger to create multiple records from one record in different tables
                  philmodjunk

                  Never heard of Variable Data Printing.

                  When manipulating data in a portal. It's often simpler and safer to Freeze the window, use Go To Related Records to pull up a found set of the portal records on a layout based on the portal's table. Then manipulate the data as needed on this layout and finally, return to your original layout.

                  Note that steps like go to portal row do not include an option for specifying which portal is to be manipulated by the step. If you have more than one portal on your layout, you have to include code that puts the focus on the correct portal before you execute the go to portal row step. This requires using the inspector to give the portal an object name and then you use Go to Object with that exact name to put the focus into the correct portal. Using Go to Related Records and switching layouts avoids those complications and also the risk that future changes to your layout desing could accidentally mess up how your script functions.

                  If you want to create brand new related records in the portal, you can put the parent record's ID into a variable, change layouts to the portal's table and then use new record, followed by a set field step that assigns the ID to the new record and you then have a new record that will appear in your portal.

                  • 6. Re: Script Trigger to create multiple records from one record in different tables
                    PhotoBuffoon

                    Hi Phil,

                    Every bank statement you receive (be it electronic or paper) is created through VDP.  The application that creates the PDF file is told by the variables set in it which graphics, data and images to print on the page.  For example a premium customer may get a statement with a special offer because they spent more than a certain amount on their credit card.  I digress.

                    What you say makes sense.  I'll have a go this weekend and let you know where I end up.  Appreciate the advice.

                    On another note, in the same DB I'm trying to create a layout that will print all of the subjects names an ID#'s from a particular group on one page with a page break after each group.  I have been boning up on managing Parts but when I follow the advice I'm still getting subjects from the next group appearing with the previous group.  I have uploaded a screen shot of my layout.  See screen shot to see what I'm talking about.  I've tried adjusting the page break options in various parts and also the print options as suggested in some posts but I just cant get it to break for each group.

                    • 7. Re: Script Trigger to create multiple records from one record in different tables
                      philmodjunk

                      Every bank statement you receive (be it electronic or paper) is created through VDP

                      That sounds like any report you produce from almost any software you might use--including Filemaker. I don't see why that requires splitting up into individual records like you have specified here.

                      On another note, in the same DB

                      Did you add a sub summary part to your layout?

                      Is that part set up as "when sorted by" a field that identifies all records that belong to the same group?

                      Did you sort your records in a sort order that will group them on this same field?

                      Did you specify "Page break after every [1] occurrence"?

                      • 8. Re: Script Trigger to create multiple records from one record in different tables
                        PhotoBuffoon

                        Hi Phil,

                        The reason they are split is because within the graphic application they areusing one template to generate many different versions.  This means the RIP used to print the document can cache the common elements and only print the variables regardless of which combination of variables are used therefore freeing up systme resources and enabling faster rip times.  Anyway, I digress and I'm not an expert in the area so I'm no the best explain it.

                         

                        Please see screen shot of part settings:

                        • 9. Re: Script Trigger to create multiple records from one record in different tables
                          philmodjunk

                          If you then sort your records by group name, you should get this to appear with one group per page when you preview, print or save as PDF.