Populate Country, State, or City Based on Other Field Data

Navigate to Data Management > Transform Data, and select the database and record type from the top menu. Then, explore the templates for an existing solution that may be close to what you need.

In Step 1 under the Filter tab, choose fields and criteria that the field values must meet. By using this filter, you tell Insycle which records you want to update.

In the example below, we only want to change records that don't have a Country/Region value, do have an email address, and the email doesn't end with ".com," ".net, ".inc," ".io," or ".co."

Click the Search button, and a preview of the records that match this filter will load in the Record Viewer at the bottom of the page. Take a look through the results to see if there's anything else you'd like to filter out. If you make changes to the filter, click the Search button again to reload the Viewer.

Under Step 2, give Insycle with instructions on what changes to make to the identified records.

To populate the country value, you can use the top-level domain (TLD), which is everything that follows the final dot of a domain name. For example, in the domain name "about.us", ".us" is the TLD. In the domain name, "bbc.co.uk", ".uk" is the TLD. You can look at email addresses or website URLs for the TLD.

Then you convert this abbreviated value to a full country name and copy it into the country field.

1. Select the email, domain, or website URL field from the dropdown.  
2. Select the Split: By any delimiter and pick the last term function. (Learn more about the "Split" functions in the Advanced How-Tos below.)
3. In the Parameter field, enter a "." to use as the delimiter.
4. Click the plus at the end of the row to add an additional function for this field.
5. Now that you've isolated the two-character country code, select the Standardize: Country Code2 to Name function. Click the plus.
6. Select the Copy: Value function and the Target Field the country name should be written into.

Navigate to Data Management > Transform Data, and select the database and record type from the top menu. Then, explore the templates for an existing solution that may be close to what you need.

In Step 1 under the Filter tab, choose fields and criteria the field values must meet. 

In the example below, we want to update records that have a phone number but are missing state information. We're filtering for records that have a phone number value but have a blank state/region field.

Click the Search button, and a preview of the records that match this filter will load in the Record Viewer at the bottom of the page. Review the results to ensure you're targeting the right records. If you make changes to the filter, click the Search button again to reload the Viewer.

Under Step 2, give Insycle instructions on what changes to make to the identified records.

For records with phone numbers but missing state information, you can extract the area code and use it to populate the state field.

• Select the Phone Number field from the dropdown.
• Select the Remove: Non-digits function to clean the phone number and remove any formatting characters.
• Select the Remove: Symbols function to ensure all non-numeric characters are stripped from the phone number.
• Select the Extract: Substring function and set the parameters to extract characters 1 through 3 (the area code portion of the cleaned phone number).
• Select the Map: US Phone Area Code to US State (abbrev.) function to convert the extracted area code to the corresponding state abbreviation.
• Select State/Region as the Target Field where the state abbreviation should be written.

In Step 1 under the Filter tab, choose fields and criteria that the field values must meet. With this filter, you're specifying which records you would like to update in Insycle.

In the example below, we want to update records that have a postal code but are missing city information. We're filtering for records that have a postal code value but have a blank city field.

Click the Search button, and a preview of the records that match this filter will load in the Record Viewer at the bottom of the page. Review the results to ensure you're targeting the right records. If you make changes to the filter, click the Search button again to reload the Viewer.

Under Step 2, you provide Insycle with instructions on the changes to make to the identified records.

For records with postal codes but missing city information, you can use the postal code to automatically populate the city field.

• Select the Postal Code field from the dropdown.
• Select the Map: US Zip Postal Code to US City function to convert the postal code to the corresponding city name.
• Select City as the Target Field where the city name should be written.

Preview Changes in the CSV Report

With the filters and functions set up, you can preview the changes in a CSV file. It's important to verify that your changes are working as expected before they are pushed to your live database.

Under Step 3, click the Review button, then select Preview in the popup.

On the Notify tab, you can select recipients for the email report and add additional context to the message. (Make sure to hit Enter after each email address.)

On the When tab, click Run Now and select which records to apply the change to (you can select All, but if you have a large number of records, it may be best to preview a subset first). Then, click the Run Now button.

Insycle will generate a preview CSV and send it to your email. Open the CSV file from your email in a spreadsheet application and review the (After) values for your updated field. 

If your results don't look the way you expected, go back to your filters in Step 1 and functions in Step 2, and try making some adjustments, then preview again.

If you discover that some of the two-character countries from the email domains didn't map to the full country names, see the Troubleshooting section below for a solution.

Apply Changes to the CRM

If everything in your CSV preview appears correct, return to Insycle and proceed with applying the changes to the live CRM data.

Under Step 3, click the Review button again, this time select Update mode.

On the When tab, you should use Run Now the first time you apply these changes to the CRM. If you have a large number of records, you might want to process a smaller batch to review the results in your CRM.

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 to regularly add the missing values. By automating with a template, you'll save time and ensure that your fields are consistently updated on an ongoing basis. If you have several templates you'd like to automatically run together, you can create a Recipe. Additionally, HubSpot users can incorporate Insycle Recipes into HubSpot Workflows, while Salesforce users can integrate Recipes into Salesforce Flows.

Return to the Template menu at the top of the page and click Copy to save your configurations as a new version of the template you started with. Then click the pencil to edit your new template name.

Under Step 3, click the Review button, and select Update mode.

On the Notify tab, select the send option appropriate for your automation: Always send, Send when errors, or Do not email.

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, select Automate, and configure the frequency you'd like the template to run. When finished, click Schedule.

The Split functions are used to separate the values or characters in a field.

For example, phone numbers can come in various formats, and you may need to extract the area code before using the mapping function. Here are common scenarios:

Then, after extracting the area code, use the Map: US Phone Area Code to US State (abbrev.) function to convert it to the state abbreviation.

For a complete list of Transform functions with more information, visit the Function Catalog.

If you'd like to see additional fields in the Record Viewer or your export, click the Layout tab under Step 1, find the desired field in the right column, and drag it left to the Visible Fields column. Drag and drop fields to reorder them in the Visible Fields column. 

Note that adding additional fields to the Record Viewer does not affect the contents of the Step 3 review CSV report; it only affects the Record Viewer and export from Step 1.

In the preview CSV, you may see that most of the country data from the domains are being transformed to full country names successfully, but there are a couple of outliers. In this example, the "uk" value from the "nike.co.uk" email domain is copied to the Country/Region field as is. This is because some countries' top-level domains don't align with the ISO two-letter standard Insycle uses for the "Standardize Country Code2 to Name" function. In this case, the top-level domain is "uk", but the ISO code is "gb".

To address these cases, you can add a "Map" function under Step 2 that specifically instructs Insycle to replace all instances of "uk" with "gb" before proceeding to the next step. Make sure you insert the additional function before the "Standardize Country Code2 to Name" and "Copy" functions.

When you re-run your preview, you should now see the correct country name value in the (After) column.

The Transform Data preview CSV includes a fixed set of fields: record identifiers, a deeplink to the record in the CRM, and the Before and After values for the transformed fields. Other fields cannot be added.

To see all the fields that are in your Layout and Record Viewer, use the Export button under Step 1. This will export the current data without the Transform updates. For more details, see the Advanced How-Tos above.

Yes, with the Transform Data module, you can save your field population settings as a template and schedule automation. You can also bundle templates together and automate them using Recipes.

Refer to the Save Templates and Setup Automation section above for more information.

Filtering data before exporting ensures that Insycle only analyzes the records you want to focus on. Otherwise, Insycle will look at all records of the selected record type in your CRM. 

There can be a few reasons filtering is important:

1. You only want to make changes to a segment of records that meet certain criteria. 
2. If you have thousands of records, the operations can take a considerable amount of time. It is more efficient and manageable to filter your data down into small segments and use multiple templates rather than running operations for your entire database.
3. There may be an overwhelming number of results. If you are trying to analyze your data, adding a filter or narrowing filter criteria lets you work with a relevant, reasonably sized subset of data.