3 Replies Latest reply on Oct 15, 2013 5:26 AM by willrollo

    Updating portal invoice records with new tax rate

      Title

      Updating portal invoice records with new tax rate

      Post

           I have two layouts called Order  and invoice, both  based on the table - Invoicedetails. On this layout is a portal to my line items table, Invoicedetails_INVOICEITEM.

           The layouts look the same and the idea is to create an order by filling out the portal lines. When I convert this order to an Invoice the layout changes to Invoice and an invoice number is created. My questions is how do I add something to my Convert to invoice script that will update the invoice lines' totals is the tax rate changes from the point of order?

           So far I have a global VAT field called tax rate, but only effects newly created records. The Tax rate field on the layout does change but the actual figures in the poirtal do not unless I manually update them by altering the price.

           A copy of my script to convert order to invoice is below...Thank you

            

             
      •           #Has order been confirmed, Yes or No
      •      
      •           Set Variable [ $ORGINVID; Value:INVOICE DETAILS::ID_Invoice_pk ]
      •      
      •           Set Variable [ $DEPOSIT; Value:INVOICE DETAILS::Deposit Amount ]
      •      
      •           Set Variable [ $VAT; Value:invoicedetails_PREF::Tax Rate ]
      •      
      •            
      •      
      •           Show Custom Dialog [ Title: "Create Invoice"; Message: "Convert this order to an invoice? NOTE: This cannot be reversed!!"; Default Button: “Cancel”, Commit: “Yes”; Button 2: “Yes”, Commit: “No” ]
      •      
      •           If [ Get(LastMessageChoice)=1 ]
      •      
      •           Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::ID_Invoice_pk: “$ORGINVID” AND INVOICE DETAILS::Deposit raised: “Yes” ] [ Restore ]
      •      
      •           Go to Layout [ “Order” (INVOICE DETAILS) ]
      •      
      •           Exit Script [ ]
      •      
      •           End If
      •      
      •           #Isolate Record
      •      
      •           Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::ID_Invoice_pk: “$ORGINVID” AND INVOICE DETAILS::Deposit raised: “Yes” ] [ Restore ]
      •      
      •           Go to Layout [ “Invoices” (Invoices) ]
      •      
      •           New Record/Request
      •      
      •           Set Field [ Invoices::fk_Invoice details ID; $OrginvID ]
      •      
      •           Set Variable [ $Invoice Number; Value:Invoices::Invoice Number ]
      •      
      •           Set Variable [ $Invoice date; Value:Invoices::Invoice Date ]
      •      
      •           Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::ID_Invoice_pk: “$OrgInvID” AND INVOICE DETAILS::Type: “Order” ] [ Restore ]
      •      
      •           Go to Layout [ “Invoice” (INVOICE DETAILS) ]
      •      
      •           Set Field [ INVOICE DETAILS::fk_Invoice; Invoices::Invoice Number ]
      •      
      •           Set Field [ INVOICE DETAILS::fk_Invoice Date; Invoices::Invoice Date ]
      •      
      •           Set Field [ INVOICE DETAILS::Type; "Invoice" ]
      •      
      •           Set Field [ INVOICE DETAILS::Schedule Seat; "HAP" ]
      •      
      •           Freeze Window
      •      
      •           Go to Layout [ “Invoice Items” (invoicedetails_INVOICEITEM) ]
      •      
      •           New Record/Request
      •      
      •           Set Field [ invoicedetails_INVOICEITEM::ID_Invoice_fk; $OrginvID ]
      •      
      •           Set Field [ invoicedetails_INVOICEITEM::Item; "Dep" ]
      •      
      •           Set Field [ invoicedetails_INVOICEITEM::Description; "Less Deposit" ]
      •      
      •           Set Field [ invoicedetails_INVOICEITEM::Discountable; "" ]
      •      
      •           Set Field [ invoicedetails_INVOICEITEM::Price; -( 100 / ( 100 + $VAT ) ) * $Deposit ]
      •      
      •           Set Field [ invoicedetails_INVOICEITEM::Report; "Y" ]
      •      
      •           Commit Records/Requests
      •      
      •           Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::ID_Invoice_pk: “$ORGINVID” AND INVOICE DETAILS::Deposit raised: “Yes” ] [ Restore ]
      •      
      •           Go to Layout [ “Invoice” (INVOICE DETAILS) ]
      •      
      •           Set Field [ INVOICE DETAILS::Type; "Invoice" ]
      •      
      •           End If
      •      
      •           End If

            

        • 1. Re: Updating portal invoice records with new tax rate
          philmodjunk

               Any reason why you can't define the tax rate as a field in InvoiceDetails instead of InvoiceItems? Then you update one field in one record instead of in mulitple line item records.

               If some line items are taxable and others are not, a calculation field in InvoiceItems can selectively apply the tax rate in InvoiceDetails to taxable items.

               If ( Taxable ; InvoiceDetails::TaxRate * Amount )

               would compute the tax for an individual item in InvoiceItems.

               If you must update the tax rate field in every line item, you can use Go To Related Records to isolate the line items for a given Invoice details record and then use either relookup or replace field contents to update that set of records.

               1) Make sure that your code always checks for the existence of related line items or the relookup or replace field contents operation could catastrophically update large numbers of the wrong records.

               2) use relookup if you use a looked up values auto-enter setting to look up the tax rate in the first place. Otherwise, use Replace Field Contents.

          • 2. Re: Updating portal invoice records with new tax rate

                 Thank you Phil.

                 I currently do have a tax rate field in my Invoice details table. This field receives the rate from my global tax rate field that I have in a preferences table so that it can be altered when/if required, but only effects new records.

                 The VAT field in my invoice details_INVOICEITEM takes the invoice details::tax rate data when a new line item is created in invoice details_INVOICEITEM portal. 

                 I have tried to add a replace field contents by using a GTRR step and also a find step in my script - but all records in the invoice items table seem to update..I have added the new portion of the script for you to check over if that is ok...?

                 Thank you

            Set Variable [ $ORGINVID; Value:INVOICE DETAILS::ID_Invoice_pk ]

                  

                   
            •           Go to Layout [ “Invoice Items” (invoicedetails_INVOICEITEM) ]
            •      
            •           Perform Find [ Specified Find Requests: Find Records; Criteria: invoicedetails_INVOICEITEM::ID_Invoice_fk: “$OrgInvID” ] [ Restore ]
            •      
            •           Replace Field Contents [ INVOICEITEMS::VAT; Current contents ] [ No dialog ]
            • 3. Re: Updating portal invoice records with new tax rate

                   Think I have sorted it in a round about way - It works at least!

                   Thank yo for your help...

                   PS used the GTRR step with replace field contents - but I altered the tax percentage field using a variable...