Have you ever wanted to ask a user to enter values when they integrate data in SmartConnect? This week’s Tech Tuesday is from our Director of Professional Services, Lorren Zemke, who will lead you through an example of doing just that!
Overview
While most data integrations are executed on a scheduled basis or the data needed can be calculated or pulled directly from some source, there are times when you may want to ask a user for some values when they execute the process. This can be done in SmartConnect with a simple task and some global variables.
There are two requirements needed to be able to use this method. The main requirement is that the map will be executed manually by a user inside of the SmartConnect User Interface. The second requirement is that you must be using an ODBC Datasource so we can include the global variable as part of the query. The last requirement is that you are using VB.NET for your scripting language as C# does not have the input box method available.
For my example we will be asking the user to provide a document date and a batch number when executing our map to create Accounts Payable transactions.
Global Variables
To create the global variables, we will go to the SmartConnect Maintenance menu and click Global Variables. Enter the Global Variable name and click Add. After you have added your global variables be sure to click the Save button.
We will add GBL_BATCH and GBL_DOCUMENTDATE variables.
Create the Accounts Payable map
We will use an Excel workbook as our data source to create Accounts Payable transactions but we will use the ODBC Connection Data Source type so we can create a SQL statement for our data. Later we will modify the query to select specific data rows based on user input.
Create Script Task to Ask For User Input
Create a pre-map script task to ask the user for the values and store them into the global variables. Right-click on the Tasks that run before the map and choose New Task->Run Script.
We will utilize VB.NET code for this purpose by using the Microsoft.VisualBasic.Interaction.InputBox method to pop a dialog box where the user can enter the value requested.
The script will ask the user for the Document Date that we use in our source query. First, we loop until the user has entered a valid date in the format required. A RegEx Expression is used to validate the user entered the date in the necessary format. Second, we ask the user for a batch number and loop until one is entered. Finally we store the input into global variables.
Remember all task scripts in SmartConnect must return true to be successful otherwise return false to stop processing.
VB.NET Code
‘—————————————————————————————
‘ Requirements: This map should be located in the Tasks -> Map -> Before location so
‘ that the dates entered can be used later on. This isn’t required
‘ however, and this script can be run anywhere it is needed.
‘—————————————————————————————
‘—————————————————————————————
‘ Setup: The only variable that may need to be set is the regular expression
‘ string. By default, it accepts a fairly standard input date, but it
‘ can be edited if a change is needed.
‘
‘ Update: If the map that the script is attached to is going to be
‘ scheduled or run real-time then the mapInterface variable should
‘ be set to 0.
‘ 0 = scheduled map / real-time map
‘ 1 = manually run map
‘—————————————————————————————
‘———————————– CONFIGURATION ————————————-
Dim regExpression As String = “^(0[1-9]|1[0-2])/(0[1-9]|1d|2d|3[01])/(19|20)d{2}$”
‘First we allow 01,02, etc. OR 1,2,etc. OR 10,11,12 as month entries
‘The separators we use can be / to separate the month, day and year
‘Next we allow 01,02, etc. OR 1,2, etc. OR 10,11,…,22,23 etc. OR 30,31 as day entries
‘Another separator
‘Finally we allow 19** OR 20** where the * represents a digit
Dim mapInterface As Integer = 1
‘—————————————————————————————
Try
Dim docDate As String
Dim batchNumber as String
Dim dDate As Date
Dim match As System.Text.RegularExpressions.Match
‘Define the regular expression to check dates
Dim dateRegex As New System.Text.RegularExpressions.Regex(regExpression)
‘Prompt for the document date
docDate = Microsoft.VisualBasic.Interaction.InputBox(“Enter the document date (mm/dd/yyyy):”, “Document Date”)
‘Match the input against the regex
match = dateRegex.Match(docDate)
‘While it doesn’t match the regex or a valid date continue to prompt for it
Do While (Not match.Success Or Not Microsoft.VisualBasic.IsDate(docDate))
‘Alert the user of the problem
Microsoft.VisualBasic.MsgBox(“Please enter a valid date in the format of mm/dd/yyyy.”)
‘ Prompt for the document Date
docDate = Microsoft.VisualBasic.Interaction.InputBox(“Enter the document date (mm/dd/yyyy):”, “Document Date”)
‘Match the input against the regex
match = dateRegex.Match(docDate)
Loop
‘Prompt for the Batch Number
batchNumber = Microsoft.VisualBasic.Interaction.InputBox(“Enter the Batch Number:”, “Batch Number”)
‘While it doesn’t match the regex or a valid date continue to prompt for it
Do While (string.IsNullOrEmpty(batchNumber))
‘Alert the user of the problem
Microsoft.VisualBasic.MsgBox(“Please enter a valid Batch Number”)
‘Prompt for the ending date
batchNumber = Microsoft.VisualBasic.Interaction.InputBox(“Enter the Batch Number:”, “Batch Number”)
Loop
‘Store the input dates in Date datatypes
dDate = CDate(docDate)
‘ Set the value into the global variables
GBL_DOCUMENTDATE = dDate
GBL_BATCH = batchNumber
Catch ex As Exception
If (mapInterface = 1) Then
Messagebox.Show(ex.Message, “DateInputTemplate Script Error”)
End If
Return False
End Try
Return True
Create a Calculated Field to Use the Global Variables
To map the Batch Number global variable, we set up a calculation column and map that new column to the destination columns in our map.
We create the calculation column called BATCHNUMBER_CALC.
Map the new calculation column to the Batch number.
Modify Your Query
Add the global variable to your ODBC Query to restrict what data is actually sent to the destination. When the map executes the user will be prompted to enter the Batch ID and then the Document Date. The Document Date entry will require the user to enter the date in a valid format of Month/Day/Year before continuing.
We will modify our query to select only those transactions in our source file with the date we just entered. We place single quotes around the global variable since SmartConnect replaces that global variable with the value it contains and SQL statements require the single quotes around a date value.
As a warning, with this setting you will receive an error when opening the map about an invalid data source. This is normal and will be changed in our 2014 SmartConnect release.
Execute
