11 Replies Latest reply on Nov 30, 2012 9:51 AM by philmodjunk

    Duplicating invoices



      Duplicating invoices


           I am running into an issue with duplicating records.

           I am trying to figure out a way to duplicate records in my invoices/work order section of my database.

           When i go into the data entry portion of my invoice/record, and click "Duplicate record" it makes a duplicate of the record, with the client information, but none of the other information. (line items, descriptions, and pricing)

           I have tried to set up a script for this, copy pasting the invoicing information to the duplicate record, but there is more than one information field, so this process doesnt work.

           I am fairly new to filemaker, so the answer may be staring me in the face....

        • 1. Re: Duplicating invoices

               First, don't use the copy/paste scripts steps for this, there are much better alternatives that leave any data the user may have copied to the clipboard intact.

               Here's a thread that duplicates a BOM--a bill of materials. It has the same basic structure as an invoice--a parent record and a set of related records. You should be able to adapt it to your needs simpy by using your table and field names in place of those used in the script.

          Duplicating Bill Of Materials (duplicating portal line items)

               Feel free to post follow up questions here, by using the Post A Answer box if you need help with this script example. (Don't post them to the thread I've suggested here as they will likely not be seen by other users or me.)

          • 2. Re: Duplicating invoices

                 Not sure what happened, I set up the script as indicated in the BOM post.

                 When I ran the script, it made about 16 duplicates.

                 Unfortunately, i dont have advanced, so i cant run the script debugger.

                 I have attached a screen shot of the script. Can you see whats wrong with it?


            • 3. Re: Duplicating invoices

                   The debugger alone is well worth the added cost much as I'd like to see the data viewer and debugger become standard FileMaker features.

                   One substitute that you can use with Standard FileMaker is to insert show custom dialog steps in strategic points in your script and put references to fields and variables in the custom dialog so that it pops up, pauses the script and shows you the current value of the fields and variables so specified.

                   I think you need to re-open the thread and go to the very last script example. This uses go to record/request/page [first]--which isn't reliable unless you start with a found set of only the project record that you want to duplicate. If you have other records present, this script can end up on the wrong record. The last version of the script corrects for that.

                   You are also setting $Job Number to the value of Project::__kp_Job_Number for the original record. Then you duplicate the record--the kp field in this new record should auto-enter a new value, and then you set the value of $Job Number to the kp field of this new record without ever doing anything with the value you set in this same variable first. You won't find that in the example script. wink

              • 4. Re: Duplicating invoices
                 Ok, I went in and changed the Set variable.... Now i am trying to change the Go to record/request/page...and i only have the drop down box as options for this step. Thank you so much for your patience with my learning curve ;)
                • 5. Re: Duplicating invoices

                       use the By calcualtion... option.

                  • 6. Re: Duplicating invoices

                         OK. So I have been messing with this all day, trying to figure it out for myself, and I just cant seem to work it out. Unfortunately I have come into working with a partially functional database, with only a limited amount of filemaker experience.


                    • 7. Re: Duplicating invoices

                           The set field step, immediately after the Duplicate Record/Request step inside the loop, is missing the second parameter.

                           It should read:

                           Set Field [PROJECTLINEITEM::__kf_Job_Number ; $JobNumber ]

                           When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Duplicating invoices

                             Thanks for your help!

                             I finally got it to (mostly) work....

                             The only problem that I am having, Is the pricing, and pricing descriptions are not carrying over. I think this is because it is on a different layout ("Estimate worksheet" rather than "project") Is there a way to modify the script to take information from each layout?

                        • 9. Re: Duplicating invoices

                               You'll need to look past the layout to determine the table in which the data resides. Each layout refers to a specific "box" in Manage | Database | Relationships--what we call a "table occurrence" and that box refers to a specific data source table--the tables listed in Manage | database | tables. Not only can more than one layout refer to the same table occurrence, but more than one table occurrence can refer to the data source table.

                               Thus, you'll need to understand how your data is structured into tables and how they are linked in relationships before you can determine why your current script is failing to duplicate them.

                          • 10. Re: Duplicating invoices

                                 Most of the fields in this layout do not correspond with eachother....so there is no such way to establish these linked relationships

                                 The estimate layout contains information about labor and material costs, that are not listed in the line item layout.

                            • 11. Re: Duplicating invoices

                                   Again, you are thinking in terms of layouts instead of thinking in terms of the tables that store the data you see on these layouts. The fields that comprise one record in the underlying tables may or may not be visible on a given layout and thus there can still be a relationship linking the two without the fields that serve as the "match" fields being visible on these layouts.

                                   As a way to explore this, you can temporarily change the layout's view to "table view" and then use the modify to select additional fields from the layout's table so that you can examine the values stored in them.

                                   You can also find the table occurrence names used in Layout setup in these layouts in Manage | Database | Relationships and study how or if they are linked in relationships.