Lookup definition

The Lookups definition screen is only accessible if your organisation is licenced for the Dynamic lookups module.

Once you have defined the external database that will be used for your lookup in External databases, you need to define how it will be looked up by using this Lookup definitions screen.

The databases defined in External databases will automatically appear in the drop down list in the top right hand corner of the “Lookup definitions” screen, as shown in the screen print below, and for each database, the “insert new record” line can be used to define the different types of lookups that will be able to be used on that database.

There is no escaping the fact that an IT action is happening here and so the RefTracker “Lookup definitions” page should be completed by someone who understands the layout of the database being looked up, the data that will be retrieved and how that data will be used in RefTracker – and that will generally be an IT person.

New lookup definitions can be added by clicking on the green “plus sign” icon. The line will expand and provide data entry boxes for each of the columns of information that make up a definition. Once a lookup definition has been added, icons on the right allow it to be edited, deleted, and the mapping for the retrieved fields to be defined in detail. Use the Edit icon to change your lookup definition. Click the green tick to save changes that you make to that line. To retain the original details (cancel any changes made, or abort the process of making changes), click on the red cross.
Description is simply a user defined name for this lookup definition.
Source object allows a table or view to be selected for use with this particular lookup, from a drop down list of all of the tables and views defined for that database. The drop down list of tables and views is automatically retrieved and it’s mapping to RefTracker fields defined by clicking on the spy glass icon. All defined tables and views available for use in that database are returned so we highly recommend that the DBA for the database being looked up, should define a series of views that are going to be used specifically by RefTracker – see the Dynamic lookups documentation for guidance for your DBA.
Field maps (accessed using the View field map icon) defines what is done with the information retrieved by the lookup, including how information retrieved can be ignored, however if the DBA creates a specific view/s for that database, the view would just retrieve the information specifically required by that RefTracker lookup.
Where clause allows the user to restrict the records being returned by the lookup by specifying the SQL where clause (e.g. where matter_no like ‘A%’ would return only matter numbers beginning with ‘A’). This information will come from your DBA for that database – see the Dynamic lookups documentation for guidance for your DBA.
Type sets the way in which this lookup will be used in conjunction with the Request form, and as a result, the places it can be used:
– Dynamic population – uses the value in a field as the key for the lookup and returns values to the RefTracker fields as defined in the details of this lookup definition (CANNOT be used with Pre and Post population)
– Autocomplete – provides a drop down list of values for the external database so that a valid value can be selected for this field (CANNOT be used with Pre and Post population)
– Form population – uses the value in a field as the key for the lookup and returns values to the RefTracker fields as defined in the details of this lookup (can only be used with Pre and Post population)

Lookup definition field mappings:
Every lookup definition must also have mappings defined for the fields that will be retrieved by the lookup and how the information in those fields will be used in RefTracker.

The “Field mappings” table display automatically shows the fields that will be retrieved from the remote source by this lookup definition (the source column information is provided by the View defined in the database being accessed), and allows the field to which each will be mapped in RefTracker(target) to be defined. This information is freshly obtained from the remote database each time this table is displayed. Fields not previously mapped including newly found fields will show with the RefTracker target field unmapped.

To edit the mapping, click on the edit icon at the end of its line. The Target field will display as a drop down list that shows all of the fields that can be used in RefTracker forms (as controlled by your Data Dictionary settings). For each retrieved field that needs to be used in RefTracker forms, you need to select which RefTracker field the data will be saved into based on the use to which you have put each RefTracker field. The field that you select is the field that the retrieved data will be stored in if you include that field in the Request form doing the lookup. If you do not include that field in the Request form, your staff will not be able to see the data that has been retrieved when they work with that question in RefTracker. Note that if you select a RefTracker code table here, you will also need to create a Code map for that field. Code maps are required because the data coming from the source database may well be different to the code table data held in RefTracker for that field (for example, a “3” in the source database may equate to “Accounts” in your RefTracker Department code table).

The fields in the lookup definitions details table are:
Source column is the field that will be retrieved from the remote source database by this lookup as defined by the view.
Target field is the RefTracker field into which the information from the remote source database will be inserted by this lookup definition. Initially this will show as not mapped which will result in the retrieved information not being loaded into any RefTracker field. To select a particular RefTracker field for the information in this external source field to be mapped to, click on the Edit icon and the RefTracker field column will display as a drop down box. Clicking on the drop down box will show a list of all of the fields enabled in your RefTracker Data Dictionary so that a mapping can be selected – for example “phone” in the remote database might be mapped to the RefTracker client field “Client phone #”. Data loaded into fields in this way, will be viewable by staff in the Details screen, but they will not be viewable by clients unless you specifically include the RefTracker field in your Request form as a Display, Optional or Mandatory field. Don’t forget to do a Code map if you select to map to a code table field.

Test lookup:
To assist with the detail definition and mapping process, a “Test lookup” link is provided. Clicking on this link will display the SQL statement that will be submitted to the remote database, and up to 10 records retrieved from the remote source using that statement. You may need to enter a search parameter, according to the purpose of the lookup you are working with.

Full details of how to use the Dynamic lookups module are provided in the Dynamic lookups module manual.