1 2 Previous Next 20 Replies Latest reply on Apr 22, 2011 11:01 AM by philmodjunk

    Invoice Duplication Script Trouble

    BERGSTEN

      Title

      Invoice Duplication Script Trouble

      Post

      Hi there,

      I have the attached script which is supposed to duplicate the line items on my invoice as well as any relevant contact information. The problem I'm having is that this script duplicates the contact information from the invoice I trigger the script from, but duplicates the line items from the last record in the found set.

      For now, I've been having employees find a particular invoice and execute the script when it's the only record in the found set. To be clear, I want the script to execute from the record I'm on in any set (regardless of # of records present) and have it duplicate those line items, not the ones from the last record in addition to the contact information. Right now, it's duplicating information from two separate records.

      (The end of the script just has a few more SetField commands and then "End If")

      Thanks! 

      lineitems.jpg

        • 1. Re: Invoice Duplication Script Trouble
          philmodjunk

          Your script looks correct (and familiar! Wink)

          Duplicate record adds the duplicated record to the end of the current found set. If you have more than one record present when this script runs, Go To Record/Request/Page [Previous] then moves you to the record that was last before this script started up.

          Add this code:
          Set Variable [$RecordNumber ; Get ( RecordNumber)]
          Duplicate Record
          Set Variable [$invoice_ID...
          Go To Record/Request/Page [no dialog ; $RecordNumber] //use the by calculation option

          After the duplication loop, change Go To Record/Request/page [next] to: Go To Record/Request/page [last] so that the correct invoice fields are cleared.

          • 2. Re: Invoice Duplication Script Trouble
            BERGSTEN

            I was hioping you'd see this, since it's yours =)

            Adding these steps and changing to Go To Record to "last" duplicated the invoice and ignored the set field; "" steps in the second half of the script. It also failed to duplicate line items from the invoice.

            • 3. Re: Invoice Duplication Script Trouble
              philmodjunk

              Where did you put Go To Record [Last]?

              This should be placed after:

              End Loop
              Go To Layout [Invoices]
              Go To Record/Request/Page [Last]

              • 4. Re: Invoice Duplication Script Trouble
                BERGSTEN

                That's what I have.

                It's also creating two duplications, each with all the contact information present and no line items. (I think I'm in a little over my head here =)

                • 5. Re: Invoice Duplication Script Trouble
                  philmodjunk

                  I see the problem.

                  You have:

                  Duplicate Record/Request
                  Set Variable [$RecordNumber ; value: Get ( RecordNumber )]

                  When you should have:

                  Set Variable [$RecordNumber ; Value: Get ( RecordNumber)]
                  Duplicate Record/Request

                  The way you have it, you are storing the record number of the newly created invoice record instead of the record number of the original invoice record.

                  • 6. Re: Invoice Duplication Script Trouble
                    BERGSTEN

                    I switched it, but the line items are still not appearing and the contact info isn't clearing...

                    • 7. Re: Invoice Duplication Script Trouble
                      philmodjunk

                      For some reason, the If step is evaluating as false when it should be true.

                      Here's the script I just tested and confirmed that it works. Compare it to yours and see if you can spot any discrepancies.

                      Freeze Window
                      Set Variable [ $RecordNumber; Value:Get ( RecordNumber ) ]
                      Duplicate Record/Request
                      Set Variable [ $InvoiceID; Value:Invoices::InvoiceID ]
                      Go to Record/Request/Page [ $RecordNumber ] [ No dialog ]
                      If [ not IsEmpty ( LineItems::InvoiceID ) /* Line item records exist */ ]
                             Go to Related Record [ From table: “LineItems”; Using layout: “LineItems” (LineItems) ] [ Show only related records ]
                             Go to Record/Request/Page [ First ]
                             Loop
                                     Duplicate Record/Request
                                     Set Field [ LineItems::InvoiceID; $InvoiceID ]
                                     Omit Record
                                     Go to Record/Request/Page [ First ]
                                     Omit Record
                                     Exit Loop If [ Get ( FoundCount ) = 0 ]
                             End Loop
                             Go to Layout [ original layout ]
                             Go to Record/Request/Page [ Last ]
                      End If
                      Set Field [ Invoices::ShippingDate; "" ]

                      Note: I only clear one field, just enough to make sure it works and I moved this step outside of the If-EndIF block so that it will be cleared even when there are no line items present to duplicate.

                      • 8. Re: Invoice Duplication Script Trouble
                        BERGSTEN

                        AHA! In my script, Set Variable [RecordNumber... was switched with duplicate record/request.

                        Thanks again!

                        • 9. Re: Invoice Duplication Script Trouble
                          philmodjunk

                          BTW, be careful with how you sort these invoice records. Due to Filemaker's infamous "auto sort" feature that cannot be easily disabled, the newly duplicated record could jump to a different location in the found set or, it could stay adjacent to the record from which it was duplicated. (New records and records where a field specified in the sort order is edited, will "jump" to the position in the found set consistent with the currently specified sort order after the changes are committed.)

                          The script you now have should work fine as long as the records are unsorted and unsorted records will be in the order they are created--a logical order for invoices in most cases--so you may want to "unsort" the records before performing this script. (Sorting the invoice records by Invoice_ID should also be OK as the new record will have the largest Invoice_ID and that will put it at the end of the found set as expected.)

                          • 10. Re: Invoice Duplication Script Trouble
                            BERGSTEN

                            I'll keep that in mind. It's very rare that anyone using the duplication function would need to sort records. I'm the only one that ever sorts records and I almost never use the function. Luckily, we're a small company so even if it did auto sort, it would be easy to find the descrepancy. The auto sort feature is something we've actually had trouble with in our inventory though. I've had to fight with it to edit some records that keep jumping around.

                            • 11. Re: Invoice Duplication Script Trouble
                              philmodjunk

                              One trick that I learned here in the Forum, is to start up a script and then pause it. As long as it is paused, the auto-sort doesn't kick in, but you can manually edit the records. After editing, you can cancel or continue the paused script.

                              • 12. Re: Invoice Duplication Script Trouble
                                BERGSTEN

                                Hi Phil--

                                I noticed that the script sometimes duplicates everything perfectly in the same order and other times it reverses the order of the line items. So for example, on some of duplications instead of duplicating 1 Item, 2 Item, 3 Item it orders them 3 Item, 2 Item, 1 Item. For us, this is a problem becuase of the way we order line items. Everything needs to be uniform. Drum kits first, then amps, then keyboards, etc..

                                Any ideas why it might be doing this?

                                • 13. Re: Invoice Duplication Script Trouble
                                  philmodjunk

                                  Not sure. Check sort order settings.

                                  Is there a sort order specified in your line item portal? As written, the line item records are duplicated in the order they were originally created (since there's no sorting of records after Go To Related Records pulls them up.) or in the sort order specified in the relationship (this is done in Manage | Database | Relationships). This could conceivably produce a different order when the records are displayed in your portal, but I can't tell from here.

                                  Simplest "fix" might be to specify a sort order in your line items portal that always sorts the records displayed in it in the desired order no matter what order they were created in.

                                  • 14. Re: Invoice Duplication Script Trouble
                                    BERGSTEN

                                    I don't have any sorts enabled. I didn't touch the loop portion of the script from before this post when it was duplicating correctly each time. 

                                    1 2 Previous Next