9 Replies Latest reply on Mar 18, 2011 9:58 AM by BERGSTEN

    Line Item Duplication or Template Selection for Line Items

    BERGSTEN

      Title

      Line Item Duplication or Template Selection for Line Items

      Post

      Hi there,

      I know similar questions have been asked about duplicating line items for invoices. I know that there isn't a clear cut solution to making it work in FMP... Nonetheless, I have to ask because not being able to duplicate the line items in the invoice is really becoming a problem here.

      Is there a way that I can write a script to duplicate items in the lineitems table and input them into a new invoice's portal?For example, we have a lot of reoccuring rentals that are large scale with hundreds of line items. (Qty, item#, Description, price, ext price). We've had to copy and paste the line items from one record to another to duplicate invoices. Doing this every time is a huge waste of time...(even though I have a script to duplicate the non-portal elements of the invoices).

      Another alternative: Is there a way that I can save a set of line items as a template and insert them into the body of a portal when selected? For example: The Ops Mgr enters a rental with 100 line items and saves it as "Rental A," which I can then select on a new invoice and it copies the line items from "Rental A" and pastes them into the portal on the new invoice where they are then editable for any small changes.

      Thank you! You've all helped me so much so far with this whole project and I greatly appreciate it!

        • 1. Re: Line Item Duplication or Template Selection for Line Items
          philmodjunk

          This is can be scripted:  Duplicating Bill Of Materials (duplicating portal line items)

          Feel free to respond back here if you have any questions. This thread discusses this method in terms of a bill of materials (BOM) but the table structure and script is the same for duplicating invoices with related line item records.

           

          • 2. Re: Line Item Duplication or Template Selection for Line Items
            BERGSTEN

            Will this script duplicate all of the fields including Qty, description and price?

            ...I also have a number of Set Field steps in a script to erase any payment data from the invoice. Where in this BOM script should I put it, before or after? (or does it matter)?

            • 3. Re: Line Item Duplication or Template Selection for Line Items
              philmodjunk

              As it duplicates the actual records, it duplicates all data in those records. That would include Qty, description and price.

              It matters, if you do it before, you modify the original invoice, thus you'll want to add that at the end of the duplication script.

              • 4. Re: Line Item Duplication or Template Selection for Line Items
                BERGSTEN

                I did it...Last major hurdle in a year's worth of work!

                THANK YOU.Smile

                • 5. Re: Line Item Duplication or Template Selection for Line Items
                  BERGSTEN

                  I have one question though:

                  It's not duplicating LineItems::Qty

                  The SetField[Invoices::Payment_Status; ""] isn't operating (isn't "clearing" the Payment Status field.

                  After the following, there are several more SetField steps and the "End If" --for some reason it wouldn't display...but this is the direct link to the image...

                  http://i.imgur.com/xAG2L.jpg

                  • 6. Re: Line Item Duplication or Template Selection for Line Items
                    philmodjunk

                    It's not duplicating LineItems::Qty

                    LineItems::Qty or Inventory_Link::Qty?

                    There's no part of your script that does anything with a table occurrence named LineItems. Are you sure you are using the correct relationship in go to related records? this step should use the relationship to the lineitems table--not an inventory table--but that could just be a misinterpretation of the names you are using here. (A line items table can, after all, be used as an inventory change log.)

                    The SetField[Invoices::Payment_Status; ""] isn't operating (isn't "clearing" the Payment Status field.

                    What type is this field? Is it a field of type calculation? Is it a field with an auto-entered calculation with the "do not replace existing value" option cleared? Calculation fields can't be edited and if you have such an auto-entered calculation with the "do not replace..." option cleared, it might re-calculate and re-enter the value after you modify it with set field.

                    • 7. Re: Line Item Duplication or Template Selection for Line Items
                      BERGSTEN

                      Oops! Sorry...

                      Inventory_Link is the line items table and Inventory_Link::Qty is not being duplicated, but Inventory_Link::Description/Price/ext price are. Everything in the script copies correctly except the qty, so the relationship is set up correctly.

                      Invoices::Payment Status is a drop down list text field. Previously, in my duplication script without line items, this SetField script step worked fine. The following script is on the Payment Status:

                      Case(Payment_Status = "received" ; Self ;        Invoices::Invoice_Status = "CONFIRMED" or Invoices::Invoice_Status = "FINAL INVOICE" ; "OUTSTANDING" ;         Invoices::Invoice_Status = "QUOTE" or Invoices::Invoice_Status = "NO INVOICE"  or Invoices::Invoice_Status = "DID NOT HAPPEN"  or Invoices::Invoice_Status = "IN PROGRESS"; "NO INVOICE" ; Invoice_Status = "WORK ORDER"; "NO INVOICE";         Invoices::Invoice_Status = "CANCELLED"; "CANCELLED"; Self)

                      The "Do not replace existing values..." optionis unchecked.

                      • 8. Re: Line Item Duplication or Template Selection for Line Items
                        philmodjunk

                        Please take a careful look at how the Qty field is defined. Duplicating the record does indeed duplicate this value. There is no way I can tell from here what is going on exactly, but some other factor is affecting this external to the duplication script. It could be a layout based script trigger or an auto-enter setting.

                        You might try adding these steps to see if it makes it work:

                        Set variable [$qty ; value: Inventory_link::Qty]
                        Duplicate Record
                        Set Field [Inventory_Link::Qty ; $Qty]

                        On the payment_status field, try clearing Invoice_status first, then Payment_status and see if that successfully clears the field.

                        • 9. Re: Line Item Duplication or Template Selection for Line Items
                          BERGSTEN

                          The Qty field had the "Data" box checked on the auto enter menu. I unchecked it and it works now. Hopefully I didn't check the data box for a reason...I guess we'll find out. 

                          The Invoice_Status issue still hasn't resolved, but when the new record is created from the duplication, it marks the invoice as outstanding, which is ok for now.