Lookup Transform Index
Purpose: Allows mapping lookup values to separate admin-created values in reports.
Use Case: A funder requires that you only report whether a client's current living situation is Housed, Unhoused or Not Reported. You collect this data using the "Current Living Situation" lookup list but currently collect 10 different living situations types. You want to create a report that pulls from your current data but shows the funder-required values without having to create a new, funder-specific field.
Use Case: Pivot a multi-select lookup field into separate columns for each value.
Cost: No additional cost. Enabled on all sites.
User Role Permissions Needed: Add Lookups and Import and Export Lookup Values
Errata: During development, the informal name for this feature was "The Transformer". It is not called that for fear of getting a copyright violation notice from the movie or comics people. But referenced here so anyone searching this site will find this page.
Step 1: Create an Index Set
An Index Set is a collection of lookup lists whose values are mapped to other values using the Export Index Management tool. Admins can create an Index Set for each funder in order to list out the various lookups that need to be mapped and exported specifically for that funder, or can create Index Sets for internal reporting and day-to-day metrics for folks like managing attorneys, supervisors, etc. Admins can create as many Index Sets as needed, and Index Sets can be edited, inactivated, and updated as needed.
Navigate to Admin > Process Management > Lookup Export Index Management (or Search: Lookup Export Index Management).
On the landing page's list view, you are able to see a collection of current Index Sets, and the ability to add new Index Sets.
To add a new Index Set, type the desired name, confirm "Active" is set to yes, and click continue. To delete or merge Index Sets, use the delete/merge link in the list view.
Your Index Set is now ready to use. To add a lookup list and its mapping to an Index Set, you must navigate to the lookup list itself.
Step 2: Map the Lookup List
Navigate to the lookup list you would like to map (Admin > Process Management > Lookups (or Custom Lookups) or search for "Lookup" on the Admin page. This listview below displays the available system lookups that most admins are familiar with, but the Export Index Management Tool can also be used to map custom lookups (additional screenshots for that process, below).
System Lookups
To map a system lookup using the Export Index Management Tool, first click on the name of the lookup list you wish to edit.
In the Actions Menu of the lookup page, select "Manage Export Indexes".
Custom Lookups
Note that when using the Export Index Management Tool for custom lookups, instead of going to the Actions Menu on the lookup list, you will access this process using the clickable blue Manage Export Indexes link:
Initial Export Index Page
If you get a mostly empty page after clicking either Manage Export Indexes link, click the + icon in the filter bar on the right:
The next page allows you to create the lookup list mapping. At the top, you are required to select an Index Set for this lookup list. You can then enter numeric and text values to assign to your lookup list values.
In the example below, only "Homeless" should appear in the report as "Unhoused". Therefore this is a one to one mapping. There are two values in the lookup list that, when selected, should be reported as "Unknown", and all others should be reported as "Housed". The value allows you to set a numeric value to report, instead of the label, if required.
NB: Be careful to not map a lookup value more than once. Doing so could result in duplicate rows on a report, even if you do not use the transformed field on the report.
NB2: Do not include special characters ("/", "\", etc.) in the Values and Labels.
Step 3: Adding the Transformed Values in Reports
The transformed label and value fields are available under the lookup subtable of the mapped value.
In this use case, Living Situation is found by going to Person -> Living Arrangement [Lookup] -> Living Arrangement label (mapped to "Funder X"). If you wanted to add the value, rather then the label, it would be found under Person -> Living Arrangement [Lookup] -> Living Arrangement label (mapped to "Funder X").
If the funder requires a strictly formatted report with only codes, it can be difficult to read the report to determine its accuracy. It may help to make two reports: one quality assurance report with your easy to understand label, like 'Unhoused' and also that choice's value '02', and a second report with just the codes the funder requires.
Your mapped values will be found under their respective lookup subtables. These are added automatically when you map the values of the lookup.
The report for this example shows Living Arrangement as it is recorded by the front-end user, the mapped value and the mapped label. You can change the column labels, sort, filter and take advantage of all the reporting functionality described in our reports manual.
Additional Documentation
Lookup Map Transform and Export Tool Video: https://youtu.be/rvZDpIRv59w
Notes/Known Issues
- This process will not impact the ability for an admin to add a lookup field to a form. It will not impact the values the front-end user sees in that field.
- If you have only one Index Set on your site, you cannot merge/delete it.
