IM - How to filter source query records dynamically

Ever wanted to give users the ability to limit source records dynamically? How about being able to filter records from in a source query by a date range? Well, I encoutered this situation working on my current project.

Out of the box, Integration Manager offers the ability to retrieve records and set static filters to source records. However, in many cases users may want to dynamically (at runtime) establish a date range or any other range parameter for the records, then have these ranges applied to the source query.

This is possible by using some old fashioned VBScript with Integration Manager. Consider the following records:

DOCUMENT_DATE JOURNAL_NUMBER ACCOUNT_NUMBER DEBIT_AMOUNT CREDIT_AMOUNT
12/01/2009 100  000-1200-00 100.00  0.00
12/01/2009 100  000-6620-00 0.00  100.00
12/15/2009 200  000-1201-00 22.50  0.00
12/15/2009 200  000-6630-00 0.00  22.50
01/06/2010 300  000-1200-00 120.00  0.00
01/06/2010 300  000-6620-00 0.00  120.00
01/20/2010 400  000-1201-00 52.50  0.00
01/20/2010 400  000-6630-00 0.00  52.50

In order to make our integration interactive, we must first prompt the user to enter the date restriction in the format required to filter the data:

Before Integration

Dim startDate, endDate

Do
  startDate = InputBox("Enter the start date for your transactions (mm/dd/yyyy).")
  If Not IsDate(startDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(startDate)

Do
  endDate = InputBox("Enter the end date for your transactions (mm/dd/yyyy).")
  If Not IsDate(endDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(endDate)

If CDate(startDate) > CDate(endDate) Then
 MsgBox("The start date must be greater than the end date. Integration will end now.")
 CancelIntegration
End If

' Store the user input in global variables that can be retrieved later on
SetVariable "gblStartDate", startDate
SetVariable "gblEndDate", endDate

Now we can apply the user's input as restrictions to our source query data by invoking the AdditionalCriteria property of the Query object.

Before Query

Query.AdditionalCriteria = "DOCUMENT_DATE >= '" & CDate(GetVariable("gblStartDate")) & "' AND DOCUMENT_DATE <= '" & CDate(GetVariable("gblEndDate")) & "'" 

Note that the Query.AdditionalCriteria will only work on source queries that use a Text or Simple ODBC DSN. The AdditionalCriteria property will not work on Advanced ODBC queries.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/