1 2 3 Previous Next 32 Replies Latest reply on Sep 10, 2012 9:50 AM by willrollo

    Autocreating a line on an invoice

      Title

      Autocreating a line on an invoice

      Post

      I want to be able to automatically create an "invoice'. I have got it working so far as the customer details etc but myline portal remains empty. I tried implementing a set field to the 'Item number" field of the line so that it would autofill from the linked products table, but it remains empty. I also want to set the price of this line from another field on the layout called Invoicedetails::Deposit. 

      Any suggestions?

      Thank you

        • 1. Re: Autocreating a line on an invoice
          philmodjunk

          You have a relationship something like this:

          Invoices::__pkInvoiceID = LineItems::_fkInvoiceID

          but perhaps with different names.

          This script portion will create one new, blank lineitem record in the portal of your invoice:

          Set variable [$InvoiceID ; Invoices::__pkInvoiceID ]
          Freeze Window
          go to layout [LineItems ( LineItems)]
          New Record/Request
          Set field [LineItems::_fkInvoiceID ; $InoviceID]
          Go to Layout [Original Layout ]

          • 2. Re: Autocreating a line on an invoice

            Works perfectly Cool

            Thanks Phil

            • 3. Re: Autocreating a line on an invoice

              Hi Phil

              I need the user to be taken back to the original "order" layout that they were on before the script beloow is initiated. I attempted to do a find by field at the end of the script but it doesnt work (In red) - could you shine a light on this if possible. I will supply more info if required..

              thanks

              • Allow User Abort [ Off ]
              • Set Error Capture [ On ]
              • If [ Get (WindowMode) ≠ 0 ]
              • #Check for browse mode
              • Exit Script [ ]
              • End If
              • #Prompt User
              • Show Custom Dialog [ Title: "New Deposit Invoice"; Message: "Would you like to create a Deposit invoice for £" & INVOICE DETAILS::Deposit Amount & " for this customer?"; Buttons: “No”, “Yes” ]
              • If [ Get (LastMessageChoice) = 1 ]
              • Exit Script [ ]
              • End If
              • If [ Get (LastMessageChoice) = 2 ]
              • #Isolate Record
              • Freeze Window
              • #Create Deposit invoice
              • Go to Layout [ “Invoice” (INVOICE DETAILS) ]
              • Set Variable [ $order; Value:INVOICE DETAILS::fk_Order ]
              • Set Variable [ $customerID; Value:INVOICE DETAILS::ID_Customer_Billing_fk ]
              • Set Variable [ $DepositID; Value:INVOICE DETAILS::Deposit Amount ]
              • New Record/Request
              • Set Field [ Invoices::fk_Invoice details ID; INVOICE DETAILS::ID_Invoice_pk ]
              • Set Field [ INVOICE DETAILS::ID_Customer_Billing_fk; $customerID ]
              • Set Field [ INVOICE DETAILS::fk_Invoice; Invoices::Invoice Number ]
              • Set Field [ INVOICE DETAILS::fk_Invoice Date; Invoices::Invoice Date ]
              • Set Field [ INVOICE DETAILS::fk_Order; $order ]
              • Set Field [ INVOICE DETAILS::Type; "Invoice" ]
              • Set Field [ INVOICE DETAILS::Deposit Amount; $DepositID ]
              • Set Field [ INVOICE DETAILS::Reference; "Deposit Invoice" ]
              • Perform Script [ “Copy Shipping to Shipping” ]
              • Set Variable [ $InvoiceID; Value:INVOICE DETAILS::ID_Invoice_pk ]
              • Freeze Window
              • Go to Layout [ “Invoice Items” (invoicedetails_INVOICEITEM) ]
              • New Record/Request
              • Set Field [ invoicedetails_INVOICEITEM::ID_Invoice_fk; $InvoiceID ]
              • Set Field [ invoicedetails_INVOICEITEM::Item; "Dep" ]
              • Set Field [ invoicedetails_INVOICEITEM::Long Description; "Deposit for Club Fender" ]
              • Set Field [ invoicedetails_INVOICEITEM::PriceUnit; INVOICE DETAILS::Deposit Amount/(INVOICE DETAILS::Tax_Percentage + 1) ]
              • Show Custom Dialog [ Title: "Settle Deposit"; Message: "Has the customer paid?"; Buttons: “Yes”, “No” ]
              • If [ Get (LastMessageChoice) = 1 ]
              • Go to Layout [ “Payment List” (PYMNT) ]
              • Show Custom Dialog [ Title: "Select Customer"; Message: "Please add a new payment for this customer"; Buttons: “OK” ]
              • Commit Records/Requests [ Skip data entry validation; No dialog ]
              • End If
              • If [ Get (LastMessageChoice) = 2 ]
              • Show Custom Dialog [ Title: "Reminder!!"; Message: "Don't forget to chase the customer for the deposit!"; Buttons: “OK” ]
              • Freeze Window
              • Enter Find Mode [ ]
              • Set Field [ INVOICE DETAILS::ID_Invoice_pk; $ID ]
              • Perform Find [ ]
              • Go to Related Record [ From table: “INVOICE DETAILS”; Using layout: “Order” (INVOICE DETAILS) ] [ Show only related records ]
              • End If
              • End If
              • 4. Re: Autocreating a line on an invoice
                philmodjunk

                WHere in your script do you assign a value to the $ID variable?

                Performing a find like this (even if you specified a value for $ID) then using Go to Related Records with these options makes no sense here.

                To which record do you want to return? The original invoice record or the new deposit invoice record?

                 

                • 5. Re: Autocreating a line on an invoice

                  I want to return to the original record. In this case, the original layout called 'order'  and is based on Invoice details.

                  The script creates a new record in invoice details, adds the lines and changes layout to 'invoice' whilst keeping the order number field the same value as the original order. So I want to be able to then return to that orginal invoice details record but on the order layout..

                  • 6. Re: Autocreating a line on an invoice
                    philmodjunk

                    But note that $ID never gets a value anywhere in your script.

                    Looks like you need to add this script step near the beginning:

                    Set Variable [$OriginalInv ; value: INVOICE DETAILS::ID_Invoice_pk ]

                    Then where you have your red script steps, replace them with:

                    Go to Layout [original layout]
                    Enter find Mode []
                    Set FIeld [ INVOICE DETAILS::ID_Invoice_pk ; $OriginalInv }
                    Perform Find[]

                    I'm assuming that your Order layout is based on the INVOICE DETAILS table occurrence. If not, you'll need to modify the set field step.

                    • 7. Re: Autocreating a line on an invoice

                      That makes sense - I shall test it out tomorrow - thank you..

                      • 8. Re: Autocreating a line on an invoice

                        Yup -that sorted it. thanks 

                        • 9. Re: Autocreating a line on an invoice

                          Hi Phil

                          Extending this topic a little futher - I am really stuck with the following problem.

                          To refresh:

                          Tables - Customers - invoice details - Invoice - All three linked. Also, anothe table, Orders, is linked to invoice details too.

                          So A customer can have an order created which has a unique order number. This order can then be converted into an invoice and given an invoice number too.

                          However (as per problem above), when an order is placed, a deposit invoice is also created . This uses all the data from customers, invoice details and the orderr number,(which is set fielded to invoicedetails::order_fk field on order creation). SO this deposit invoice is an entirely new record from invoice details, but keeps the same order number.

                          Now, what I want to do is have a field on Invoice details in the orginal record, that shows Yes if the new deposit has been paid and settled or No if still open. I cannot find a way to do this!

                           

                          Thank you

                          I have copied the create invoice script below to show how I created the new linked deposit.

                          • Allow User Abort [ Off ]
                          • Set Error Capture [ On ]
                          • If [ INVOICE DETAILS::Deposit Created = "No" ]
                          • #Isolate Record
                          • Freeze Window
                          • #Create Deposit invoice
                          • Set Field [ INVOICE DETAILS::Deposit Created; "Yes" ]
                          • Go to Layout [ “Invoice” (INVOICE DETAILS) ]
                          • Show Custom Dialog [ Title: "New Deposit Invoice"; Message: "Please Enter Deposit Amount"; Default Button: “Ok”, Commit: “Yes”; Input #1: INVOICE DETAILS::Deposit Amount, "Deposit Amount" ]
                          • Set Variable [ $order; Value:INVOICE DETAILS::fk_Order ]
                          • Set Variable [ $link; Value:INVOICE DETAILS::Deposit Link ]
                          • Set Variable [ $OriginalInvoiceID; Value:INVOICE DETAILS::ID_Invoice_pk ]
                          • Set Variable [ $customerID; Value:INVOICE DETAILS::ID_Customer_Billing_fk ]
                          • Set Variable [ $DepositID; Value:INVOICE DETAILS::Deposit Amount ]
                          • Set Variable [ $Ref; Value:"Deposit" & "/" & INVOICE DETAILS::Reference ]
                          • New Record/Request
                          • Set Field [ Invoices::fk_Invoice details ID; INVOICE DETAILS::ID_Invoice_pk ]
                          • Set Field [ INVOICE DETAILS::ID_Customer_Billing_fk; $customerID ]
                          • Set Field [ INVOICE DETAILS::fk_Invoice; Invoices::Invoice Number ]
                          • Set Field [ INVOICE DETAILS::fk_Invoice Date; Invoices::Invoice Date ]
                          • Set Field [ INVOICE DETAILS::fk_Order; $order ]
                          • Set Field [ INVOICE DETAILS::Type; "Deposit" ]
                          • Set Field [ INVOICE DETAILS::Deposit Amount; $DepositID ]
                          • Set Field [ INVOICE DETAILS::Deposit Link; $Link ]
                          • Set Field [ INVOICE DETAILS::Reference; $Ref ]
                          • Perform Script [ “Copy Shipping to Shipping” ]
                          • Set Variable [ $InvoiceID; Value:INVOICE DETAILS::ID_Invoice_pk ]
                          • Freeze Window
                          • Go to Layout [ “Invoice Items” (invoicedetails_INVOICEITEM) ]
                          • New Record/Request
                          • Set Field [ invoicedetails_INVOICEITEM::ID_Invoice_fk; $InvoiceID ]
                          • Set Field [ invoicedetails_INVOICEITEM::Item; "Dep" ]
                          • Set Field [ invoicedetails_INVOICEITEM::Long Description; "Deposit for Club Fender" ]
                          • Set Field [ invoicedetails_INVOICEITEM::PriceUnit; INVOICE DETAILS::Deposit Amount/(INVOICE DETAILS::Tax_Percentage + 1) ]
                          • Set Field [ INVOICE DETAILS::Deposit Created; "Yes" ]
                          • Show Custom Dialog [ Title: "Settle Deposit"; Message: "Has the customer paid?"; Default Button: “Yes”, Commit: “Yes”; Button 2: “No”, Commit: “No” ]
                          • If [ Get (LastMessageChoice) = 1 ]
                          • Freeze Window
                          • Go to Layout [ “Invoice” (INVOICE DETAILS) ]
                          • Set Field [ INVOICE DETAILS::Comments Invoice; "Received with thanks the sum of £" & INVOICE DETAILS::Deposit Amount ]
                          • Go to Layout [ “Payment List” (PYMNT) ]
                          • Show Custom Dialog [ Title: "Select Customer"; Message: "Please add a new payment for this customer"; Default Button: “OK”, Commit: “Yes” ]
                          • Commit Records/Requests [ Skip data entry validation; No dialog ]
                          • End If
                          • If [ Get (LastMessageChoice) = 2 ]
                          • Freeze Window
                          • Go to Layout [ “Invoice” (INVOICE DETAILS) ]
                          • Set Field [ INVOICE DETAILS::Comments Invoice; "We shall commence manufacture on receipt of this deposit." ]
                          • Show Custom Dialog [ Title: "Reminder!!"; Message: "Don't forget to chase the customer for the deposit!"; Default Button: “OK”, Commit: “Yes” ]
                          • Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::Deposit Link: “$Link” ] [ Restore ]
                          • Go to Layout [ “Order” (INVOICE DETAILS) ]
                          • Else
                          • Exit Script [ ]
                          • End If
                          • 10. Re: Autocreating a line on an invoice
                            philmodjunk

                            You'll need a way to connect the two Invoice Records such as this relationship:

                            INVOICE DETAILS-----INVOICE DETAILS Deposit

                            Invoice Details::_fkDepositInvoiceID = INVOICE DETAILS Deposit::ID_Invoice_pk

                            Where INVOICE DETAILS Deposit is a new occurrence of INVOICE DETAILS

                            Your script can be modified to use a variable to copy the pk from the newly created deposit record to _fkDepositInvoiceID in the original INVOICE DETAILS record.

                            Then you can use this relationship to show whether or not the status of the deposit invoice is paid or unpaid.

                            • 11. Re: Autocreating a line on an invoice

                              Hi Phil

                              Have got that part working (variable etc) . I have added this calculated field but it doesnt seem to work... Any ideas where I am going wrong?

                              Deposit paid

                              If(Deposit Invoice::Balance_Due > 0 ; "No" ; "Yes").

                               

                               

                              I then have a container field that has this formula

                              Deposit image

                              If (Deposit Invoice::Deposit paid = No ; Global::negative ; Global::positive)

                               

                              • 12. Re: Autocreating a line on an invoice
                                philmodjunk

                                It would seem that you don't have that part working then or something isn't correct in your relationship.

                                Try putting a portal to Deposit Invoice on your layout. I predict that the portal will be empty.

                                • 13. Re: Autocreating a line on an invoice

                                  Yup - you are right!

                                  Will look back and see what is going on...

                                  thx

                                  • 14. Re: Autocreating a line on an invoice

                                    Hi Phil

                                    The portal to InvoiceDetailsDeposit is working on the invice details layout.

                                    The linked fields now contain the same data after the script has been executed.

                                    My relationship is as follows Invoice Details::Invoice ID PK-----Invoice Details deposit::Deposit link

                                     

                                     

                                    However... The calculated field is not switching properly. I have these two fields on this order layout - Invoicedetails::deposit paid and invoicedetails::deposit image.

                                     

                                    Depost paid field is a calculated text field following this criteria -

                                    If (
                                    INVOICE DETAILS deposit::Balance_Due = "0" ; "Yes" ; "No")

                                     

                                    The deposit image field is a container field that displays a tick image or cross image based on global fields. It is calculated as follows:

                                    If(Invoice Details deposit::Balance_Due = "Yes" ; GLBL::Checkmark_g ; GLBL::Delete_g)

                                    Help appreciated!

                                    1 2 3 Previous Next