The Value Lookup Table is an option for offices needing an easy way to store and manage large amounts of data. This article covers the following topics related to lookup tables:

• Understanding Lookup Tables
• Importing a Lookup Table
• Creating a Lookup Table Manually
• Downloading a Lookup Table from the TDCL
• Managing Lookup Tables
• Frequently Asked Questions

Understanding Lookup Tables

A lookup table stores a large amount of data. It can be used when your database requires a code or abbreviation whereas site visitors might need to see option values expressed in a more user-friendly way. For example, it might be desirable for a drop-down list to display "Argentina" to applicants but for the corresponding value stored in the database to be "AR". Codes and abbreviations are often required in SIS exports and SEVIS reporting.

Other examples of value lookup tables might include the following:

• Country abbreviations
• Major/Minor with corresponding CIP codes
• Class/Level
• Programs

Permissions & Access 

The permission of Settings: Lookup Tables is required in order to access the lookup table administrative tool. It allows the admin to view, create, edit, export/import, and delete lookup tables and lookup table values.  

Keys & Default Values

A lookup table is comprised of two parts:

• Key
  • Code or abbreviation that is stored in the database.
  • Not displayed to site visitors.
  • Example: "AR"
• Value
  • Option available and visible to site visitors throughout the site.
  • Example: "Argentina"

To use lookup tables in Terra Dotta, an admin will first need to create a lookup table to indicate which "values" correspond to which "keys". Once the lookup table has been created, it can be selected for use in question items, applicant parameters, or program parameters. Then a single-select drop-down list generated from the available, user-friendly values in the lookup table will be displayed to applicants.

There are three ways to create a lookup table:  

• Import a lookup table
• Create a lookup table manually
• Download a lookup table from the Terra Dotta Community Library (TDCL)

Importing a Lookup Table

Navigate to Settings > Lookup Table and take action with these steps:

1. Click the "New Lookup Table" button. From the page that appears, click the "Download Template" link. This action opens the Excel template that will be used during the import.

2. Enter your data into the template as follows:

• Key Column
  • Populate the data fields in this column with a code or abbreviation.
  • It is sometimes the case when it doesn't matter if the "Key" and "Value" are populated with the same data. In this case, the data entered in both columns can be the same.
• Default Value Column
  • Populate the data columns with the user-friendly value that you want displayed to site visitors.
  • It is sometimes the case when it doesn't matter if the "Key" and "Value" are populated with the same data. In this case, the data entered in both columns can be the same.
• Hide Column  
  • If you do not want applicants to see the value as a picklist option, then enter a value of "1" to hide the pair.
  • If you want applicants to see the value as a picklist option, then enter a value of "0".
  • Most new key/value pairs will have a "Hide" value of "0".

Important Note: Values are limited to 100 characters per column. Entering more than 100 characters will result in your text being truncated when displayed in the lookup table. 

 

3. Save the file as ".txt" or Text (tab-delimited format). Mac users should save the file as a windows-formatted text file if there is difficulty during the upload process. 

4. Navigate to Settings > Lookup Tables and click the "New Lookup Table" button. This will open a new "Add Data Lookup" page.

5. Lookup Name: Enter a name for your table. This will display under Settings > Lookup Tables.

6. Upload File: From this section, the following actions are possible:

• Click "Browse" to search and locate the applicable text file from your computer.
• Indicate whether or not the first row in the file contains column header names. This box is checked by default. 

7. Click "Save" to preserve your changes. This action will bring you back to Settings > Lookup Tables where the name of your lookup table will appear as a new row. Under the "Number of Entries" column, a number will populate that reflects the number of rows populated. This should match your text file. 

From the "Actions" column, export the lookup table, make edits, or delete it from your site. 

Creating a Lookup Table Manually

To create a new lookup table manually, follow these steps:

1. Navigate to Settings > Lookup Tables. Click the "New Lookup Table" button. This directs you to the "Add Data Lookup" page.

2. In the "Lookup Name" field, enter a name for your table. This will display under Settings > Lookup Tables.

3. In the large text box provided, type - or copy and paste - the key/value pairs. The key value should be listed first, followed by a comma, then followed by the default value (spaces are not necessary).

For example: GA,Georgia

Important Note: Values are limited to 100 characters. Entering more than 100 characters will result in your text being truncated when displayed in the lookup table. 

4. Click "Save" to preserve your changes.

Downloading a Lookup Table from the TDCL

Several lookup tables are available for download from the Terra Dotta Community Library (TDCL). In addition to these pre-made lookup tables, the TDCL also contains many lookup tables that will be used with the SEVIS module for offices with the Terra Dotta ISSS solution. 

To download a lookup table from the TDCL, follow these steps:

1. Navigate to Settings > Lookup Tables. Click on the "Browse TDCL" button. Available lookup tables are listed with information organized into the following columns:

• Lookup Table Name
• Number of Records
• Date Added
• Date Updated
• Actions 

2. Locate the desired lookup table and click the corresponding "Import Lookup Table" icon under the "Actions" column to install the table on your site. The lookup table is now downloaded and can be edited, if necessary, before use.

3. After downloading a lookup table from the TDCL, it can be edited, if necessary, and then used in applicant parameters, program parameters, and question items. All lookup tables downloaded from the TDCL will also have a refresh "double arrow" icon so that updates made to the lookup table in the TDCL can be imported.

Managing Lookup Tables

Deleting a Lookup Table

Lookup tables can only be deleted if they have not yet been configured for use in a question item, applicant parameter, or program parameter. Once the lookup table is in use, the delete function will not be available because of the dependencies and the need to retain historical data integrity. If a dependency exists, a "View" icon will appear in that lookup table's action column under Settings > Lookup Tables.  

If a lookup table was created but not used elsewhere in the site, the admin can delete it by navigating to Settings > Lookup Tables and clicking the red "X" icon in the table's "Action" column. If a lookup table was created and used, then it cannot be deleted. However, question items that use the lookup table can be retired and applicant/program parameters that use the lookup table can be deleted so that site visitors no longer interact with the lookup table. 

 

Using a Lookup Table

Once a lookup table has been created, it can be used in applicant parameters, program parameters, and question items. When creating or editing an app parameter, program parameter, or question item, select "Data Lookup" or "Data Lookup w/Search" as the type and then select the appropriate lookup table from the drop-down list at the bottom of the page. An applicant or admin will see a picklist of all the "values" from the lookup table and will be permitted to select one of the options in the picklist. 

Data Lookup Type: When an applicant parameter is configured with a lookup table type of "Data Lookup", the applicant will see a picklist of options and is able to select one value from the list. 

Data Lookup w/Search Type: When an applicant parameter is configured with a lookup type of "Data Lookup w/Search", the applicant is presented with a blank field that uses a type-ahead search. After an applicant enters the first three characters of their desired response, the lookup table will reveal options that match the typed value until the applicant locates and selects the value they want. This option is especially helpful when the list of key/value pairs is extremely long and scrolling through one long picklist would be too cumbersome.

 

Adding New Key/Default Value Pairs to a Lookup Table

Should a new value need to be added to a lookup table, navigate to Settings > Lookup Tables and take these steps:

1. Click the edit pencil of the lookup table for which you'd like to make edits. You then have two options:

• Import additional key/value pairs. First, create a file with the new value pairs using the same import template as described previously. Browse your computer to find the file, and click the "Save" button. The new value pairs will be added to the table while existing value pairs remain unchanged. OR,
• In the large text field provided, enter the "Key" and then the "Value", separated by a comma. If adding more than one new option, add the second option to the next line and so forth. These new values will simply be added to the table without changing existing values.

Editing Lookup Table Values

Important Note:

• The two options below can only be used to edit a value, not a "key". Attempting to change a "key" in this way will result in a new key/value pair being created with the corresponding value from the import file.
• Editing a value will update any answers that applicants selected prior to the change. For example, if an applicant had already selected "Georgi", the update to "Georgia" will be pushed to any instances where "Georgi" had been selected previously. Changes will be noted in the system under the Lookup Name (see below for example - Georgia was Georgi).

A lookup table's values can be edited in one of the following ways:

• Via Manual Edit

If you need to edit the value of a key/value pair in a lookup table, navigate to Settings > Lookup Tables and click the edit pencil for the desired table. Enter the new value in the "Copy/Paste Lookup Data" box, along with the corresponding key. 

Example: The data was Key=GA, Value=Georgi. To change "Georgi" to "Georgia", enter GA,Georgia in the box and click "Save". The value will now show "Georgia" instead of "Georgi".

• Via Import 

Navigate to Settings > Lookup Tables and click the Excel spreadsheet icon for the desired lookup table. This will generate an Excel file with all of the current keys and values for that lookup table. 
 

Make the edits to the default value(s). Make sure there is a "0" in the Hide column for any key/default value pair that you want to appear to the applicant (or a "1" for the pairs you do not want to appear to the applicant). Then save the file as Text (Tab delimited).

Navigate back to Settings > Lookup Tables and click the edit pencil for the desired lookup table.  Select "Choose File" and select the updated file from your computer. Click "Save" to preserve your changes.
 

Removing key/default value pairs from a lookup table:

In order to remove key/default value pairs from a lookup table, the admin should navigate to Settings > Lookup Tables and click the Excel icon for the table where values will be removed. The Excel file of the current key/default value pairs will include the following columns: Key, Default Value, Hide. Change the designation in the "Hide" column from 0 to 1. All active options that are available to applicants should have a "Hide" value of "0". Old options that should no longer be displayed to applicants should have a "Hide" value of "1". Import the file.  

Because it is important to retain all values in the lookup table for historical records, it is not possible to completely remove/delete the key/default value pair options from the lookup table. Instead, admin can hide the unwanted options from view.  

Reporting on lookup table keys and values:

• Query results > Create Report from the "Options" drop-down list at the top of the page:
  • Reports generated through the standard report wizard will show the default value for any lookup table question item, applicant parameter, or program parameter included in the report. Standard reports do not display the key.
• Maintenance > Data Import/Export > Bulk Export
  • Full data exports will show the key for any lookup table question item, applicant parameter, or program parameter included in the report.  Full exports do not display the default, user-friendly value.

Frequently Asked Questions

1. Can I use applicant parameter lookup table values as Data Access Objects for permissions?  For example, I want to allow a staff member to see only those applicants with a U.S. state value of "Alabama".

It is not possible to use lookup table values as Data Access Objects. Only single selection, multiple selection, and yes/no applicant parameters can be used in this capacity.

2. Can I filter process elements based on applicant parameter lookup table values? For example, I want to deploy a questionnaire only to those applicants with a U.S. state value of "Alabama".

It is possible to use lookup table values as filters for process elements. Single selection, multiple selection, yes/no, data lookup, and data lookup w/search applicant parameters can all be used in this capacity.  

A data lookup type applicant parameter can also be used as a condition with Deployment Rules.

3. I have a really, really long picklist of values.  I don't need associated keys/codes/abbreviations, but I love the w/search feature of lookup tables where it reveals matching values as the user types. Can I use that functionality in a non-lookup table capacity?

The w/search functionality is only available for applicant parameters, program parameters, and question items that use a lookup table to generate the picklist. However, even if you don't need associated keys/codes/abbreviations, you can still create a lookup table so that you can use the w/search functionality. To do this, simply create a lookup table and enter the exact same value for the key and the default value in the table.

4. Are lookup table question items and parameters single-select and/or multi-select pick lists?

Picklists generated from a lookup table are single-select.

5. Can keys and values containing commas be added to a lookup table? (Example: 20,40 or Chapel Hill, NC)

Numerical values separated by commas can be added to a lookup table, but text values separated by commas can not. So in the example above, "20,40" can be added while Chapel Hill, NC cannot. To import numerical values containing commas, you must first manipulate the .txt file. In order to use commas the admin must use the Excel Template provided under Settings > Lookup Tables > New Lookup Table > Download Template. The Keys and Values should be added then the spreadsheet should be saved as a text tab-delimited file (.txt). Upon saving as a .txt file quotation marks will be automatically added to each row that contains values separated by a comma. Before importing the .txt file, the .txt file should be opened and the quotation marks removed. Failure to remove the quotation marks from the .txt file will result in quotation marks being added to the Lookup Table values.

6. Can values with leading zeroes or quotations be added to a lookup table?  (Example: 0091 or "TDU")

Values that contain leading zeroes or quotations can be imported, but not without first manipulating the .txt file to be imported.  Before importing the .txt file, the .txt file should be opened and the leading zeroes added back to the values and any special characters that were added in the place of a quotation should be removed and replaced by the quotation marks. Failure to do so will result in erroneous values being added to the Lookup Table.

7. Why are some of my look up table values surrounded by quotation marks?

If you are experiencing lookup table values being surrounded by quotation marks this is likely due to a special character. The most common example is having a comma in the value. Removing the comma will remove the quotation marks.