12 Replies Latest reply on Sep 18, 2012 2:23 AM by steve.winter

    CWP: Save Many Related Records simultaneously?

    Malcolm

      I'm using FMP API for PHP. I want to display a form in a table. Each row would represent a row in a portal. Upon saving I'd like to save all these related records simultaneously. Possible?

       

      Malcolm

        • 1. Re: CWP: Save Many Related Records simultaneously?
          mbraendle

          In principle you could do (as in the old XML/XSLT and CDML CWP times) by constructing an XML query that edits multiple portal records. See p. 46 of the FMS 12 CWP with XML Guide.

           

           

          The drawback of this technique is security. If you have set access privileges for editing records, you need to supply the credentials in the form, and they will be visible for an experienced user by looking at the HTML source code. Using guest access for XML CWP is not recommended, because the user could fetch the whole db (structure and content) via the -dbnames and -layoutnames queries. As an intermediate solution, you could specify a special privilege set with custom privileges that allow only editing the required fields, and assign it to special account (I'm using something like "XMLEdit" for such cases).

           

          The secure way however is not to save the data directly in the database, but to pass the values to another PHP page (e.g. saverecords.php), à la:

           

          <form method="post" action="saverecords.php">

            {loop through portal records}     

               <input type="text" name="{some_generated_query_parameter_name, e.g. row_1, row_2 ...}" value="{content of your portal row}"/>

             {end loop}

            {submit part}

          </form>

           

          In saverecords.php, one would loop through the $_REQUEST array and extract the row_x query parameters and values, and then construct either said XML query and save the portal records in one shot (I would prefer this) , or submit individual records using FileMaker::newEditCommand and setField(), which would put a little more strain on the server.

           

          The advantage is that one doesn't have to expose the credentials, because everything is processed server-side.

          1 of 1 people found this helpful
          • 2. Re: CWP: Save Many Related Records simultaneously?
            beverly

            This is my preferred method (loop the item rows). Say you have a 'cart', you are submitting something like:

             

            recID1=123&

            qty1=3&

            recID2=124&

            qty2=0&

            recID3=0&

            prodID3=abc75-45&

            qty3=1&

            ...

            (through #items in cart).

             

            Then, I can process each record as needed. If qty=0, delete from order. If qty changed, update. If prodID is passed and recID is 0, add a new item. All in one form submit. And I can validate before it gets to the DB as needed.

            YMMV

            1 of 1 people found this helpful
            • 4. Re: CWP: Save Many Related Records simultaneously?
              mbraendle

              Thanks Bev for this link. No, in my post, I tried to deduce the method myself.

              • 5. Re: CWP: Save Many Related Records simultaneously?
                Malcolm

                That is the method that I'm thinking of using. Beverly's response seems similar.

                 

                I am creating new records. A new order with several line items attached, very similar to a cart and at some point in the future the users may have "shopping" options. For now there will only be two options, however, the underlying design has to be ready for many options.

                 

                I had seen the post that Beverly linked to and I'm aware that it is possible to edit child records using "Field Name.Record ID". However, in this case, the parent and the children do not exist. The manual instructs us to append 0 as the record ID in this case. My concern is that I will only be able to pass one child record using that method, otherwise, how do we tell the difference between the new child records?

                • 6. Re: CWP: Save Many Related Records simultaneously?
                  Malcolm

                  At the same site there is a discussion on adding new records which finds that a script is much faster than using PHP methods.  http://blog.jsfmp.com/post/19243201571/adding-multiple-related-records-in-filemaker-php . I'm sure that this is because it is necessary to create a new connection for each record.

                   

                  The overhead from new connections is what I am trying to avoid. I was hoping that there was a method of building an array of data for the child records and passing it at the same time as the parent so that there was only one connection required.

                   

                  I will try using a script. I have very little info to pass, for the parent record I only need to pass an ID and a date. For each child record it is only an ID and a quantity

                  • 7. Re: CWP: Save Many Related Records simultaneously?
                    beverly

                    I base it on the row # not the recID. If you pass a recid then edit. The row# is just a way to loop. It does not need to be unique as a serial, but unique to distinguish the values in one row does not conflict with the next.

                    Row1 has ID1, qty1, etc.

                    Rowx has IDx, qtyx, etc.

                    Those name/value pairs cannot be confused. the "serial" nature of the row# allows you to loop when you get the results. Think of serialized dynamic variables.

                     

                    Yes, you can use the name[] construct in PHP if you desire, but remember that you need the SAME variables in each row (even if empty) to loop through them.

                     

                    And remember, that I'm allowing ADD, DELETE & EDIT at the same submit. Sure there's work on my end (in the php), but I allow that validation as needed, too, before ever getting to the DB.

                     

                    I'm NOT a big fan of triggering scripts via the web when there are equivilents in the PHP (and/or with JavaScript). But if, as Joel states in his blog posts (and I believe him - he's well qualified and experienced to make the statement) - a script is faster for multiple add (or edit), then YMMV (only you can test what works for you).

                     

                    I guess I'm not a fan of FM scripts on the web, as often the ones I see are not really written for efficiency (on web or on server). Confession: I do use FM Scripts on the web (the ones I've written or the ones I've taught others to write...)

                    Beverly

                    • 8. Re: CWP: Save Many Related Records simultaneously?
                      Malcolm

                      I'm NOT a big fan of triggering scripts via the web when there are equivilents in the PHP

                       

                      I see.

                       

                      I guess I'm not a fan of FM scripts on the web, as often the ones I see are not really written for efficiency (on web or on server). Confession: I do use FM Scripts on the web (the ones I've written or the ones I've taught others to write...)

                       

                      I understand completely: my script good, your script bad

                       

                      I'll give the script a go.

                       

                      In this app we have an ordering system used by members. We'll have this data set

                       

                      ( member ID , order date, product ID, qty, product ID, qty, ....)

                       

                      It's a tiny data bundle, even if there are dozens of items ordered. It can be passed into a script that will test the input, go to the correct layout, make an order record then populate line items via a portal. Hopefully, my script will get the Voth tick of approval.

                       

                      Malcolm

                      • 9. Re: CWP: Save Many Related Records simultaneously?
                        JoelShapiro

                        Hi Malcolm

                         

                        I too had been hoping to find a way to create multiple related records via PHP through just one hit to the DB, but I don't believe that's possible.

                         

                        As I mentioned in the blog post you cited, I do think it's worth using a FileMaker script when the number of new related records will be relatively high. 

                         

                        I used to always avoid using FM scripts in CWP, but I've had a few situations in which FM scripts have saved significant amounts of time -- both in performance and in development.

                         

                        As a general rule of thumb, I'd probably draw the line between an FM script & newAddCommand at around 10 child records.  Since you say users may often have "dozens" of shopping options, then I think the FM-script route may well become your good friend :-)

                         

                        HTH,

                        -Joel

                         

                        p.s. & thanks Bev for pointing to my blog : )

                         

                        ~~~~~~~~~~~~~~~~~~~~~

                        Joel Shapiro

                        FileMaker Pro

                        : database design

                        : web development

                        http://jsfmp.com

                        415-269-5055

                        ~~~~~~~~~~~~~~~~~~~~~

                        • 10. Re: CWP: Save Many Related Records simultaneously?
                          Malcolm

                          As a general rule of thumb, I'd probably draw the line between an FM script & newAddCommand at around 10 child records.  Since you say users may often have "dozens" of shopping options, then I think the FM-script route may well become your good friend

                           

                          thanks for the clarification. I've got two jobs that could use this. One will probably peak at 10 items, the other will begin with only one or two but it will increase quickly. I'll be able to compare them.

                           

                          malcolm

                          • 11. Re: CWP: Save Many Related Records simultaneously?
                            JoelShapiro

                            Just to be clear, there's no science behind this 10-record threshold.  It's more of a gut feeling...

                             

                            It seems to me that a project w/ an anticipated 2 or 3 related records will likely usually just have those 2 or 3 records, but something that might have 10 new related records might just as easily have 20 or more.  And since, in my testing, creating 20 records without a script took a half a second, I wouldn't want to push much beyond that.

                             

                            It'll be interesting to hear your experience w/ your two projects, especially if you do end up using different techniques for each.

                             

                            -Joel

                            • 12. Re: CWP: Save Many Related Records simultaneously?
                              steve.winter

                              Malcolm, I too have hunted for a method of creating multiple child records in a single submit - as Joel outlined in his blog post, it's possible to edit mutiple related records, and you can create a single record (with .0 recordID) at the same time, but only the one...

                               

                              You are, as others have explained, left with the options of multiple API calls, or using an FM script....