The following instructions are for configuring this feature
for both Windows and UNIX/Linux
FootPrints
versions.
A link to the external
database must first be enabled before the feature can be configured within
FootPrints:
Windows
versions—A
system Data Source Name (DSN) must first be created on the FootPrints
web server that can make a connection to the database that is being linked.
This can be done using the option under the Windows Start
menu for Administrative
Tools | Data Sources (ODBC). If
an Open Database Connectivity (ODBC) driver is not listed for the database,
you must obtain the driver from the manufacturer.
UNIX
versions—This
feature uses the database driver (DBD) for the Perl Database Interface
(DBI) to connect to external databases. If
the database you connect to is of the same type as your FootPrints database, nothing additional must be done. If
you want to connect to a different database type (for example, FootPrints
data is kept in MySQL, but you want to connect to an Oracle database),
a DBD for the external database must be built.
To configure the
feature within FootPrints, select
Administration
| Workspace | Dynamic SQL Field Mapper from the FootPrints
Toolbar. Available Workspace and Address Book fields are
listed in the Available
Fields dialog box. Only
integer, drop-down, email, or character (single-line) fields can be used
as lookup keys when querying the database.
Note
This feature is part of the Dynamic SQL Database Link add-on
module and is not available unless it is licensed.
Highlight the field
to be used as the lookup key. The
data from this field is used to search the external database. It
must be a unique data type in the external database, such as "User
ID", "Asset name", "IP Address", etc.
Click Add Link. The
Configure
Lookup in External Database window is displayed with the configuration
dialog:
Data
Source Name—Enter
the name of the system DSN (Windows) or external server name (UNIX) you
configured in Step 1.
Database
Type—Select the database type to which you connect.
Server
Username/Server Password—Enter
the username and password for a system user that has access to the database
to which you are connecting.
When you have completed
the dialogue, click Next
to continue. The
Configure
Lookup in External Database window is displayed.
Enter the information
requested.
Select
a Table or a View—Select
the name of the database table that contains the lookup field.
Click Next to continue.
The Configure
Lookup i External Database window is displayed.
Address
Book and Issue
Information fields are listed that can be populated from the external
table (checkbox fields are not available). For
each field that you want populated, enter the name of the corresponding
column in the external table. Only fill in those fields that are
to be mapped to columns in the external table.
Lookup
Field—Enter
the column name in the database table that matches the lookup key field
in FootPrints. For
example, if the lookup key field in FootPrints
is "UserID", the primary key in the external table might be
"UID".
Workspace
Fields—Match
the FootPrints Workspace fields
to the fields in the external database.
Address
Book Fields—Match
the FootPrints Address Book fields
to the fields in the external database.
Optional Delimiter—Optionally enter a delimiter character in this field. This option is displayed for all FootPrints multi-line text fields. The database column you are connecting to might contain data separated by a delimiter. For example, one of the database columns may contain a list of software on a particular computer that is separated with colons, such as “MS Word:MS Excel:Outlook:Acrobat Reader”. If this option is not used, that data will be populated in the field in that exact format, with the colons displayed. If the option is used with a colon character, the data will be populated in the field with each item on its own line and colons removed, as follows:
MS Word
MS Excel
Outlook
Acrobat Reader
Click Next
to continue. The
Configure
Lookup in External Database window is displayed.
The window
shows a summary of the configuration options you have selected as well
as some additional options.
Complete the additional
options as needed:
Automatically trigger this lookup when submitting a ticket—This checkbox applies only to when customers submit Issues. If checked, this lookup is performed automatically when the issue is submitted based on the completed lookup field data only from the GUI. That is, when the SAVE button is clicked, FootPrints checks for lookup fields and, if a lookup field is populated, it populates the rest of the data that it has been configured to complete from the SQL database. If there is no data for a field, it leaves the field blank. If a field is mandatory and there is no data for that field in the SQL database, FootPrints pops up the mandatory field error window to inform the user that the incident report cannot be submitted without completing that field. If multiple lookup fields have been configured, FootPrints populates the data in the order that the fields are set on the administration page; this means that fields may be overwritten with data discovered in subsequent lookups during the same submission.
If
multiple matches are found, append data in multi-line fields—Populate
multiple results in one multi-line field if this checkbox is checked.
If
this lookup populates another Lookup Key, then trigger that lookup—If
checked, can
initiate more than one lookup with a single click.
If
a Select Contact action populates this Lookup Key, then trigger this lookup—If
checked, initiates an external database lookup from the internal database
lookup.
Primary Sort Field—If multiple entries are found for the same lookup, set the field and order of sorting for the multiple entries. For example, if the lookup key is Last Name and this field is set to sort by last name, then the entries are displayed in alphabetical order based on last name.
Secondary Sort Field—If multiple entries are found for the same lookup, set a secondary field and order of sorting for the multiple entries. For example, if the Primary Sort Field is Last Name and this field is set to First Name, then the entries are displayed in alphabetical order based on last name and then based on first name. To continue the example, if the lookup finds four entries for "Malo", the order in which they are displayed might be:
Malone, David
Malone, Shirley
Maloney, Bob
Maloney, Karen
Click Finish
to complete the configuration.
To map another field,
repeat the process above. There is no limit to the number of lookup key
fields you can configure, and different key fields can point to different
tables or databases. In addition, a field mapped from one database table
can become the lookup key field to another table (i.e., a cascading lookup),
but a lookup must be done individually for each key field.
When you are finished,
click Save on the Dynamic
SQL Field Mapper page.
Matching Rules
To stop "1" from matching "10", "11",
"12", etc. in the dynamic field mapper setup, make
the footprints field an "integer" field, because the SQL query is different for
integer fields.
Limitations of the Dynamic SQL Field
Mapper
The lookup key field
should be a unique value in the external database.
Only integer, drop-down,
email, or character (single-line) fields can be used as lookup keys when
querying the database.
Checkbox fields cannot
be auto-populated from the external database.
The Dynamic SQL Field
Mapper feature must be set up per Workspace, but you can have multiple lookup
keys mapped to different sets of fields within a Workspace.
Column names cannot
have spaces or start with numbers.
Does not work via email. Only works from the FootPrints interface.