Taking FootPrints' Dynamic SQL Field Mapper Further

The Dynamic SQL Field Mapper (DSFM) feature of FootPrints is a powerful tool for integrating FootPrints with other systems. By understanding the available options for the feature, both documented and not, you can expand its suitability for use cases even further. Described in this post is a technique for ensuring that a valid User ID is entered into a ticket so that a Change Management process can assign that user as an approver.

Setup

First, let’s review what is required for connecting this feature to an external database. DSFM will pull data from another database into FootPrints tickets over a read-only channel. On a Windows server, this channel is referred to as an ODBC Data Source Name, or DSN. On the server, open the Administrative Tools > Data Sources (ODBC) menu and add a DSN under the System DSN tab. FootPrints will be using an explicit database user account to connect over this DSN, so make sure the option to connect with database-level authentication (e.g. SQL Server authentication) is chosen rather than the option to connect with integrated Windows authentication. Next, make sure a suitable user account exists in the database you are connecting to:

• At a minimum, the account will need permission to list the tables and views defined in the database and to read data from these tables and views. In SQL Server, the db_datareader role is sufficient. DFSM will never try to write to the database it connects to, but it is always a good idea to limit an account’s access to only what is required.
• The account must have its default database set to the database you will be pulling data from using the DSFM feature. If you are unable to set this value on the account appropriately, you will instead have to configure the DSN on the FootPrints server so that the default database option is defined there instead.

##LDAP directories made available via ODBC?##

Forcing the Use of Lookups

Dynamic SQL lookups save the user from the tedium of cross-referencing a database and copying values field-by-field into their FootPrint tickets. Besides the obvious convenience value of this, lookups promote data integrity by eliminating the possibility of transcription errors on the user’s part during the alternative, manual process. That’s not to say that all concerns for data integrity are resolved by lookups,however: there is no way to ensure that the user actually use the lookup; nothing to stop a user from taking a stab at manually filling out the fields that the lookups would have populated if activated.

Caution: The “Automatically perform this lookup when submitting a ticket” option on a lookup only applies to Customers.

Some use cases have little or no tolerance for data that does not conform to a strict set of rules. An example I’ve often seen is when a Change Management process obtains its approvers on a case-by-case basis by reading a User ID from a custom field on the ticket. If the value in that field where the approver’s ID is expected does not resolved to a valid User ID, no approver will be assigned (save for the backup approver defined for the CM process.) 

Users cannot be expected to always know or even correctly type in the user ID of the person who is to be the approver. What is needed is some way of ensuring that only certain, permitted values can be entered into this field. At this point, you might be thinking that a dropdown field listing all possible approvers is the way to go, but try to imagine the ongoing maintenance a dropdown field would require as users are added to or removed from the pool of approvers over time. Further, bear in mind that the values shown in this dropdown field might not be recognizable to the user forced to make a selection (how many users will know that User ID “89884-c” is really John from the Change Advisory Board?)

What I recommend for this scenario is a Dynamic SQL lookup that maps a user name to a user ID. The user filling out the ticket has in mind the person who should be the approver, and now all they have to do is enter his or her first and last names, or even just the first name, just the last name, or part of either name.

Now we return to the troubling fact that a user can simply forego the lookup and attempt to enter the approver’s User ID manually. An undocumented feature comes to our aid here. Enabling this feature requires editing the FootPrints configuration file called MRlocalDefs, which is found under FootPrints’ cgi folder. This file has no extension, but it does contain Perl programming code.

%MRLD_DSQL_FIELDS_READONLY = (
   # workspace 1:
   1 => {
      'Lookup Field' => ['Target Field']
   },
   # workspace 3:
   3 => {
      'Lookup Field' => ['Target Field1', 'Target Field2'],
      'Another Lookup Field' => ['Target Field3'],
   }
);