I'm running SAGE 100 v2018 and am trying to create a VI Export job based on historical PO Receipt records within 60 days of the Receipt Date. I can add a hard-coded date range for the Selection option but I was hoping to find a way to have a specific number of days rather than using an actual date.
If you can write VBScript and use ADO to query the data you need, you can schedule the VBScript file to be ran using Windows Task Scheduler.
If you want to leverage Crystal Reports to handle the querying…
Set up the file to contain the perform logic and add the following to it and then point the VI Export job to it on the "On Execution" perform type.
cSelectStatement$="IF PO_ReceiptHistoryHeader01…
David, the above code placed in a .pl and used as the "On Execution" perform process worked. Thank you!
First, as BigLouie as said, ODBC is usually the best way to do this, whether through a Crystal Report, Excel, Access, etc but if you must use VI Export, then you may consider the following.
You can't do this easily using the Export Job Maintenance as is but you can leverage the perform logic option to either dynamically set the select statement using the "On Execution" event or compare each record at the record level using the "Before Write" event.
If you use the "On Execution" event, you need to overwrite the "cSelectStatement$" variable with a proper statement that can be evaluated against each record and will in turn set the "selected" variable to either a 1 or a 0 which determines whether or not it is export.
If you use the "Before Write" event, you need to evaluate the record yourself and then set the "selected" variable to a 1 if you want it exported or a 0 if you don't want it exported.
Take the following VI job for example.
With that configuration on the "Select" tab, it produces the following select statement.
cSelectStatement$="IF UCS(CI_Item01.ItemCode$)=""BOARD-04220-66"" { selected = isTRUE } ELSE { selected = isFALSE }"
If you don't have anything configured on the "Select" tab, it produces the following select statement.
cSelectStatement$="selected = isTRUE"
To set up the perform logic, you click the Perform button on the Configuration tab, select your event, and then provide a relative path to a text file containing your perform logic. In both examples below, I'm using the same file name but specifying an entry label. You would omit the entry label by only having the path to the file. I'm using .pl as the file's extension but you can use .txt.
..\CM\Script\TestUDS_Procedures.pl;Change_Export_Criteria
..\CM\Script\TestUDS_Procedures.pl;Select_Record
You only need to use one or the other, not both, if you use "On Execution", you need to escape your double quotes unlike the "Before Write" where you would just use straight ProvideX to specify an IF statement.
Here is the contents of the file referenced above. Both will make it so items containing "BOARD" in the item code will be selected.
Change_Export_Criteria:
cSelectStatement$="IF POS(UCS(""BOARD"")=UCS(CI_Item01.ItemCode$)) <> 0 { selected = isTRUE } ELSE { selected = isFALSE }"
EXIT
!
Select_Record:
IF POS(UCS("BOARD")=UCS(CI_Item01.ItemCode$)) <> 0 { selected = isTRUE } ELSE { selected = isFALSE }
EXIT
Now, you did say you want to select by date so since Sage 100 stores dates as a string in the YYYYMMDD format, you need to make sure you are selecting them in that manner as well. You also need to make sure your field name is correct. Take note of the number that appears in parentheses after the table name on the Data tab. You need to add that exact number to the field name's variable referenced by your selection criteria. You can see this in the above code how the ItemCode field is referenced by CI_Item01.ItemCode$.
I don't exactly understand your selection criteria so I can't give you any guidance at the moment on how exactly to set the selection criteria.
ODBC is absolutely better than VI for exports. I'll write VBScripts if I have to.
But... have you tried a runtime prompt on tab 3?
