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'}
Never tried this before for selection, but a substring on the Select Tab seems to work for Sales Orders that start with "J":
Now to remember the syntax for the date. It's some variation of <today> or something. But then to put it in a formula to subtract one...
Never tried this before for selection, but a substring on the Select Tab seems to work for Sales Orders that start with "J":
Now to remember the syntax for the date. It's some variation of <today> or something. But then to put it in a formula to subtract one...
Found this gem from my buddy Ryan. Works for Invoices dated today, but now how to modify it for invoices dated yesterday.
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. 
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.
