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

Question

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'}

rclowe · Accepted Answer

Found this gem from my buddy Ryan . Works for Invoices dated today, but now how to modify it for invoices dated yesterday.

dDub · 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. 
 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.

Sage 100

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

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING