Export Invoices From Prior Day and Where Sales Order Begins With J

SOLVED

I need to export invoices that are from yesterday's date and the related Sales Order begins with a J.

I am a novice at using Sage Intelligence, but I have some SQL experience. I understand that it's not actually SQL.

I'm able to get the below SQL to work with a hard coded date.

SELECT AR_InvoiceHistoryHeader.CustomerPONo WHERE AR_InvoiceHistoryHeader.CustomerNo='INSERT CUSTOMER NAME HERE' AND AR_InvoiceHistoryHeader.InvoiceDate={d'2023-01-18'}

Parents Reply
  • +1 in reply to rclowe
    verified answer

    Ok, I got it to work!....SORT OF

    I first used a temp field to read in the date and convert from a string to a number while subtracting 1 day. Then assigned the temp field to the Transaction Data field to regain the date mask.

    Then I used your suggestion in the selection field to select today's date. So it selects records with today's date, reads it in, subtracts the day, and then outputs the date minus a day!

    Now here's the funny part, I don't really need it. I decided to pull from "Transaction Date" instead of "Invoice Date". This way I always pull from what was closed out (From the AR_InvoiceHistoryDetail table) and posted on that day. If I run that daily, problem solved.


    I thought I'd still put the answer up here just in case another user needs this. Slight smile

    There is probably an easier way, but I'm no expert at VI and I got this work great for me.

    TO BE CLEAR, THIS ONLY EXPORTS TODAY'S DATE AND SUBTRACTS A DAY FROM IT.

    Our billing runs a day behind, and needed the backdate to the previous day to upload invoices to the customer's database. Odd requirement, but it's what was needed.

Children
  • 0 in reply to dDub

    Glad you got it working! 

    When it comes to exporting data out of Sage 100, VI is not anywhere near the top of my list of ways to get that done.  Excel (including SI), custom report, external reporting tool (like Visual Cut / SA&WF...), BOI script, VBScript (ODBC)... there are much easier ways.