How to Replace an Open Text Field with a Dropdown to Ensure Data Consistency
You are running into issues with messy data in a few open text fields that users are manually filling in. Fields like Country or State should use consistent values across your database, so you want to change these free text fields to picklists, but before you can copy data from the free text to a picklist field, it needs to be cleaned up and standardized to match the dropdown values.
The Cleanse Data module makes it easy to identify data inconsistencies, standardize your data, and then convert free text fields to picklists.
- Create a new field in your CRM for the picklist.
- Use Insycle to identify variations in the field.
- Standardize the rogue values and copy them to the new picklist field.
- Disable the old free text field in your CRM.
In this example, we'll walk through changing the free text Country/Region field in HubSpot to a picklist.
In HubSpot, click the Settings icon in the main navigation bar. Then, in the left sidebar menu, click Properties.
Click the Create Property button and enter basic info about the new property. Make sure to give the property a name that can be differentiated from the old, open-text property. Then, click Next.
On the Field Type tab, click the Field type dropdown and choose Dropdown select.
Enter all the appropriate values to your new picklist, then click the Create button.
Before moving your data to a picklist, you need to identify the data variations that will need to be standardized to the same values. If you do not standardize first, your field variations will be copied into the picklist field, creating duplicate variations that can cause segmenting and reporting issues.
In the Cleanse Data module, select a record type from the top menu.
The field list under Step 1 shows all fields for the record type. This is not the record data itself but the meta information about the fields in the database. Use the search to find the field you want to clean up and check the checkbox next to the Field Label in the list. (This does the same thing as selecting the Field Name from the dropdown under Step 2.)
In this example, we'll be moving the Country field from free text to picklist, so we will search for the "Country" field. This will generate field-level statistics for the field below.
Once selected, the different values found in this field will populate the Record Viewer at the bottom of the page. The example below includes several variations on the values "United States" and also "Canada," which need to be addressed.
Now in one step, you can standardize the data so that it is consistent, populate the new picklist field, and clear the old free text field.
In the Record Viewer, check the checkboxes for the values you want to update. Select all the variants that should be the same.
In this example, all values referencing the "United States"—"united states," "USA," and "US"—need to be changed to, "United States."
Under Step 3, specify what the selected field values should be changed to. In this example, the new, Country Picklist field value will be set to "United States" for each value selected in the Record Viewer. The old, Country/Region field will be cleared by leaving the New Value blank.
Click the Update button and confirm the change. Note that there is no preview step here. Once confirmed, the update will be applied to your CRM.
The Bulk Update Result popup lets you know which records were successfully updated. You can review the changes that were made to your CRM using the Activity Tracker.
This same process needs to be completed for every value in the new picklist. In the example above, this would need to be done for each country represented in the database.
Once you've moved data from your old, free text field to the new picklist field, you should change the field properties in your CRM so it is no longer writable. This will preserve the field in case it needs to be reinstated, but prevents it from being used and requiring cleanup again.
If you're absolutely certain it won't be needed again, you could consider deleting the old field from your CRM entirely.
Looking at the information about each field can provide clues on data cleansing opportunities.
Field Label vs. Name
The Field Label is shown in the CRM interface while the Name is the column header in the database.
If there are two fields with a similar Field Label, such as "Phone" versus "Phone Number," looking at the underlying field Name may reveal different information that clarifies the actual purpose. You may want to update one of the Field Labels to reflect the difference.
Type, Value, and Writable
- Type – Field type, such as a picklist, number, text string, date, timestamp, true/false (boolean), etc.
- Value – Describes the type of data stored in the field such as text, number, date, true/false, etc.
- Writable – In the Insycle app, checked = True, indicating the field can be edited.
This is the count of different values that appear in this field across all records. This is a great place to look and see if there is data worth exploring. You could answer a question such as, "How many different job titles do we have?" It can also indicate a problem in fields where only a few values should be used, such as Industry, or Product. This is especially relevant on fields that should be limited to a picklist, or Yes/No values.
This is the number of records that don't have any value in the field.
Any field with a high number of empty values could indicate the field is unused or abandoned and needs cleanup. Or, it could indicate a syncing issue between Insycle and your CRM. If you see data in your CRM but the empty values number is high, contact Insycle support.
You can export all of the field meta information for all fields in your CRM by clicking on the Export button in Step 1. This will include all the field information, not the records.
If you'd like to see more information for each of the resulting records, you can alter the fields in the secondary Record Viewer by using the Layout tab in Step 2.
If you know a field in your CRM is used regularly but the empty values column for the field is high, this could indicate a syncing issue between Insycle and your CRM. Contact Insycle support for help.
If you are sure a field contains data in your CRM but aren't seeing the values or records in the Record Viewer at the bottom of the page, it is often due to the Filter settings under Step 2.
Here are a few things to look into:
- Make sure there isn't anything in the filter that you didn't intend to be. This often happens if you started with an existing template.
- Ensure that your filter is accurate, and not too specific. For instance, if you are using the "is" operator in your filter, you might broaden the condition using "contains" or "starts with" to identify other records with slight differences.
- Make sure that you have clicked the Search button.
If you still don't see the expected data, it is likely a field syncing issue.
To refresh the data in Insycle, navigate to Settings > Sync Status, select the account, and click the Refresh Accounts List button. Alternatively, you could log out of Insycle and then log back in.
For help re-syncing a specific field, contact Insycle support.
For a complete guide to troubleshooting issues with Insycle, please refer to our article on Troubleshooting Issues.
Frequently Asked Questions
A "-1" shows up when values for a field are currently not stored in Insycle. It indicates the CRM field is not syncing with Insycle. Note the Value column also shows, "not stored."
CRMs limit the number of fields that can be synced between Insycle and your database, so at a point, some will need to be excluded. If you discover a field that you need to sync but isn't, contact Insycle support for help. Fields can be prioritized to ensure the necessary fields are syncing.
Related Help Articles
- Module Overview: Cleanse Data
- Standardize Job Titles, Industries, Location
- Import New Records or Update and Append to Existing from a CSV
- Format Names, Phone Numbers, Addresses
Related Blog Articles