10 Replies Latest reply on Apr 30, 2012 10:48 AM by erolst




      Duplicating & Deleting Multiple Portal Rows



      I have something similar to the setup in the screenshot below.  I have an invoices table, and a quotes table.  Both tables use a related table called Items (i.e. your invoice our quote includes items).

      In script, I would like to copy all items related to the quotes table, and duplicate them, then assign or "link" them to the invoices table.  The linking could be handled by simply changing the fields so that the relationships match.

      I would also like to be able to replace all existing quote items with a copy of those items linked to an invoice.

      I was headed down the path of stepping through the related records in a loop script, and I was hesitant to go down that path without consulting the forum.

      I would like to avoid having to jump to different layouts if at all possible.

      The layouts for both invoices and quotes would contain a portal for the items table.

      Also, the function of the script would be all or nothing; that is: invoice would either not contain any items or all items would be deleted before copying all items from the quotes table (and vice versa).  There would be no partial copying of related records either.

      By the way, I am using a calc field called CALC_Table Name (just calculates it's own table name) so that my items table can be shared across both invoices and qutoes tables.

      Please let me know if anyone has ideas.

      Thanks in advance.


        • 1. Re: Duplicating & Deleting Multiple Portal Rows

          Unless I miss my guess, “to loop or not to loop” is not really the question here, since your structure seems to be flawed in several ways.

          An invoicing system normally comprises (minimally) an Invoices table, an Items/Products/Services table (depending on your business) and a Line Items join table to combine any number of invoices with any number of items etc.. I don't see this Line Item table in your RD – unless the TO called Items in your screenshot IS a join table, but then where is your Items table to store items and their attributes? From your post, I suspect that you create a new record for every line item of every quote and of every invoice in the tables called Quotes and Invoices. This is definitely the wrong way to do it …

          The other flaw is that you separated invoices and quotes. They share so many attributes (aka fields) that they're basically the same entities – as evidenced by your request to simply copy them. Basically, both quotes and invoices are things that combine a list of line items and are sent to a customer.

          My advice would be to store quotes and invoices in the same table and give them different type flags. After creating a quote or an invoice, you then create new line items in the join table, which inherit from their “parent” quote or invoice record as foreign keys a) the primary key, and b) the foreign key of the respective type. If later you want to convert a quote to an invoice, you can (depending on your business logic) either a) change the flag for the parent record and all related line items, or b) if you need to keep the "historical data", duplicate the quote, flag the new record as an invoice (and adjust other relevant data) and duplicate all or (if the quote is accepted only partially) only the desired line items of the original quote and flag them as line items of type invoice.

          If you keep the original quote record and line items (which I recommend), you can mark the quote as being converted to an invoice and vice versa by creating a new join table, where each record stores the PKs for a quote/invoice pair, so each involved quote/invoice “knows” its counterpart by “looking through” the same relationship. Want to find out how many quotes were accepted? Just count the records in this table :) 

          This structure means that not only can you duplicate or copy the relevant record(s) in their own table, it also means that for each item and customer (btw, where's your customer table?) you can see a complete history by looking into the Line Items join table; by simply setting the desired flag to filter, you can restrict any views or reports to include specific customers or items only, or filter on line items of a specific type (with e.g. quote line items being irrelevant for bookkeeping purposes), sort and report by date, price, item name etc. 

          The screenshot below shows a basic system (which e.g. assumes that you only have one customer per correspondence), which also lets you define the “correspondence types” (for lack of a better term to summarize quotes and invoices) with which to flag the “correspondence” records. If you ever need to incorporate other “correspondence types”, simply define a new type in the “CorrespondenceTypes“ tables, and you're set to go. Same is true if e.g. you decide to rename quotes to “proposals”. 

          btw, you should use number fields set up with the proper options for serial numbers as primary keys for relationships, or some other unique identifiers that have no meaning outside the context of your records and/or are not easily accessible.

          Hope this helps

          • 2. Re: Duplicating & Deleting Multiple Portal Rows

            Unfortunately, I've grossly generalized my solution - the example was just an example.

            Your idea to combine quotes and invoices is good.  I like it and I will consider it.  I also have a work orders table which has it's own set of items.

            Yes, I am using line items / join tables, but didn't illustrate in my post to simplify the question.

            Getting specific now....

            I have items, which have bom items associated (yes, join tables) with them.

            When I add an item to a quote, I want to *copy* the bom items from the item definition, into the quote's bom items.  The main reason is that we are make to order, and everytime we build something it's slightly different from the last.  We may quote it differently, and in the end (work order), we may build it differently from how we quoted it.  That's just the nature of our business.

            Basically, we have several tables (items, invoices, quotes), and they all may have a different set of line items in their BOM.

            What would be the best way to *copy* related records from an items table to a quotes table?  Assume that the items table (bom items) are the same table.

            Again, I'm trying to avoid having to jump around layouts if possible.


            Thanks in advance for all of your help.

            • 3. Re: Duplicating & Deleting Multiple Portal Rows

              You may find the script in this thread a useful source of ideas in how to loop through your records: Duplicating Bill Of Materials (duplicating portal line items)

              • 4. Re: Duplicating & Deleting Multiple Portal Rows

                Hi Oliver,

                One more thing I would like your input on. You mention strict rules for using ID's for primary keys. One of the major drawbacks I have found with filemaker drop downs is their inability to handle foreign keys/primary keys outside of their value list.

                For example, I have a value list that only includes "active" accounts. If an active account that was selected in the drop down becomes inactive, then the result in the drop down becomes only the ID number.

                So...what I do to get type ahead capability and full linking for FK/PK is have a fake drop down in back that has a value list based on the text value (not on the ID), but then once the item is selected, I script the setting of the FK value.

                This means the addition of one extra TO and an extra script. But it appears to be the best way around it.

                • 5. Re: Duplicating & Deleting Multiple Portal Rows

                  Hi Vinny,

                  funny you should mention it, we had to overcome the same problem last year.

                  Since the name (any human-readable value) you see in the value list is retrieved by following a specific, context-sensitive path into a certain TO, it shouldn't come as a surprise that the path can't be resolved if the condition isn't valid anymore.

                  As for your method, it has the disadvantage that the text value, other than the PK, need not be unique. I guess it works for account titles and the like, but if you have a list of department staff names, this method would only show you one instance of a duplicate name, since a value list shows the index for field. Even if it did show duplicates, how would the script know which of the identically named entries is the one you meant when you said "get me the PK"? 

                  Anyway, here's what I think is a more foolproof (no insult intended ;) method. We have a value list which shows active entries from a staff table (first item is StaffID PK, non-visible, second one is the full name), to mark transactions displayed in a portal. The FK we put in the Transactions table using this method is then resolved not via this value list but by a separate TO, which only goes by Staff PK, not active status. This means that the path is always resolved, regardless of the current status of the staff member whose ID was entered. This name is stored in a field in the Transactions table, and this field is displayed in the portal row.

                  For the actual entry, in the portal we put the FK field next to the name field described above, formatted with the value list and made very! small. We turned the name field itself, which is no-entry, display-only, into a button, which when clicked just goes to the FK field and triggers the value list to be opened. For a non-suspecting user, it looks like the pop-up came from the name field itself, and as explained above, since the content of the name field is resolved via a dedicated, status-insensitive TO, there is always a name if there's a FK (as long as you never delete users, but simply set them inactive).

                  In the screenshot you can see how it works. (We display a name abbreviation in the field, but for clarity, the popup itself displays the full name, as does the tooltip we put on the field.) Note the name field, and next to it, the very small (1 pt wide) field which holds the Staff FK. You can see that the value list is assembled via a filtered relationship (filter is on the status of the staff member). The script looks a bit awkward, but we found that a simple Go to Field [] with Select/Perform caused some strange behaviour, at least in our solution.

                  OK, need to do some work. If you have any suggestions for improvements on this method, please let me know.


                  • 6. Re: Duplicating & Deleting Multiple Portal Rows


                    I understand.  All of my situations (so far at least) only deal with unique fields... so I think I'm ok at this point.

                    After further consideration of your suggestion to commonize tables, I've learned that it can make other things a bit more complicated.

                    For example, an invoice will have an invoice number which is required and must be unique.  At the same time, a Sales order will not have an invoice number.  SO...I can no longer use the field validation "Unique" and "not empty", unless I re-use the same field for both cases and name them something differently in each case.

                    How did you handle this issue?

                    • 7. Re: Duplicating & Deleting Multiple Portal Rows

                      It's possible to use a related table to generate invoice numbers "on demand".

                      Link an invoices table to your current table by the currentTable's PrimaryKey.

                      Enable "allow creation of records via this relationship" for the new Invoices table.

                      Define an auto-entered serial number field, InvoiceNumber,  in INvoices for your invoice number.

                      Now this script, executed from your current table's layout:

                      Set Field [Invoices::ForeignKey ; CurrentTable::PrimaryKey ]

                      If the related record has not previously been created, this will create a new related record and Invoices::InvoiceNumber will generate an invoice number. You can then include this field on your current layout to display the invoice number.

                      • 8. Re: Duplicating & Deleting Multiple Portal Rows


                        Thank you for the idea, it sounds good to me.

                        It was suggested previously by Oliver that I should combine similar tables (i.e. invoices and sales orders) which may facilitate copying and moving data around between sales orders and invoices.

                        The problem I face now is that my invoice number field was set to validate unique and not empty.  However, if I'm working in sales order, I don't use the invoice number field...  so, I'll get an error that says invoice number cannot be blank (and this field is not available on the current layout).

                        I personally like utilizing filemaker's validation features.  But I don't know how I would use them or work around them in this case.

                        I may go back to having separate sales order and invoice tables that both share the same line items tables (parts).  For the relationships, I set them to PK/FK and also table name (tied to a calculation field in the parent table).

                        Waiting to hear back from Oliver as I'm curious how he handled this.

                        • 9. Re: Duplicating & Deleting Multiple Portal Rows

                          The problem I face now is that my invoice number field was set to validate unique and not empty.  However, if I'm working in sales order, I don't use the invoice number field...  so, I'll get an error that says invoice number cannot be blank (and this field is not available on the current layout).

                          With the change that I am suggesting, you would no longer have this field in your table. It would be replaced by the Invoices::InvoiceNumber field--where your validation rules will still work as you wiill only have a number here if you have used the script to create a record.

                          • 10. Re: Duplicating & Deleting Multiple Portal Rows

                            I second PhilModJunk. If you outsource the invoice number and only create it on demand, you can store it in a 1-to-1 child table with your internal FK IDInvoice and the invoice number itself. You can validate both fields on not empty – besides the fact that if you script the whole process, you would never even have to interact with this table, since it just stores your invoice number.

                            If you still would rather have the invoice number in the Invoices/Order table itself, you can deactivate the built-in Not empty validation for invoice number and set up a custom validation for this field. Check for the flag, or category ID, you use to differentiate between orders and invoices, and require the invoice number to be not empty only if the invoice flag / ID is set.