You are here: Chapter 7: Configuration and Administration > Workspace Administration > Fields > Configure Dynamic SQL Field Mapper

Configure Dynamic SQL Field Mapper

The following instructions are for configuring this feature for both Windows and UNIX/Linux FootPrints versions.

  1. A link to the external database must first be enabled before the feature can be configured within FootPrints:
  2. 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.
  3. 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.
  1. 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.

  1. 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.
  2. Click Add Link.  The Configure Lookup in External Database window is displayed with the configuration dialog:
  3. Data Source Name—Enter the name of the system DSN (Windows) or external server name (UNIX) you configured in Step 1.
  4. Database Type—Select the database type to which you connect.
  5. Server Username/Server Password—Enter the username and password for a system user that has access to the database to which you are connecting.
  1. When you have completed the dialogue, click Next to continue.  The Configure Lookup in External Database window is displayed.
  2. Enter the information requested.
  3. Select a Table or a View—Select the name of the database table that contains the lookup field.
  1. Click Next to continue.  The Configure Lookup i External Database window is displayed.
  2. 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.
  3. 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".
  4. Workspace Fields—Match the FootPrints Workspace fields to the fields in the external database.
  5. Address Book Fields—Match the FootPrints Address Book fields to the fields in the external database.
  6. 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

  1. 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.
  2. Complete the additional options as needed:
  3. 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.
  4. If multiple matches are found, append data in multi-line fields—Populate multiple results in one multi-line field if this checkbox is checked.
  5. If this lookup populates another Lookup Key, then trigger that lookup—If checked,  can initiate more than one lookup with a single click.
  6. 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.
  7. 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.
  8. 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

  1. Click Finish to complete the configuration.
  2. 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.
  3. 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