Adding a user-entered parameter to a SmartConnect Map

This article will explain how to add one user-entered parameter to a SQL statement for SmartConnect Map for use with Dynamics GP.  This assumes a valid map already exists.  You may want to make a copy of the map so that you can always have the original available as either a backup or a 2^nd version.

• Select the map
• Create a global variable to hold the prompted value
  • Click the Maintenance link on the main menu bar
  • Click Global Variables on the Generic Connector ribbon
  • Enter the variable name (GBL_ is already supplied)
  • Enter a Default Value (if desired)
  • Click the Add button
  • Click Save
  • Close the Manage User Global Variables window

• Create a pre-map task to prompt for the value:
  • Select the map
  • Click on the Tasks tab
  • Select Map Pre Tasks -> Tasks that run before the map
  • Click the Add New Task button
  • Select Run Script
  • Enter:
    • Name
    • On Success should be “Go to next step”
    • On Failure should be “Quit”
    • Enter the code
      Example:

      Try

          Dim s As String = Microsoft.VisualBasic.Interaction.InputBox("Enter Check Number", "Check Number Select")

           GBL_CHECKNUM = String.Format("'{0}'", s)

           return true

      catch

           return false

    end try

    • Click Validate to check it
    • Click Save to save the code
  • Click on the Global Variables tab and verify your global variable is listed
  • Make sure you Save EVERTHING!
  • If the global variable is being used in the WHERE clause statement and contains a string value then the quotes would need to be put around the value that is assigned in the pre-map task.

   

  

   

• Edit the map to use the global variable
  • Add a line similar to:
    • SELECT * FROM PA30101 WHERE EMPLOYID = GBL_PROMPTFORVALUE
    • Substitute the field name and global variable name for what you need
• Run the task
  • Click on the Validate button to make sure the syntax is correct.
  • The Preview button will run the map and display results
  • Note that if you are inserting a prompted value via a task, the validation and preview may fail.Running the map may be the only way to verify correct operation.
  • Click the Destination bar to configure the output format and location
    • Select Destination Type, File Type, Path
    • Select options: Include Headings, Overwrite File, and Append Date/Time
    • Click the double up-arrow to return to main window
  • Click the Run button on the Main Menu Ribbon to run the map
    • Output will be written to location determined in the Destination window