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.

Defining SQL lookups definitions (notes for DBA’s)

In the screen print below the user has clicked on the Edit icon for the “Matter population” Lookup definition, an SQL server definition, and can save changes they make to that line by clicking on the green tick. They can retain the original details (cancel any changes made, or abort the process of making changes) by clicking on the red cross.

Description is simply a user defined name for this Lookup definition, that is meaningful to you. It is the name by which this lookup definition will be linked into the Request form/s that use it.You can sort list listed lookup definitions by their Description by clicking on the hyperlinked “Description” heading.

Source object allows an SQL 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 data source (as per your DBA’s reftdbobject view). The drop down list of tables and views is automatically retrieved as this screen is displayed, and it’s mapping to RefTracker fields is defined by clicking on the spy glass icon.

All defined tables and views available for use in that data source 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 -they might have a naming convention like vReft_{some-name}.

The next section about Lookup details defines what is done with the information retrieved by the Lookup, including how information retrieved can be ignored, however if our recommendation that the DBA creates a specific view/s for that database is implemented, the view would just retrieve the information specifically required by that RefTracker Lookup.

A good reason for using a specific view is that the columns in the external database might contain a huge number of fields that are irrelevant to RefTracker that would only slow down the retrieval process. Another is that the retrieved data might need to be manipulated, for example if the external db contains a table ’employee’ which has a column for ‘first name’ and a column for ‘second name’ then the view could concatenate the two columns so that the single concatenated column could be mapped to the RefTracker client_name field (Select first_name + ‘ ‘ + second_name as name). Information about how to set this up is provided for your DBA in the previous section. You can sort the displayed Lookup definitions by the Source object that they use by clicking on the hyperlinked “Source object” heading.

Where clause allows the user to restrict the records being returned by the Lookup by specifying the SQL where clause to be used (e.g. where matter_no like ‘A%’ would return only matter numbers beginning with ‘A’). For the DBA creating this clause, the next section provides more detailed information about this “where” clause including information about variables available for use in it.

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 data source so that a valid value can be selected for this field (CANNOT be used with Pre and Post population). This type of lookup returns one value only so the details in its lookup definition will define just the one field that will be returned.
• 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)

Specifying the SQL “where” clause (notes for DBA’s)

The where clause is used to restrict the records returned by the Lookup. It is the SQL statement that will be performed on the External data source to retrieve the fields defined in your details mapping.

Your details mapping (done by clicking on the spy glass icon) can define as many fields as you want returned to RefTracker fields for Pre, Post and Dynamic lookups. But for Autocomplete lookups just one value is being returned to fill the Autocomplete field itself so the field mapping will define just the one field that should be returned as a result of your Where clause.

Another consideration in regard to Autocomplete is whether the field you are looking up contains unique data. If it does not contain unique data, the view needs to be created so that the value returned to the user is visually unique and can be used as a unique key to do any further lookups based on that data e.g. by displaying it as “<nonUniqueField> (<id>)”. Here is the view used to do this for a file containing a unique field Project_id and a non-unique field Project_name, where the autocomplete lookups will be on the non-unique Project_name field.

Variables for use by DBA’s in “where” clauses

The Where statement can include variables that are replaced by RefTracker before the Lookup is executed. Valid variables are currently as follows, however, others can be added so if you need another variable please contact your Altarama support representative.

Search variables:

$search_term will be substituted by search term entered into the RefTracker Request form field being used for this Lookup.

System environment variables:

$env_domainwill be substituted by the domain portion of the Windows Account HTTPContext.current.user.identity of the IIS logged on user.

$env_accountwill be substituted by the user portion of the Windows Account HTTPContext.current.user.identity of the IIS logged on user.

In regard to pre-population, there are four system variables that can be picked up the Dynamic Lookups processes, in order to identify the user (client), as follows:

$env_authuser will be substituted by the AUTH_USER variable. AUTH_USER is picked up from IIS and will have a value when users are logged in as a result of anonymous access to IIS having been turned off. You can see the current value of AUTH_USER by going to System>Utilities>List server variables.

$env_remoteuser will be substituted by the AUTH_USER variable. REMOTE_USER is picked up from IIS and will have a value when users are logged in as a result of anonymous access to IIS having been turned off. You can see the current value of REMOTE_USER by going to System>Utilities>List server variables.

$env_logonuser will be substituted by the AUTH_USER variable. LOGON_USER is picked up from IIS and will have a value when users are logged in as a result of anonymous access to IIS having been turned off. You can see the current value of LOGON_USER by going to System>Utilities>List server variables.

$env_remoteaddr will be substituted by the AUTH_USER variable. REMOTE_ADDR is picked up from IIS and will have a value when users are logged in as a result of anonymous access to IIS having been turned off. You can see the current value of REMOTE_ADDR by going to System>Utilities>List server variables.

Form variable:

$form:<fieldName>will be substituted by the value appearing in the form for the RefTracker field <fieldname> – for example use of ‘{$form:client_name}’ in your SQL statement will result in the value in the RefTracker field client_name in your form being substitute into your SQL statement at that point.

The form variable is a very powerful structure in that it allows parent/child relationships to be handled through the lookup process. Some example uses for this function might be:- your organisation may require that a Client number be entered first so that the Client number can be used as part of the retrieval mechanism for valid Matter numbers for that client only- your organisation may have a two tiered location structure – Location and the Practice group within that Location. When this is the case entering the Location in the form first means that it can then be used by the lookups function for the lookup of Practice groups to ensure that the client can only choose Practice groups that are valid for that Location.

Here’s an example of it in action:Here’s the form without data. The data entered into the Client number field will be used to obtain the right selection of values for the Matter number field. Both fields are lookup fields (a indicated by the icons at the end of each line).

Here’s the client selecting their Client number.

And here’s the selection of Matter numbers that automatically appear as a result of the lookup that uses the selected Client number (in other words, only the Matter numbers that are valid for that Client number).

And here’s the selection of Matter numbers that automatically appear as a result of the lookup that uses the selected Client number (in other words, only the Matter numbers that are valid for that Client number).

Lookup definition field mappings

Every SQL 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. In the screen print below, the user has clicked on the Detail definition icon (the spy glass) for the “Matter population” line which has caused the “Field mappings” table to display.

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 data source 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 data source each time this table is displayed.

Fields not previously mapped including newly found fields will show with the RefTracker target field unmapped.

You can sort the displayed field mappings by the External data source Source column or RefTracker Data Dictionary Target column by clicking on the appropriate column heading.

To edit the mapping, click on the edit icon at the end of its line. The RefTracker Data Dictionary 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 (see the Edit layout screen of the request form, to see the RefTracker data dictionary fields that have been used in that form). 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 as it will not have been saved.

Note that if you select a RefTracker code table as the field that is mapped to here (target field), you will also need to create a code map for that field (see a later section of this document about code maps). Code maps are required because the data coming from the source data source may well be different to the code table data held in RefTracker for that field (for example, a “3” in the source data source may equate to “Accounts” in your RefTracker Department code table). The need for a code map is automatically detected and an explanatory message displayed.

The number of fields that are mapped to code tables should be minimal – the most common need for a code table map is where data needs to be mapped to either the Staff or Client location fields. Code tables are used in RefTracker to ensure data is stored in a consistent fashion so that it can be easily converted to statistics. When data comes from an external data source using the lookups functionality, it will be provided in a consistent manner by the external data source, so it can be stored as text in text boxes or text areas and still be easily analysed for statistics – there is no need to store it in a code table just to ensure data uniformity.

Using the Lookups definition details table (notes for DBA’s)

The columns in the Lookup definitions details table are:

• External Data source Source column is the field that will be retrieved from the remote source data source by this Lookup as defined by the view. The system goes back to the external data source and freshly obtains this information from the specified view, each time this Lookup definitions details table is displayed.
• RefTracker Data Dictionary Target field is the RefTracker field into which the information from the remote source data source 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.

Your details mapping can define as many Target fields as you want returned to RefTracker fields for Pre, Post and Dynamic lookups. But for Autocomplete lookups just one value is being returned to fill the Autocomplete field itself so the field mapping for Autocomplete lookups will define just the one Target field that should be returned as a result of your Where clause.

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 data source 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.

Do not select mapping to a RefTracker Code table unless there is a consistency reason to do so. The external data source will provide consistent data suitable for statistical analysis, simply because it is controlled by the external data source. If you decide to map to a code table you WILL have to do a Code table map (see later) for that 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 data source, and up to 10 records retrieved from the remote source using that statement. If the Lookup you are testing requires a search term (has {$search_term} in the Where clause) to be provided to obtain a result, you will need to insert a valid search term in the box provided.

Exercise

Have your DBA examine the example Lookup definitions provided. Using the information provided above, have the DBA create a Lookup definition for each of the Lookups that you need to define for each of the External data sources.Then have them define the Lookup field maps that will be retrieved by each (they will need the assistance of the RefTracker system administrator who designed the form to which this lookup will be applied, to assist with this process). Remember that it is often convenient to define one Lookup that retrieves a number of fields, so that the one Lookup definition can be used for multiple purposes. When the Lookup is used, only the fields specifically defined in your request form will be saved – additional fields retrieved by the Lookup do no harm unless there are so many of them that they are effecting the time to transmit the Lookup results.

Defining Web service lookups definitions (notes for programmer)

In the screen print below the user has clicked on the Edit icon for the “EmailAutocomplete” Lookup definition, a Native web service definition, and can save changes they want to make to that line by clicking on the green tick.

To use this screen to map the information provided by your web local service to the way it will be used in a RefTracker form, provide the following information:

Description is simply a user defined name for this Lookup definition, that is meaningful to you. It is the name by which this lookup definition will be linked into the Request form/s that use it.You can sort list listed lookup definitions by their Description by clicking on the hyperlinked “Description” heading.

Web service token is the name that you provided for the type of lookup that you have defined in your local web service, in the System>Code tables menu>Other>Lookup token code table. See the previous section for how to set up these different types of response from your local web service. The next column (Type) allows you to associate the type of lookup that this web service will be associated with. You can sort the displayed Lookup definitions by the Source object that they use by clicking on the hyperlinked “Source object” heading.

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 (type 100) – 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 (type 200) – provides a drop down list of values for the external data source so that a valid value can be selected for this field (CANNOT be used with Pre and Post population). This type of lookup returns one value only so the details in its lookup definition will define just the one field that will be returned.
• Form population (type 300) – 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)

There is no field mapping process in this screen (as there is for the SQL setup), as the field mappings are defined by the XML schema used by this web service.