4 Replies Latest reply on Feb 23, 2012 11:27 AM by philmodjunk

    Avoid line item being invoiced twice



      Avoid line item being invoiced twice


      Ok this script is run by a button. What is does is the job orders are not billed all at once. They are invoiced by selecting various items from the portal. There is a y/n drop down on each line so when it comes time to invoice they select what is to be invoiced. I hope to avoid duplicate items being invoiced. So i have taken the lineitemId from the joborderitems table and copied it to the invoice items table as a check. If that item number is there, then stop, warn the user and exit back to the joborder screen. This is the part of the script that is supposed to do that. the check as you can see is the $lineid variable. When I run it, even if there are no records present i the invoice lineitem table, the error message shows up.  I tried making the jolineitem field a unique value which stops the script immediately with the revert record popup, and stops the script there at the copy process. I hope that helps.

      Go to Record/Request/Page
      [ First]
      #--------------------------------------Set variables with line item info
      Set Variable [ $lineid; Value:Job_Order_Line_Items::ID_LineItem ]
      Set Variable [ $desc; Value:Job_Order_Line_Items::Description ]
      Set Variable [ $quantity; Value:Job_Order_Line_Items::Quantity ]
      Set Variable [ $price; Value:Job_Order_Line_Items::UnitPrice]
      #--------------------------------------Go to invoice line items window
      Go to Layout[ “Invoice_LineItems_Maint” (Invoice_LineItems) ]
      New Record/Request
      Go to Record/Request/Page [ Last ]
      Set Field[ Invoice_LineItems::id_invoice ; $ID_INV ]
      Set Field[ Invoice_LineItems::Description ; $desc ]
      Set Field[ Invoice_LineItems::Quantity; $quantity ]
      Set Field[ Invoice_LineItems::UnitPrice ; $price ]
      Set Field[ Invoice_LineItems::joblineitem; $lineid ]
      #--------------------------------------If lineitem from Jobs exists, notify user
      If [ $lineid ]
      Show Custom Dialog[ Title: "Invoicing Error!"; Message: "An Invoice with one/some of the line items you selected have already been invoiced for.  Please verify your selection and try creating another invoice!"; Buttons: “OK”, “Cancel” ]
      If [ Get ( LastMessageChoice ) = "2" ]
      Revert Record/Request [ No dialog ]
      Go to Layout[ “Job Orders” (JobOrders) ]
      Exit Script [  ]
      End If
      End If

        • 1. Re: Avoid line item being invoiced twice

          hi david... i'm not sure i totally understand how your solution works, but it seems only natural that your variable $lineid will always contain a value (and thus evaluate true in your "if" statement) becaue it is coming from your job_order table and has nothing to do with invoice_line_items... am i missing something?  there is no test comparing the id from the selected job_order_line_item with the corresponding field in invoices, "joblineitem".

          i can think of several ways to resolve this:

          1. you could either remove items from the job_order portal as they are posted to an invoice (using a script to either delete them or tag them so they will be filtered out of the portal or the relationship) so that a user does not have the ability to add an item twice.

          2. you could leave all the items in the portal, but provide feedback (e.g. use an indicator) to show the user that it has been invoiced already (and prevent items tagged like this from being entered). 

          3. or you could do a test as you are suggesting, each time the user attempt to add an item..

          you are currently performing your test after you have already created a new, potentially duplicate, record requiring the added step of deleting it.  this is dangerous, because, as you have noticed, if the test does not perform as you expect, you are deleting a record you might not want to delete, or in this particular case, you are deleting every record you try to create.  i think it would be better to perform your test first and then only proceed should it evaluate correctly... correction... i just noticed that you are reverting and not deleting but i thnk the same general point still applies, why create a record if you don;t need to?

          i can think of at least 2 ways you could test for existing invoice line items that match the order line item the user is attempting to enter.  you could set up a relationship between the job line item and invoice line items based on the joblineitemid and then test for the count of related reocrds and if it is greater than 0, it is a duplicate.  the second method would be to loop through the invoice line items and test if the id for your order line item matches the stored joblineitemid for any of the records.  in either case, you would only proceed if there were no matches (avoiding the necessity to ever create/revert a record because it is a newly created duplicate).

          i hope that helps....

          • 2. Re: Avoid line item being invoiced twice

            You need to remove this step:

            Go to Record/Request/Page [last]

            New record/request puts you on the correct new record for the subsequent set field steps. Moving to the last record could result in modifying a different record.

            The reason you get the error message every time is that

            If [$lineid]

            is always true. Any non-zero value in that variable will evaluate as true. This is not in any way a test for duplicate values.

            If you set a unique values validation rule on JobLineItem, you can use this code to check for a duplicate:

            Set Error Capture [on]
            Set Field[ Invoice_LineItems::joblineitem; $lineid ]
            Commit Record
            If [ Get ( LastError ) = 504]
               Show Custom Dialog ["An Invoice..."

            • 3. Re: Avoid line item being invoiced twice

               Both are very viable solutions, but i went with philmodjunk's answer as it seemed short and sweet.  I got it to work no problem. 


              Thanks guys for the replies.



              • 4. Re: Avoid line item being invoiced twice

                I do think you should consider an approach such as John's as a long term improvment. Preventing users from making an error is generally a better interface than allowing them to make a mistake and then catching the error when they make it.