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/

Advertisements

One Response to IM – How to filter source query records dynamically

  1. Vic says:

    Hi

    If i had a sales department that constantly want to change the generic description on items, class id’s, Sales person on accounts territories basically anything to do with tracking sales (Oh Wait I DO HAVE ONE OF THOSE!@!!!) I have done these back end updates telling then that the history for sales data will not change as I am doing then VERY CAREFULLY through sql. Now they want the history to follow these changes. If i told them that if we bought integration mamager that would be possible would I be lying??

    In a nutshell. does integration mamger update history ?

    thanks
    Vic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: