Updating Pick List Values

[Grant]

Note: These instructions will require direct access to the database via SQL. If you are hosted on Specify Cloud, please reach out to Specify Support for assistance. If you are self-hosted, please contact your IT department.

Make a database backup before proceeding with any of the following steps.

Any field in which data is limited to a controlled vocabulary is called a ‘Pick List’. Specify ships with many fields already formatted as pick lists and populated with choices.

Before proceeding, please click here to learn more about pick lists.

Pick lists are assigned to a field using the Schema Config tool. For this guide, we will be using a pick list named Size.

If a pick list is used with multiple fields, some steps will need to be repeated across each field the pick list is assigned. You can easily find every place a pick list is used by going to the Form Meta menu by clicking the (gear) button in the top right of the pick list form.

Each item within a pick list has two components:

1. Title: The name shown in query results, on the data entry forms, and when searching.
2. Value: The value stored in the database in each record.

Changing the Value field will update the value for the pick list items. However, it will not update the records that already have values captured in them. To keep these records in sync, you will need to batch update them using the following guide.

Behind-the-scenes, the pick list named Size is made up of a few database records. To retrieve the first record we need to reference, run the following SQL command:

Pick List Tables

SELECT PickListID, Name, ReadOnly, SizeLimit, CollectionID FROM picklist WHERE Name = 'Size';

This will return all pick lists within the picklist table that have a Name that equals ‘Size’. In my case, this pick list exists only in one collection, identified by a CollectionID of 4.

PickListID	Name	ReadOnly	SizeLimit	CollectionID
1336	Size	1	5	4

Take note of the PickListID and CollectionID.

If there are multiple rows returned, take note of each PickListID and CollectionID. Note that a pick list with the same name can have different values between different collections, so we highly reccommend doing this one collection at a time.

Now we need to run another SQL command to retrieve the items in that list via SQL (replacing the PickListID with the appropriate value for you):

SELECT PickListID, PickListItemID, Title, Value FROM picklistitem WHERE PickListID = 1336;

This returns the list of items currently available in that pick list along with the values and titles.

PickListID	PickListItemID	Title	Value
1336	6799	Extra Small	XS
1336	6800	Small	S
1336	6801	Medium	M
1336	6802	Large	L
1336	6803	Extra Large	XL

What we want to do in this case is make the Value equal the Title in both the picklistitem table and the database fields to which this pick list (in our case, Text1 in collectionobject) is assigned.

Pick List-Assigned Field(s)

We must also verify that the current pick list values match the ones in the Text1 field in collectionobject within our collection:

Command:

SELECT 
  Text1,
  COUNT(*) AS Count
FROM 
  collectionobject
WHERE 
  Text1 IS NOT NULL
  AND CollectionID = 4
GROUP BY 
  Text1
ORDER BY 
  Count DESC;

Output:

Text1	Count
XS	808
S	568
M	380
XL	294
L	250

Remember to save the output for reference so we can ensure the update was successful!

Before updating the picklistitem records, we need to make sure the new values fit in the desired field.

Update Field Values

Before proceeding, make sure to start a new transaction to prevent unintentional database edits.

Remember that the values must fit within the field being updated. In cases where a pick list is assigned to a ‘byte’ field, such as AgentType in Agent, VarType in AgentVariant, or ReferenceWorkType in ReferenceWork, you can only use byte values (between -128 and 127).

To update the database field values, you can create a simple SQL script to UPDATE the appropriate table(s) and field(s). In this case, I need to update collectionobject, setting Text1 equal to the appropriate new value. Remember to check if other fields are assigned this pick list (using the ‘Show Usages’ option demonstrated above) to know if additional updates are needed.

Previously, XS, S, M, L, and XL were the values for my pick list. Now I want to update them to be the full title value. To do so, I wrote the following short script:

UPDATE collectionobject
SET Text1 = CASE Text1
    /* This is essentially WHEN 'Text1' is this value THEN 'Text1' should equal this
       Replace with the appropriate values! */
           WHEN 'XS' THEN 'Extra Small'
           WHEN 'S'  THEN 'Small'
           WHEN 'M'  THEN 'Medium'
           WHEN 'L'  THEN 'Large'
           WHEN 'XL' THEN 'Extra Large'
           ELSE Text1
           END
WHERE CollectionID = 4;

Note that it scopes the update to the appropriate collection. After running this script, I can verify that the values are appropriate by running the command we ran earlier.

Command:

SELECT 
  Text1,
  COUNT(*) AS Count
FROM 
  collectionobject
WHERE 
  Text1 IS NOT NULL
  AND CollectionID = 4
GROUP BY 
  Text1
ORDER BY 
  Count DESC;

It is important to double-check the counts to make sure they remained the same before and after the update:

Output:

Text1	Count
Extra Small	808
Small	568
Medium	380
Extra Large	294
Large	250

Before the update:

Text1	Count
XS	808
S	568
M	380
XL	294
L	250

Perfect! The update was a success.

Updating the pick list item records

Now we will do something similar for the items in the pick list. Now we need to update (still within the same transaction) on the picklistitem table to match:

Command:

UPDATE picklistitem
SET Value = CASE Title
    /* This is essentially WHEN 'Title' THEN 'Value'
       Replace with the appropriate values for your pick list! */
           WHEN 'Extra Small' THEN 'Extra Small'
           WHEN 'Small'      THEN 'Small'
           WHEN 'Medium'     THEN 'Medium'
           WHEN 'Large'      THEN 'Large'
           WHEN 'Extra Large' THEN 'Extra Large'
           ELSE Value
           END
WHERE PickListID = 1336;

Once completed, run the command to see the picklistitem values once again:
Command:

SELECT PickListID, PickListItemID, Title, Value FROM picklistitem WHERE PickListID = 1336;

Output:

PickListID	PickListItemID	Title	Value
1336	6799	Extra Small	Extra Small
1336	6800	Small	Small
1336	6801	Medium	Medium
1336	6802	Large	Large
1336	6803	Extra Large	Extra Large

Once you verify all looks good, make sure to commit the transaction and then open Specify.

---

Verifying in Specify

You’ll want to build a query on the field(s) the pick list is assigned to. Make sure to include only the pick list field and make the query distinct by checking the box in the bottom right corner.

In the results, you should see the titles presented to you. You can click on the Create CSV to verify that the values are shown correctly in the export file. You can click on the (slide drawer) icon next to each result to verify the pick list appears valid and appropriately.

---

If you have any questions or need help, please feel free to reach out!