Lookup Lists

Lookup lists are values referenced by lookup fields such as gender, legal problem code, county, etc. The values and settings in a lookup list determine what users see in the dropdown list for a lookup field.

Tip: Although Offices and Grants (Funding Codes) appear to be lookup fields, they are special cases and each has its own admin page (Admin > Offices and Admin > Grant Management).

This page covers system lookups. For information on creating custom lookup fields and lists, and maintaining custom lookup lists, see Fields - Adding New Fields to a Site.

Lookup Lists

Creating and Maintaining Lookup Values

Batch Edit

Merging Lookups

Canceling a Merge

Mark Inactive or Merge?

Exporting and Importing System Lookup Values between Sites

Creating and Maintaining Lookup Values

Lookup lists are maintained from the Admin > Lookups page.

Clicking on the name of a lookup will display the list of values. In this example, the Case Status lookup:

Add a new lookup value by entering a new name in the blank fields at the top of the page and clicking the Continue button. (Lookup lists only display active values by default; it is a good practice to change the filter on the list to display all lookup values to avoid creating duplicate values.)

Edit an existing value by clicking its name to display the value's properties at the top of the page, including the display name, whether the value is active, and the order in which it appears in the list.

The Name of a lookup value can be changed without changing any data. The name is simply a text label for the underlying ID in the database.

The Active field determines if the value shows up in dropdown lists on forms and in report filters. Making a value inactive does not delete or change any data.

The List Order of all active lookup values determines the order of the values users will see in a dropdown list. Lookup values with the same list order are sorted alphabetically. For example, to have English and Spanish appear at the top of the language dropdown, you could give each a list order value of 0, and all other languages a list order value of 1 or blank. All list values with 0 are sorted together alphabetically, followed by list values with 1, etc. List values with no list order are sorted together last.

Some lookup lists have additional columns. Citizenship Statuses, for example, has a column for LSC Eligible and Site Eligible. The values in those columns are used by the Citizenship Status block and eligibility blocks. That lookup also has a Legalserver Index ID column that is used in electronic case transfers to another LegalServer site.

Some lookup lists will have a System column and/or have values marked "[System]" in the Action column. These values cannot be changed except for list order. If your site needs a System value changed, for example to inactive, contact PSTI support.

Batch Edit

The "Batch Edit" actions menu link lets you edit the List Order and Active value for multiple lookup values on one page:

Depending on the specific list, you may be able to edit additional attributes for each value.

Merging Lookups

Lookup values can be merged into another value, or deleted if the value is not referenced by any records in the database. Merging changes the numeric ID in the lookup field on the underlying records, then deletes the lookup value that was merged.

For example, if you merge an asset lookup value of "Cash on Hand" into "Cash", that would change the ID on each record that referenced it to now point to the ID for "Cash", then delete "Cash on Hand" from the lookup list for assets.

Clicking on Merge/Delete for a lookup value displays the following form:

The form displays the number of records that will be affected by the merge. If there are no records associated with the value, it can be deleted. The number of records does not equal number of cases, outreaches, or similar records using that value. The value may be referenced by other lookups or other 'internal' records in the database. In other words, in the example above, you might run a case report and find no cases using the "Car/Other Vehicle" value.

Once you select which value the records will be merged into, clicking the proceed button will place the merge in the merge queue. It will also mark the value inactive, meaning it will no longer be available for selecting on forms.

If the Skip Queue option is selected the merge will take place immediately and cannot be canceled. This is referred to colloquially as the "I'm Feeling Lucky" checkbox. Merging hundreds or thousands of records will temporarily slow down your site. How temporary and how slow depends on the number of records being merged and the lookup. Lookup values used on person records, for example, take longer than values used on matters.

Canceling a Merge

Scheduled merges can be canceled for a short period on the Admin > Lookup Merge Queue page.

If a merge is canceled in the queue, the lookup value will still be marked inactive and must be manually changed to active if it should still appear in dropdown lists on forms.

Once completed, merges cannot be undone. Reversing the effect of a merge may be possible by restoring data from a backup. However, that can only be done by PS Technologies, is a chargeable action, and can only restore the data as it was at the time of the backup.

Mark Inactive or Merge?

Marking a lookup value inactive preserves existing data. Merging a lookup value into another one changes existing data, and generally cannot be undone, as explained in Merging Lookups section.

Example: A site has "Transgender" as a lookup value for the Gender field, as required by a specific funder. If the agency no longer has that funding, and does not want to continue to use Transgender, but needs to be able to report on the number of cases that have that value, the Transgender value should be marked inactive, not merged into another value.

Exporting and Importing System Lookup Values between Sites

The values in a system lookup list can be exported from one site and imported into another, for example from a demo site to a live site, or from one agency's site to another agency's site. Custom lookup lists cannot be exported and imported.

Your user role needs the "Import and Export Lookup Values" permission to use this feature.

To export a list, display it, then use Actions menu > Export Values link. This will download a "json" file. If the lookup being exported is Race, the file will be named "race.json".

All values in a list are exported, including inactive ones. You may want to clean up a list by merging inactive values before exporting.

Each lookup value's attributes are exported; including the name, whether the value is active or inactive, whether it is a 'system' value, and the LegalserverIndex ID it is mapped to (if applicable). This means that an inactive lookup value will be imported as inactive.

To import one or more lookup lists, display any lookup list, then use the Actions menu Import Lookups link to display the page where up to 5 files can be uploaded:

Use the Browse buttons to select as many files as are being imported, and optionally check the "Duplicate" box.

By default, lookup values with the same name (or internal database note, if any) will not be imported, and a message indicating that will be displayed.

Lookup lists are imported based on the database name of the list, not the "Title" of the list (which can be changed by administrators). This is why you can use the Import Lookups link from any lookup list, and can import up to 5 at a time.

You can see the database name for a lookup list in the URL when viewing it. For example, most sites have a lookup with the title "Asset", but the database name is "liquid_asset". The import feature would import that list's values to the corresponding "liquid_asset" list on the importing site, regardless of the title of the list on either site.

Page updated

Report abuse