5 Replies Latest reply on Aug 10, 2016 6:22 AM by williamrollo

    how to Export a csv file with blank lines between groups of records

    williamrollo

      My Accounts software requires a file in the following format:

      Invoice number     Quantity      Desc.

      111                         1                    Cd

      111                         1                    Book

      111                          2                    Photo

       

      112                             1                    Cd

      112                              1               Book

       

      So, it needs a blank row to separate the invoices. Thanks to Brian, I now have an idea how to do this - however, I am having trouble with the script.

       

      I have an invoice details table (contains the namer, address, invoice number/ date etc) that is linked to my Invoice items table (invoice lines - price, quantity etc).

      I have created a shadow table of the invoice items (called invoice export) and it is joined via the line ID keys in each  of the tables. I have made all the fields in the invoice sport table calculated auto enters from invoice items. I have added an extra serial number field in invoice exports.

      The idea is when I convert the invoice details record into an invoice (they start as an order), then I copy the line id field from invoice items into invoice export so that the fields can populate, a new blank record is created into invoice exports. and also a serial number is created in this field. as the last record to be created is the blank line, then this record should have the latest serial number in. So when I export a group of invoices, there should be a blank record between each one.

      I haven't been able to test this completely as I cannot program the script that creates the invoice export records. please see below.. It is a very complicated database and script so please do ask me to clear anything up if not clear..

      Thank you

      PS (The script below is the first part of the original....I didnt add the rest as not required..text in red where the issue resides.... I think

       

      • Set Variable [ $ORIGINAL ID; Value:INVOICE DETAILS::ID_Invoice_pk ]
      • Set Variable [ $VAT RATE; Value:invoicedetails_CUSTOMERS__billing::VAT Rate ]
      • Set Variable [ $VAT CODE; Value:invoicedetails_CUSTOMERS__billing::VAT Code. ]
      • Set Variable [ $ALPHA CODE; Value:INVOICE DETAILS::Alphacode ]
      • Set Variable [ $ORDER NUMBER; Value:INVOICE DETAILS::fk_Order ]
      • Set Variable [ $DATE; Value:Get(CurrentDate) ]
      • Set Variable [ $DEPOSIT; Value:INVOICE DETAILS::Net Deposit ]
      • Show Custom Dialog [ Title: "Message"; Message: "Would you like to create another order for this customer or convert this order to an invoice?"; Default Button: “Invoice”, Commit: “Yes”; Button 2: “Order”, Commit: “No”; Button 3: “Cancel”, Commit: “No” ]
      • If [ Get(LastMessageChoice) = 1 ]
      • If [ not IsEmpty(INVOICE DETAILS::Billing_Country) ]
      • Perform Script [ “Convert Order to Invoice - EXPORT” ]
      • Else
      • Set Field [ INVOICE DETAILS::VAT Rate; $VAT RATE ]
      • Set Field [ INVOICE DETAILS::VAT Code; $VAT CODE ]
      • Set Variable [ $VAT CODE LETTER; Value:INVOICE DETAILS::VAT Code letter ]
      • If [ INVOICE DETAILS::Deposit raised = "No Deposit" ]
      • Show Custom Dialog [ Title: "Create Invoice"; Message: "Convert this order to an invoice?"; Default Button: “Cancel”, Commit: “Yes”; Button 2: “Yes”, Commit: “No” ]
      • If [ Get(LastMessageChoice)=1 ]
      • Exit Script [ ]
      • End If
      • #Isolate Record
      • Go to Layout [ “Invoices” (Invoices) ]
      • New Record/Request
      • Set Field [ Invoices::fk_Invoice details ID; $ORIGINAL ID ]
      • 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: “$ORIGINAL ID” ] [ Restore ]
      • Freeze Window
      • 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 Related Record [ From table: “invoicedetails_INVOICEITEM”; Using layout: “Invoice Items” (invoicedetails_INVOICEITEM) ] [ Show only related records; Match found set ]
      • Go to Record/Request/Page [ First ]
      • Set Variable [ $Export ID; Value:invoicedetails_INVOICEITEM::ID_InvoiceLI_pk ]
      • Loop
      • Go to Layout [ “Invoice Items Export” (Invoice Items Export) ]
      • New Record/Request
      • Set Field [ Invoice Items Export::Export Link; $Export ID ]
      • Set Field [ Invoice Items Export::Card ID; $Alpha Code ]
      • Set Field [ Invoice Items Export::Description; INVOICEITEMS::Description ]
      • Set Field [ Invoice Items Export::Customer PO; $Order Number ]
      • Set Field [ Invoice Items Export::Date; Get(CurrentDate) ]
      • Set Field [ Invoice Items Export::Invoice No.; $Invoice Number ]
      • Set Field [ Invoice Items Export::Quantity; 1 ]
      • Go to Record/Request/Page [ Next; Exit after last ]
      • Go to Layout [ original layout ]
      • End Loop
      • #Add Discount Line
      • If [ INVOICE DETAILS::Trade percentage > 0 ]
      • New Record/Request
      • Set Field [ invoicedetails_INVOICEITEM::ID_Invoice_fk; $Original ID ]
      • Set Field [ invoicedetails_INVOICEITEM::VAT Rate; $VAT RATE ]
      • Set Field [ invoicedetails_INVOICEITEM::ID_Item_fk; "TD" ]
      • Set Field [ invoicedetails_INVOICEITEM::Description; "Discount at " & INVOICE DETAILS::Trade percentage & "%" ]
      • Set Field [ invoicedetails_INVOICEITEM::Discountable; "" ]
      • Set Field [ invoicedetails_INVOICEITEM::Price; (INVOICE DETAILS::Subtotal Discount)*-1 ]
      • Set Field [ invoicedetails_INVOICEITEM::Account No.; "42400" ]
      • End If
      • Perform Find [ Specified Find Requests: Find Records; Criteria: invoicedetails_INVOICEITEM::ID_Invoice_fk: “$Original ID” ] [ Restore ]
      • Commit Records/Requests
      • Go to Layout [ “Invoice” (INVOICE DETAILS) ]
      • Perform Find [ Specified Find Requests: Find Records; Criteria: INVOICE DETAILS::ID_Invoice_pk: “$Original ID” ] [ Restore ]
      • End If