Lookups Setup
All lookup configuration is comprised of two components: the initial setup within the Setup tab, and the accompanying VBScript Lookup & Data Cleanse Functions that executes the lookup.
Setup > Lookups
The lookup Id's are configured under Lookups in the Setup Tab.
Lookup Id
The unique Id given to the lookup.
This Id is used to call in the Lookup function.
Description
A useful description of the Lookup.
Lookup From IMan DB
When selected, the lookup uses the internally maintained table as the lookup source.
When unselected, the lookup will query an external database.
Internal Table Name (Internal Lookup Only)
When the 'Lookup from IMan DB' box is selected, the available internal lookup tables (see Lookup Tables) will appear here.
Select Clause (External Lookup Only)
When the lookup is set to be external this field is the ‘select’ portion of the SQL query making up the lookup.
This tells the query what to retrieve, such as a Customer Id.
From Clause (External Lookup Only)
This field is the ‘from’ portion of the SQL query.
This tells the query which part of the database to search, such as the transaction type 'Orders'.
Where Clause (External Lookup Only)
For external DB lookups, this field forms the ‘where’ portion of the SQL query. The 'Where' clause tell the query what piece of data to use to find the relevant record, such as a customer's email address.
If there are to be multiple parameters, the where clause supports these in the form of %1, %2, %3 and so on. Where %1 corresponds to the first value in the list of parameters from the Lookup function, %2 corresponds to the second value, and so on.
Database Connect String (External Lookup Only)
When the lookup is set to be external this field is the connection string the lookup uses to connect and authenticate to the database.
To connect to an external database a ‘connection string’ must be provided. A connection string provides the details for the lookup to find and authenticate with the database.
The connection string must be either ODBC or OleDB format; (native .Net connection strings are not valid).
A good resource to construct connection strings is: http://www.connectionstrings.com/.
The database connection string can be parameterised with replacements %1, %2 and so on, for use with the Lookupdb Function and LookupWhere Function.
Use Safe
When checked the parameter values of the where clause will be passed as parameters. This prevents against SQL Injection attacks.
When unchecked the parameters values will be textual replacements in the where clause. This allows for complex type queries (such as IN clauses or dynamically generated where clauses) but opens the risk for SQL Injection.
We recommend enabling Safe lookups where possible especially in WebAPI scenarios of where there is a risk of IMan consuming malicious data from outside (unknown) sources.
Cache Results
When enabled the function saves (or caches) the result of the function so subsequent calls to the lookup are checked prior to querying the database. Caching improves performance typically on larger datasets where the lookup function is requesting the same data. On a set of requests where the deviation of the requests resembles a standard bell curve, performance is improved by 50-65%.