1 2 3 Previous Next 73 Replies Latest reply on Jun 7, 2016 12:11 AM by BKamp

    Duplicate a record and her related records

    BKamp

      Hi guys!

       

      I need to add some features to my bookkeeping system. I started out with the standard bookkeeping system by filemaker to keep track of clients and invoices.

       

      In my invoice table I have a field (invoice/quote) to select if it's either an invoice or a quote/bid. Whenever I sent a `quote`, and the client accepts it, I would like to copy the quote` record and it's related records (invoicedata) and set the field to `invoice` instead of `quote`

      here's my table setup:

      client<->invoice<->invoice-data

       

      I created this script so far on the invoice layout:

      -set var $clientID

      -set var $invoiceID

      -duplicate record (the invoice)

      -set `invoice/quote` field to `invoice` instead of `quote

      -set client ID field with $clientID

       

      now, how can I duplicate the related invoice-data records and set their `invoiceID related field` to the newly created invoice's ID?

       

      thanks!

        • 1. Re: Duplicate a record and her related records
          Mike_Mitchell

          Fairly common requirement. There are a couple of approaches people generally use.

           

          The simpler approach is to Find the related records and then Import them back into the table. Once you've duplicated the parent record, it'll have a new invoiceID. Save that in a variable, then use Replace Field Contents to update the imported records' invoiceID with the new value.

           

          The more sophisticated approach is to use a portal and a transactional approach. What you do in this case is collect all the data from the related records in variables, then use a portal that allows record creation to loop over the data and create new records from the new parent record. That one has the advantage of being able to "back out" if you run into a problem without leaving records in an indeterminate state. It does perform slower, though, and requires you learn about how transactions work in FileMaker. There are plenty of examples on the web (just Google "FileMaker transaction").

           

          A third approach is kind of "old school", and I generally don't recommend it. In this version, you locate all the related records and loop over them, duplicating one at a time and updating its key field. It improves the fault-catching of the Import approach (because you can trap the errors on each record), but it really doesn't solve the "indeterminate state" problem. You also have to pay very close attention to the sort order of the child records, lest you wind up messing with the wrong record.

           

          Which one you choose will be based on your risk tolerance, how many users you have, and how business-critical the application is.

           

          HTH


          Mike

          • 2. Re: Duplicate a record and her related records
            BKamp

            thanks Mike! That definitely helps!

             

            it's obvious I want to try your sophisticated approach, the transactions.

            let me try to explain what I need to do so you can judge if I'm on the correct path:

            1. I need to create a script that does not have a commit step before all the stuff is done

            2. I need to store all the needed fields of my quote in variables

            3. I need to loop over each related record to the quote and store all the needed data in variables

            4. I need to create a new invoice with the variable's data (this requires a commit right?)

            5. after the invoice creation, I need to loop-create all the related portal records with the data from the variables of point 3

             

            This confuses me for now, as I've read you should not commit a record, I cant see how to complete step 4 without a commit

             

            one more question, how do I store all the related record's data in var's? Im new to this part, I guess it takes some kind of loop, does my var become an array or object in that way?

             

            Thanks Mike, we'll get there.

            • 3. Re: Duplicate a record and her related records
              Mike_Mitchell

              The requirement not to commit comes after you start creating the new records, not when you're working with the old data. The idea is to submit all the changes to the parent record and the related records at the same time, so you can back out if something goes north.

               

              Your basic steps are more or less correct. 1 isn't needed. 3 is ... sort of needed, but there are better ways to do it. 4 doesn't commit the record; creating the new record opens it. (We don't care about the old record.)

               

              To assemble the data, you can either loop over the portal on the existing record and go record-by-record (which works okay) or you can use something like ExecuteSQL to assemble a combined variable that has all the fields in rows. This is important, because if you just use something like the List function, you'll miss any empty values on the related side. I generally do something like this:

               

              Let ( [

              sqlQuery =

              "SELECT table.field1, table.field2, table.field3 " &

              "FROM table " &

              "WHERE table.foreignKey = ?"

               

              ] ;

               

              ExecuteSQL ( sqlQuery ; "|" ; "¶" )

               

              )

               

              This results in a delimited "array" like this:

               

              field1record1|field2record1|field3record1

              field1record2|field2record2|field3record2

              field1record3|field2record3|field3record3

               

              Then you can parse out the array using a combination of GetValue and Substitute. For example:

               

              Loop

                   Set Variable [ $curRow ; Value: GetValue ( $result ; 1 ) ]

                   Set Variable [ $curValues ; Value: Substitute ( $curRow ; "|" ; "¶" ) ]

                   Set Field [ table::field1 ; GetValue ( $curValues ; 1 ) ]

                   Set Field [ table::field2 ; GetValue ( $curValues ; 2 ) ]

                   Set Field [ table::field3 ; GetValue ( $curValues ; 3 ) ]

                   Set Variable [ $result ; RightValues ( ValueCount ( $result ) - 1 ) ]

                   Exit Loop If [ ValueCount ( $result ) < 1 ]

              End Loop

               

              That's just one method. There are others. You can make this one more sophisticated by using Set Field By Name instead of Set Field (so you can pass the field list in as well), but this works for a specified process.

              • 4. Re: Duplicate a record and her related records
                BKamp

                thanks for your swift answer Mike!

                 

                let me look into this, I'll get back to you.

                 

                thanks!

                • 5. Re: Duplicate a record and her related records
                  BowdenData

                  Here is a potential alternative that I have used in the past. The main thing that is nice is when you have multiple tables with child data that are related to the same parent record. You can also control what fields get duped in the child tables. In the case where I used it, I only wanted a subset of fields to be duped with the remaining ones to be blank.

                   

                  The downside is that it requires some additions to the relationship graph. If I where doing my specific task over again today, I would likely build my own routine using SQL and scripting as suggested by Mike. I might even look at using a plugin that can do SQL so I could use an INSERT function to create the new records. This is assuming that a plugin would be workable for your situation.

                   

                  In any case, have a look at the demo file from Ray. It is unlocked so you can quickly see if it might work for you.

                   

                  http://www.nightwingenterprises.com/demos2014/demo1304.html

                  • 6. Re: Duplicate a record and her related records
                    BKamp

                    Thanks BowdenData, but I will go for the "best" solution with the SQL excecution

                    • 7. Re: Duplicate a record and her related records
                      BKamp

                      Hi Mike, I've been applying your code and got stuck at the looping part where you set the $result variable.

                      I'm trying to set the variable with this calculation and it gives an error when I tried to commit the calculation:

                       

                      `RightValues ( ValueCount ( $values ) -1 )`

                       

                      It states 'There are too few parameters in this function.

                       

                      My sqlQuery looks like this now, is that fine?:

                       

                      Let ( [

                      sqlQuery =

                      "SELECT Factuurgegevens.Beschrijving, Factuurgegevens.Aantal, Factuurgegevens.Belastbaar, Factuurgegevens.Kortingspercentage, Factuurgegevens.Eenheidsprijs " &

                      "FROM Factuurgegevens " &

                      "WHERE Factuurgegevens::FACTUURNUMMER VERGELIJKINGSVELD = ?"

                       

                      ] ;

                       

                      ExecuteSQL ( sqlQuery ; "|" ; "¶" )

                       

                      )

                       

                      Factuurgegevens::FACTUURNUMMER VERGELIJKINGSVELD is the key of the table.

                      • 8. Re: Duplicate a record and her related records
                        Mike_Mitchell

                        It should be:

                         

                        Set Variable

                        • 9. Re: Duplicate a record and her related records
                          Mike_Mitchell

                          Oops. Should be:

                           

                          Set Variable

                          • 10. Re: Duplicate a record and her related records
                            BKamp

                            yes, in my script I did:

                            Set Variable [ $values ]

                             

                            then in the 'options' I try to specify the Value with:

                             

                            RightValues ( ValueCount ( $values ) -1 )

                             

                            but I cant because of the error, what am I missing?

                             

                            Thanks again!

                            • 11. Re: Duplicate a record and her related records
                              Mike_Mitchell

                              Blasted email munged up my replies. Try:

                               

                              Set Variable [ $values ; Value: RightValues ( $values ; ValueCount ( $values ) - 1 ) ]

                              • 12. Re: Duplicate a record and her related records
                                BKamp

                                Thanks, that works.

                                 

                                That means the script is finished if you ask me, however, it does not work yet.

                                here's the full script

                                 

                                this is what happens:

                                1. the go to layout is working, the '$klantID' and '$samenvatting' are successfully inserted.

                                2. I got no error message or whatsoever, but my 'factuurgegevens' fields do not get inserted (the loop part), Only one records in this table is created and has the questionmark (?) in 'beschrijving'

                                 

                                Any clues?

                                 

                                Thanks!

                                • 13. Re: Duplicate a record and her related records
                                  Mike_Mitchell

                                  You have this statement in your SQL query:

                                   

                                       WHERE Offertegegevens::OFFERTENUMMER VERGELIJKINGSVELD = ?

                                   

                                  but you have no parameter in the actual ExecuteSQL query. It should look like this:

                                   

                                       ExecuteSQL ( sqlQuery ; "|" ; "¶" ; insert value you're searching for here )

                                   

                                   

                                  • 14. Re: Duplicate a record and her related records
                                    erolst

                                    Not to mention that the SQL parser has no idea what these double colons mean … and doesn't like spaces:

                                    Mike_Mitchell wrote:

                                    You have this statement in your SQL query:

                                    WHERE Offertegegevens::OFFERTENUMMER VERGELIJKINGSVELD = ?

                                    So this would read

                                     

                                    Offertegegevens.\"OFFERTENUMMER VERGELIJKINGSVELD\" = ?

                                     

                                    btw, if the query is against the Offertegegevens table (TO, to be precise), and there is no other field of that name somewhere, you can just use the field name without the table reference.

                                     

                                    Did I mention that I'm hesitant to suggest SQL-based approaches? To paraphrase that old saying:

                                     

                                    “Try to solve an FM problem with SQL – now you have two problems.”

                                    1 2 3 Previous Next