Convert Field Type From Free-Text to Picklist

Country picklist

 

Changing a free-text field to a picklist makes sense for certain fields. Fields like country or state, that you want to standardize and use a specific convention for, are bound to be messy if you use a free-text field and expect users or employees to manually enter values.

But you can't just change a free-text to a picklist value without first standardizing your data, or else you will create many inconsistent variations within the picklist. First, you have to standardize your data to the values that you would like to be present in the picklist, and then you can convert the field from free-text to picklist.

Insycle makes it easy to identify standardization issues, standardize your data, and then convert free-text fields to picklists.

Summary

Converting a free-text field to a picklist can be a good idea for keeping your data consistent. But because you can't convert a field without first standardizing the values in it (or else you will create many inconsistent values within your picklist), you must first go through a standardization process before converting.

Insycle makes this easy. First, you create the custom picklist field in HubSpot. Then, you can use the Cleanse Data module to gain a top-down view of the values present in your fields, standardize them, and move the data to your new custom picklist field.

Create Your New Custom Picklist Field In HubSpot

In our example, we'll be moving data in our free-text "Country" field to a new picklist property.

Before we can move data from a free-text to a picklist, we must have a picklist property created to house the data. If you already have one created, you can skip this step.

If you do not have your picklist created, creating one in HubSpot is simple.

In HubSpot, click the settings icon in the main navigation bar. Then, in the left sidebar menu, click on "Properties."

properties

 

Then, click the "Create Property" button. You'll be taken to a screen where you enter basic info about your new property.

Create new Property basic info

 

Then, you'll be taken to a screen that allows you to provide values for your new picklist. Fill these values in, in full. "Dropdown select" is the picklist type we are choosing.

Country picklist

 

Then click the "Create" button to create your new picklist.

Explore Standardization Opportunities, Then Standardize in Bulk and Convert From Free-Text to Picklist

With Insycle, you can use the Cleanse Data module to explore your data, identify opportunities for standardization, and move data from a free-text field to a picklist.

It's simple. First, you tell Insycle what field that you would like to explore and convert. Then you identify all of the different variations that you would like to standardize within that field, update them, and pass that data to your picklist field.

Step 1: Review Field-Level Statistics

Select the Cleanse Data module from the main navigation menu on the lefthand side of your screen.

Before moving your data to a picklist, it's important that you have a complete picture of the field that you are working with. Then we must standardize the data to be consistent before moving it.

First, we will search for the field that we would like to standardize in the search bar of Step 1.

Field-level stats

 

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.

Field-level stats for country field

 

Notice that this provides us with some basic information about this field including the field name, type, whether it is writable, and the total unique values in the field.

Step 2: Pick a field to explore its underlying values

Here, we get to explore the values contained within the field. Select the "Country" field.

There are two ways to do this. First, you could select the field using the checkbox in step 1. Alternatively, you can select the field in Step 2. Both methods are the same setting. If you select the checkbox, the field name in Step #2 will be pre-populated.

Once you have a field selected, the underlying values are viewable in the Record Viewer.

Country variations

 

Additionally, you can select one of the buckets by checking the checkbox.

select

 

This opens up a secondary Record Viewer, allowing you to view individual records contained within that bucket.

Record Viewer

 

Step 3: Standardize Values and Populate Your Picklist

Now we can standardize our data so that it is consistent and populate our new picklist field. You can select entire buckets of records from the Record Viewers on the bottom.

For our purposes, we are going to standardize all data referencing the "United States" to "United States," removing variations like "USA," and US." Then, we move the data to our picklist and clear it from the free-text field that it was initially contained in.

Start by selecting all buckets that you would like to standardize and make consistent for your picklist field.

Country

 

Then, we set our function for adding the data to our picklist field and clearing it from its original field.

Move to picklist

 

You can see that the button has been updated to say "Update 568 Contacts" because we have selected 568 total contacts from the variation buckets.

When you click the button, you will be prompted to confirm. When you do, the update will go live in your database. Changes can always be reviewed on the Activity Tracker page.

You'll be provided with a report of the records that were successfully updated.

Succeed report

 

Now we have standardized our US-based contacts to "United States," populated our country picklist field, and cleared the original free-text field that contained the data.

This same process will need to be completed for every value in your custom picklist field. In our example, we would need to do this for each country that was represented in our database.

The Cleanse Data module provides a simple, straightforward way to understand your data, make it consistent, then convert it from a free-text field to a picklist field.

Audit Trail and History

The Activity Tracker lets you review all changes made through Insycle. At any time you can download a CSV report of the operation and records affected.

Customer Data Health Assessment

The Data Health Assessment surfaces data quality issues that negatively impact your marketing, sales, and support efforts, and guides you through the process of fixing them. Here, you can keep an eye out for issues in your data and fix them with a few button clicks.

Additional Resources

Related Blog Articles

Related Help Articles

Additional Resources