How to Update a Field Based on the Value in Another Field
You need to make bulk updates to a field based on values that have been entered in another field. For example, if the State/Region value in a record is "Quebec," then set the Country field value to "Canada."
Insycle makes it easy to identify records that have a specific field value and update them based on your defined conditions. For straightforward, "If value=x, set it to=y," tasks, use the Bulk Operations module. For more complex tasks where you need to do multiple conditional bulk updates all in one template, the Transform Data module would be more appropriate.
Simple Conditional Updates with the Bulk Operations Module
In this section, we'll walk through a simple example of updating a contact's Country/Region field, based on the US State.
Process Summary
- Filter your records down to those that you would like to update.
- Define how to change the filtered records.
- Preview your template, then update the data in your CRM.
Navigate to Data Management > Bulk Operations.
Select the database and record type. Then explore the templates for an existing solution that may be close to what you need.
Under Step 1, set up a Filter to narrow the records down to only those you would like to update.
In this example, the filter is looking for contacts with a State/Region value of "New York."
When you click Search, Insycle will list matching records in the Record Viewer at the bottom of the page. These are the records that the changes you specify will be made to.
Add columns to the view from the Layout tab under Step 1.
If you make changes to the filter, click the Search button again to refresh the results in the Record Viewer.
Now you'll tell Insycle how to update data based on the condition you just set.
Under Step 2, make sure you are working in the Update tab. When the Update action is applied, previously existing data will be removed from the field.
This change will be made to all records that match your Step 1 Filter– in this example, contacts with a State/Region value of "New York."
Select the Field Name that should be updated, and enter the New Value.
Preview Changes in a CSV Report
Now with the filters and update actions in place, you can preview the changes. It's important to verify that your fields are being updated as expected before those changes are pushed to your live database.
Under Step 3, click the Review button, then in the Bulk Update popup, select Preview and click Next.
On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). You can also provide additional context in the message subject or body.
On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.
Open the CSV file from your email in a spreadsheet application and review the columns. For each field you’ve set up to change, a column will appear for both (Before) and (After). This will let you know what you started with and what change will be made to your CRM.
Apply Changes to Your CRM
If everything in your CSV preview looks correct, return to Insycle and move forward with applying the changes to the live CRM data.
Under Step 3, click the Review button, this time select Update mode.
On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.
Save a Template and Setup Automation to Maintain Formatting
After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save your configuration as a template, and set up automation so this formatting operation runs on a set schedule.
You can create templates for as many states or regions as needed, then create a Recipe to run them all together.
Using templates and automation, you'll save time and ensure that your fields are consistently updated on an ongoing basis.
Complex Conditional Updates with the Transform Data Module
This section looks at a more complex example of syncing record owners across CRMs.
Let's say your team needs to sync Pipedrive organization owners with HubSpot company owners. Though you have the applications integrated, the fields don't natively sync, and the owner isn't associated properly in both systems. Additionally, these fields are storing the owner ID, not the actual name of the owner.
The Transform Data module makes it easy to handle this using three functions—Map, Map Default, and Copy.
Here is an example of mapping the ID numbers so that if they are one value in Pipedrive, it will be changed in HubSpot:
The way to read it is the following:
- If "My Custom Field" has a value 123, set the value 456 in the "Contact Owner" field << 123 is the Pipedrive owner ID, 456 is the matching HubSpot owner ID
- If "My Custom Field" has value 234 or 345, set the value 567 in the "Contact Owner" field << 234 and 345 are the Pipedrive owner ID, 567 is the matching HubSpot owner ID. You can optionally map two Pipedrive owners to one HubSpot owner.
- If "My Custom" has no value, that is, no owner, set the "Contact Owner" to a default HubSpot contact ID.
To clarify, in this template the field "My Custom" is not modified, it's used as an input for the mapping. The output of the mapping is set on the "Contact Owner" field.
Tips for Bulk Updating Values
If you aren't sure what values you have across your database, you should do a little exploratory work. Use the Cleanse Data module to get a summary of all the fields and the data they contain in your database. This makes it easy to review the values that are in use and decide how to set up your conditions.
Learn more about using the Cleanse Data module.
Frequently Asked Questions
You can update any field in your CRM that is writable using the Bulk Operations module. To see a full list of writable fields, use the Cleanse Data module.
Yes, the pre-existing data in the record will be overwritten by the bulk update. If you need this data for reference or restoration, it will be available in the CSV in the Activity Tracker.
All pre-existing data will be available in the CSV in the Activity Tracker for restoration purposes should you require it.
Additional Resources
Related Help Articles
- Module Overview: Bulk Operations
- Module Overview: Transform Data
- Bulk Update Values of Any Field
- Bulk Clear Values from a Field
- Format Names, Phone Numbers, Addresses
Related Blog Articles