Using VI Import Perform Maintenance to Store Data between two Linked Jobs?

Question

I stumbled upon another post that described hard-coding or allowing a user to manually type in a deposit number when chaining two VI-Import jobs together for a Cash Receipts import. We are not wanting to go this route as in our case the deposit number should be generated the same way as clicking the next button when doing a manual entry. 
 I've successfully created the first job that generates the batch + deposit numbers (BatchNo is 'ASSN' with <blank> default, DepositNo is 'Next'). I have verified that each batch and deposit is created as intended in Cash Receipts Entry, as can be seen below: 
 
 There were a total of 10 test records that were read, and VI was able to bring it down to a single batch # and 2 deposit #s as intended (simulating ACH and Cash deposits) based on the imported "Deposit Type" column in the CSV file. 
 -------------------------- 
 Now the goal is to figure out how I can "export" (or store) these deposit numbers via a "Perform Command" so they can be picked back up in the chained job. Then to determine how from the 2nd job while also using "Perform" to import them. Needless to say, I don't know anything about these commands. 
 I was thinking that with the appropriate command, either an "After Assign", "After Write", or "On Completion" perform type could work to save/store the deposit number(s) to be then picked back up with a "After Read" or "Before Write" perform command. 
 
 Even if this is stored in a text file, it would be overwritten by this job every time it runs, so it can be a hard-coded filepath or a variable/TempField if it's able to persist between jobs. 
 But the import job would need to be able to get the saved/stored DepositNo and the PaymentType so it knows how to apply the number to the same payment types in the source file. 
 Does anyone here have any experience with these commands that can shed some insight? 
 -------------------------------- 
 Another possibility (assuming it's in fact possible) with perform would be for me to create a UDT, with the UDT keys being each of the payment types accepted by our customer web portal (currently ACH or CC); this would be a similar idea to what the GL_CompanyActiveBatch table does. Each payment type is deposited to the bank separately, which is why we are wanting to create 1-2 deposit #s per batch depending what was deposited that day. The idea here would be for job 1 to possibly use a perform command to update either the CC/ACH key value column in the UDT to which then the job 2 would be able to immediately grab. The values that would be stored in this table would be temporary - so they will be overwritten every day as needed. 
 In the end I am pretty flexible with the medium being used to store the payment-type deposit numbers between jobs (UDT / Server File / etc). I am just not too flexible on the desire to use Sage's auto-assign method for batch + deposit numbers as that is the method being used in manual cash receipt entries and would like to somewhat keep the integrity of that system.

SoonerFan21 · Answer

Update... I have, but it's not ideal. It involves creating a script and uploading to VI externally. I also used MS Access to aid in this process, but I am sure it can be used without but you're probably on your own if that's the case. For the external VI jobs, I created a shortcut and placed this in the paths: \\MAS90\Home\pvxwin32.exe ..\LAUNCHER\SOTAPGM.INI ..\SOA\STARTUP.M4P -ARG DIRECT UIOFF AUTO Steps the script takes Queries MS Access to determine how many current VI Imported jobs are in Cash Receipts (so we can later determine when the new one was added). I just count the number of records that are returned from the following query: SELECT BatchNo, PrivateBatch, Comment, RecordCount, BatchTotal01 AS BatchTotal, DateCreated FROM GL_CompanyActiveBatch WHERE Comment='VI Import: ' AND ModuleCode='A/R' AND BatchType='02' Externally uploads to VI Job #1, which creates the batch/deposit numbers Run the same query from #1, and wait for the number of records to be incremented by 1 to verify the job is complete I placed this in a finite loop to perform the query every couple of seconds and to quit once it reaches 20 attempts You can then run a similar query to get the MAX(BatchNo) or another way to determine which was the new batch, which then within that batch you can grab the DepositNo's Establish some logic to link the deposit numbers back to your data, perhaps based on the order of the deposit numbers and deposit totals and inject the batch/deposit numbers into the original CSV data somehow. What I did was have a linked file table in MS Access that linked to the original CSV file and performed a join on that data against the previous query, then exported as a new file Make sure to use a linked table and not an import so every time the query runs it's getting live data and not what was imported into Access Run the 2nd external VI job Like I said, it's not ideal but it gets the job done. I have this automatically run as a scheduled task at 8am every morning (we have a vendor that sends us payments they processed for us and drops it in our server via ftp). Note if you choose to use Access: if you're running this from a server the server itself won't need Office installed. You can simply install the Access Database Engine Redistributable from Microsoft's website. This will allow your script to communicate with Access via ADO/DAO. Access is what connects to Sage via ODBC and all the queries and data exports can be performed there. If you're fortunate enough that your csv file contains some type of UID for the individual payments (such as as confirmation or reference number) - MS Access can also help you compare those against CashReceiptsHistory to ensure they aren't duplicated. My script combines the last 30 days of payment files into one large file for MS Access to link to then excludes the ones that have already been processed by the confirmation numbers that are being stored in a UDF in Cash Receipts. 30 days is overkill but sometimes I prefer it that way. Another nice thing is that you can create sub queries in MS access to get deposit totals and export that into your csv file instead of trying to have VI calculate those totals. This in turn also helps you for the 2nd job to link your lines back to the deposit numbers. ----------------- Initially I had no intent to provide the actual script itself. First, it's probably more complicated and confusing than it is helpful. Second, this hasn't been validated or certified to not contain any security or other flaws. But if you know what you're doing and want to see the actual steps I am using as a reference point for you to create your own script then here it is. No warranties of any kind here, so don't use it at all or use at your own risk. And just to also note, the `CleanupLogFiles` method wasn't working as intended, which is why it's commented out. I can't remember the issue I was having with it and I haven't gotten back to fixing it yet so don't use it unless you can fix and test yourself to not wipe your server. Option Explicit '################################### SCRIPT SETTINGS ################################## 'Should a log file be generated during script run time? Const GenerateLogFile = True 'Number of historical logs to keep (excluding current log). If set to -1, there will be no automatic cleanup ' of log files. This occurs before the new log file is created, so even if set to 0 there will be only the ' most recently created log from this session. In other words, the number of logs after script has concluded ' = n+1 unless {GenerateLogFile} is set to False Const LogsToKeep = 100 'Number of attempts the loop will perform checking for a new batch from the first upload before aborting Const MaxAttempts = 20 'Time (ms) between each attempt. Remember that each attempt performs an ODBC query against Sage, ' so including a pause between requests is preferable ( 1500 = 1.5 seconds ) Const WaitTimeMS = 1250 'Directory that stores log files Const LogFileDir = "\filebox\Accounting\Billtrust\_VI\Logs\" 'Directory for all file exports (for VI and for Access DB) Const ExportDir = "\filebox\Accounting\Billtrust\_VI\Exports\" 'File name in the export directory that Excel will export the consolidated CSV as Const FileNameCSV = "ConsolidatedPaymentTable.csv" 'Full path to the Access Database file that performs the required queries and other file cleanup tasks before uploading to VI Const AccessDbPath = "\filebox\Accounting\Billtrust\Assets\DB Files\Consolidated Payments.accdb" '################################### GLOBAL CONSTANTS ################################# Const ForWriting = 2 Const ForAppending = 8 Const xlCSV = 6 '################################### GLOBAL VARIABLES ################################# Dim Log, Stopwatch, Accdb Dim bDepositFound, bProceed '###################################################################################### '###################################################################################### Sub InitializeObjects() Log.OpenedSubroutine "InitializeObjects" Set Stopwatch = New StopwatchCls Set Accdb = New AccessDBCls Log.ClosedSubroutine "InitializeObjects" End Sub Dim InitRecordCount, AttemptNo 'CleanupLogFiles InitializeLogFile InitializeObjects LogGlobals ExportConsolidatedPaymentTable 'Get the initial number of records from the deposit table before uploading the batch CSV Log.NewLineBuild "Querying Sage for current batch count of existing VI Jobs... " InitRecordCount = Accdb.RecordCount("OpenBatches") Log.AppendLine "Count = " & InitRecordCount 'Export the batch CSV from Access ExportBatchCSV 'If file is empty then no records were exported If FileSize(ExportDir & "BT-PMT-01.BATCH.csv") = 0 Then bProceed = False MsgBox "FileSize = 0" WScript.Quit Else bProceed = True End If 'Use the export from prior step to import into 'AR_CashReceiptsDeposit' If bProceed Then UploadBatchVI 'Wait until the new deposit record is showing in Sage Dim nAttemptNo, nCurrentCount Do nAttemptNo = nAttemptNo + 1 Log.NewLineBuild "Querying Sage for new batch creation... Attempt # " & nAttemptNo & " of " & MaxAttempts nCurrentCount = Accdb.RecordCount("OpenBatches") Log.BuildLine " .. Result = " & nCurrentCount & " (Original = " & InitRecordCount & ") .. " If nCurrentCount <> InitRecordCount Then bDepositFound = True Log.AppendLine "Import Successful!" Exit Do Else If nAttemptNo = MaxAttempts Then Log.AppendLine "Max Attempts Reached, aborting." bDepositFound = False Exit Do Else Log.AppendLine "Script Pause set to [" & WaitTimeMS & " ms] for recheck" End If End If WScript.Sleep WaitTimeMS Loop If bDepositFound Then ExportDepositCSV UploadDepositVI Else Log.WriteLine "Skipped Deposit export/import operations due to: Deposit not found" End If End If '################################################################################################################## '################################################################################################################## Function FileSize(sFilePath) Dim fso: Set fso = CreateObject("Scripting.FileSystemObject") Dim oFile: Set oFile = fso.GetFile(sFilePath) FileSize = oFile.Size Set oFile = Nothing Set fso = Nothing End Function Sub ExportBatchCSV Accdb.ExportTableAsCSV "ExportQuery:Batch", False, ExportDir & "BT-PMT-01.BATCH.csv" End Sub Sub ExportDepositCSV Accdb.ExportTableAsCSV "ExportQuery:Deposit", False, ExportDir & "BT-PMT-02.DEPOSIT.csv" End Sub Sub UploadBatchVI Const CmdPath = """\MV-SAGE02\Sage100\VI Jobs\VI CashReceipts Batch Import.cmd""" Log.WriteLine "Attempting remote Sage-VI Batch import job @ " & CmdPath Stopwatch.Start With CreateObject("WScript.Shell") Dim retVal retVal = .Run(CmdPath, 0, True) Log.WriteLine "~!WScript.Shell.Run -> Return Code = [" & retVal & "]" End With Log.NewLineBuild "~!VI Import job concluded (Batch)." Stopwatch.WriteElapsedTime End Sub Sub UploadDepositVI Const CmdPath = """\MV-SAGE02\Sage100\VI Jobs\VI CashReceipts Deposit Import.cmd""" Log.WriteLine "Attempting remote Sage-VI Deposit import job @ " & CmdPath Stopwatch.Start With CreateObject("WScript.Shell") Dim retVal retVal = .Run(CmdPath, 0, True) Log.WriteLine "~!WScript.Shell.Run -> Return Code = [" & retVal & "]" End With Log.NewLineBuild "~!VI Import job concluded (Deposit)." Stopwatch.WriteElapsedTime End Sub Sub LogGlobals With Log .WriteLine "Script Global Variables and constants:" .IndentLevel = .IndentLevel + 1 .WriteLine "ExportDir~!= " & ExportDir .WriteLine "AccessDbPath~!= " & AccessDbPath .IndentLevel = .IndentLevel - 1 End With End Sub Sub CleanupLogFiles If LogsToKeep < 0 Then Exit Sub Dim objFSO, objFolder, objFile Dim arrFiles() Dim i, j Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder(LogFileDir) ReDim arrFiles(objFolder.Files.Count - 1) ' Collect all files in the folder i = 0 For Each objFile In objFolder.Files If LCase(objFSO.GetExtensionName(objFile.Name)) <> "log" Then MsgBox "There are non-log files located in the log directory. Aborting the log directory cleanup routine." & vbNewLine & LogFileDir Exit Sub End If Set arrFiles(i) = objFile i = i + 1 Next ' Sort the array by creation date/time, descending For i = 0 To UBound(arrFiles) - 1 For j = i + 1 To UBound(arrFiles) If arrFiles(i).DateCreated < arrFiles(j).DateCreated Then ' Swap files Set objTemp = arrFiles(i) Set arrFiles(i) = arrFiles(j) Set arrFiles(j) = objTemp End If Next Next ' Delete all but the 5 most recent files For i = LogsToKeep To UBound(arrFiles) arrFiles(i).Delete Next ' Clean up Set objFSO = Nothing Set objFolder = Nothing End Sub Sub ExportConsolidatedPaymentTable Log.OpenedSubroutine "ExportConsolidatedPaymentTable" ' Source and destination folder/file paths Const strSourceFolder = "\filebox\Accounting\Billtrust\_InboundFileTransfers\PaymentFiles\" Dim objFSO, objFolder, objFile, objDestTextFile Dim strData, regEx ' Initialize FileSystemObject Log.WriteLine "Initializing objFSO" Set objFSO = CreateObject("Scripting.FileSystemObject") ' Check if source folder exists If Not objFSO.FolderExists(strSourceFolder) Then Log.WriteLine "Source folder not found." WScript.Quit End If ' Delete the existing destination file if it exists Log.NewLineBuild "Checking for existing destination file... " If objFSO.FileExists(ExportDir & FileNameCSV) Then Log.AppendLine "Located old destination file." objFSO.DeleteFile(ExportDir & FileNameCSV) If Not objFSO.FileExists(ExportDir & FileNameCSV) Then Log.WriteLine "File Deleted" Else Log.AppendLine "Old destination file does not exist." End If ' Create the destination file Log.WriteLine "Creating new destination file." Set objDestTextFile = objFSO.CreateTextFile(ExportDir & FileNameCSV, True) ' Write headers to the destination file Dim headers headers = "DepositType,FileDate,DepositAmt,ConfirmationNo,PaymentDate,CustomerNo," & _ "CustomerName,TotalPaid,CardType,Last4,Comments,EmailFrom,EmailSubj," & _ "EmailDate,OriginSys,CreateUser,InvoiceNo,InvoiceAmt,InvoicePaid," & _ "DiscountAmt,ShortPayReason,InvoiceLevelComments,SourceName" objDestTextFile.WriteLine headers ' Regular expression for file matching Const sRegexPattern = "^(?:BT_)?MVPAY_(?:BPNCARD|CC|ACH)_[\d_]{15,}\.csv$" Log.WriteLine "Setting filename regex pattern to: /" & sRegexPattern & "/i" Set regEx = New RegExp regEx.Pattern = sRegexPattern regEx.IgnoreCase = True ' Read files from source folder Set objFolder = objFSO.GetFolder(strSourceFolder) ' Loop through each file in the source folder Log.WriteLine "Looping folder for file merge." For Each objFile In objFolder.Files Log.NewLineBuild "Checking File Pattern: " & objFile.Name & ".." & vbTab If regEx.Test(objFile.Name) Then Log.AppendLine "File name passed pattern test. Reading file." ' Open the current source file for reading Dim objSourceTextFile Set objSourceTextFile = objFSO.OpenTextFile(objFile.Path, 1, False) ' Skip the header of the source file If Not objSourceTextFile.AtEndOfStream Then objSourceTextFile.ReadLine ' Get the file name without extension Dim fileNameWithoutExtension fileNameWithoutExtension = Left(objFile.Name, InStrRev(objFile.Name, ".") - 1) ' Read the rest of the file and write to the destination file Do Until objSourceTextFile.AtEndOfStream strData = objSourceTextFile.ReadLine ' Append the file name without extension to the end of the line objDestTextFile.WriteLine strData & "," & fileNameWithoutExtension Loop objSourceTextFile.Close Log.WriteLine "~!File Closed." Else Log.AppendLine "Failed Regex Pattern test" End If Next ' Close the destination file after writing all data objDestTextFile.Close Set objFolder = Nothing Set objFSO = Nothing Set regEx = Nothing Log.ClosedSubroutine "ExportConsolidatedPaymentTable" End Sub Sub InitializeLogFile If Not GenerateLogFile Then Exit Sub Set Log = New LogFile End Sub Function ComputerName ComputerName = CreateObject("WScript.Network").ComputerName End Function Function UserName UserName = CreateObject("WScript.Network").UserName End Function Sub Kill(sFilePath) Dim FSO: Set FSO = CreateObject("Scripting.FileSystemObject") FSO.DeleteFile sFilePath, True Set FSO = Nothing End Sub Class LogFile Private oFile 'As Scripting.File Private sFileName, sFilePath Private nIndentLevel, t Private Sub Class_Initialize() t = Timer Dim FSO: Set FSO = CreateObject("Scripting.FileSystemObject") sFileName = NewFileName sFilePath = LogFileDir & sFileName Set oFile = FSO.OpenTextFile(sFilePath, ForAppending, True) Set FSO = Nothing InitialLogEntries End Sub Private Sub InitialLogEntries WriteLine "Log file initialized." WriteLine "~!Directory~!= " & LogFileDir WriteLine "~!LogFile~!~!= " & sFileName WriteLine "~!ComputerName~!= " & ComputerName WriteLine "~!UserName~!= " & UserName End Sub Private Function NewFileName Dim timestamp, formattedDate, formattedTime timestamp = Now formattedDate = Year(timestamp) & Right("0" & Month(timestamp), 2) & Right("0" & Day(timestamp), 2) formattedTime = Right("0" & Hour(timestamp), 2) & Right("0" & Minute(timestamp), 2) & Right("0" & Second(timestamp), 2) timestamp = formattedDate & "-" & formattedTime NewFileName = "VI-Payments_" & timestamp & "_" & ComputerName & ".log" End Function Public Property Let IndentLevel(nValue) nIndentLevel = nValue End Property Public Property Get IndentLevel IndentLevel = nIndentLevel End Property Public Sub OpenedSubroutine(sName) WriteLine "Called Sub: " & sName IndentLevel = IndentLevel + 1 End Sub Public Sub ClosedSubroutine(sName) IndentLevel = IndentLevel - 1 WriteLine "Sub Terminated: " & sname End Sub Public Sub NewLineBuild(t) 'For new lines that should include the time stamp but not close itself oFile.Write Now & vbTab & String(nIndentLevel, vbTab) & Replace(t, "~!", vbTab) End Sub Public Sub WriteLine(t) 'For new lines that is independent of any other lines (TimeStamp + NewLine) oFile.WriteLine Now & vbTab & String(nIndentLevel, vbTab) & Replace(t, "~!", vbTab) End Sub Public Sub BuildLine(t) 'For existing lines that you don't want to terminate (no timestamp, no line termination) oFile.Write Replace(t, "~!", vbTab) End Sub Public Sub AppendLine(t) 'For existing lines that builds from a prior 'NewLine' (doesn't include timestamp but does include line termination) oFile.WriteLine Replace(t, "~!", vbTab) End Sub Private Sub Class_Terminate() WriteLine "Log File Class_Terminate() Event called..." WriteLine "Total elapsed script time = " & FormatNumber(Timer - t, 2) & " seconds" oFile.Close Set oFile = Nothing End Sub End Class Class StopwatchCls Private nTimer Public Sub Start() nTimer = Timer End Sub Public Function GetElapsedTime() GetElapsedTime = vbTab & "[ " & FormatNumber(Timer - nTimer, 2) & " Seconds ]" End Function Public Sub WriteElapsedTime Log.AppendLine GetElapsedTime End Sub Private Sub Class_Terminate() Log.WriteLine "StopwatchCls Class_Terminate() Event called..." Log.WriteLine "Class Terminated." End Sub End Class Class AccessDBCls Private conn Private Sub Class_Initialize() Stopwatch.Start Dim connStr: connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessDbPath Log.WriteLine "Initializing AccessDBCls - (Access Database Manager)" Log.IndentLevel = Log.IndentLevel + 1 Log.WriteLine "Connection String = [" & connStr & "]" Set conn = CreateObject("ADODB.Connection") Log.NewLineBuild "Opening Database Connection." conn.Open connStr Stopwatch.WriteElapsedTime Log.IndentLevel = Log.IndentLevel - 1 End Sub Public Sub ExportTableAsCSV(sTableName, bIncludeHeaders, sPath) Log.WriteLine "Exporting Access Database Table to CSV." Log.IndentLevel = Log.IndentLevel + 1 Log.WriteLine "Table Name~!= " & sTableName Log.WriteLine "Include Headers~!= " & bIncludeHeaders Log.WriteLine "Export Path~!= " & sPath Dim rs, sql, fso, outputFile, field, isFirstField, dataLine Set fso = CreateObject("Scripting.FileSystemObject") Set outputFile = fso.CreateTextFile(sPath, True) Stopwatch.Start Log.NewLineBuild "Running database query." & vbTab sql = "SELECT * FROM [" & sTableName & "]" Log.AppendLine "SQL = '" & sql & "'" Log.NewLineBuild "Executing SQL Statement.. " Set rs = conn.Execute(sql) Stopwatch.WriteElapsedTime ' Write headers if required Stopwatch.Start If bIncludeHeaders Then Log.NewLineBuild "Writing Headers..." isFirstField = True For Each field In rs.Fields If Not isFirstField Then outputFile.Write "," End If outputFile.Write """" & field.Name & """" isFirstField = False Next outputFile.WriteLine Log.AppendLine "Done" End If ' Write data Log.NewLineBuild "Writing table data... " Do Until rs.EOF isFirstField = True For Each field In rs.Fields If Not isFirstField Then outputFile.Write "," End If outputFile.Write """" & field.Value & """" isFirstField = False Next outputFile.WriteLine rs.MoveNext Loop Log.BuildLine "Done." Stopwatch.WriteElapsedTime Log.NewLineBuild "Cleaning up. Closing Recordset" ' Clean up rs.Close Log.BuildLine "...Recordset Closed; Closing OutputFile" outputFile.Close Log.BuildLine " ...OutputFile Closed." Set rs = Nothing Set outputFile = Nothing Set fso = Nothing Log.AppendLine " Object references destroyed successfully." Log.IndentLevel = Log.IndentLevel - 1 End Sub Public Function RecordCount(sTblName) Dim rs 'As ADODB.RecordSet Set rs = conn.Execute("SELECT COUNT(*) As RecordCount From [" & sTblName &"]") RecordCount = rs(0) rs.Close Set rs = Nothing End Function Private Sub Class_Terminate() Log.WriteLine "AccessDbCls Class_Terminate() Event called... " Log.NewLineBuild "Access DB Connection Closed = " If Not (conn Is Nothing) Then If conn.State = 1 Then Log.AppendLine "False. Attempting to close." conn.Close Else Log.AppendLine "True" End If Set conn = Nothing Else Log.AppendLine "True" End If Log.WriteLine "Class Terminated." End Sub End Class Log.WriteLine "Terminating objects for script completion." On Error Resume Next Set Accdb = Nothing Set Stopwatch = Nothing Set Log = Nothing I won't be providing the queries right now but I can attempt to help answer questions you might have about them.

Sage 100

Using VI Import Perform Maintenance to Store Data between two Linked Jobs?

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING