How to Override Salesforce Field Definitions

Copystorm creates database columns using metadata provided by Salesforce to determine the proper database column type. Salesforce metadata is usually correct, but in the rare occasion that is is wrong or inconsistent CopyStorm provides a way to override the column type. This article describes how to configure CopyStorm to override the column definition provided by Salesforce (or even ignore a Salesforce column completely).

• If Salesforce says a column is 80 characters long, CopyStorm will create a NVARCHAR(80) column in the target database.
• If Salesforce says a column is a single digit number, CopyStorm will create a DECIMAL(1,0) column in the target database.

Unfortunately, sometimes data provided by Salesforce does not match the column definition. For example, Salesforce claims that multipicklist columns may contain up to 4099 UTF-8 characters. Though this is true in fact, it would be rare for a Salesforce instance to have a value this long. For searching, indexing, and data storage purposes a CopyStorm instance may override Salesforce’s 4099 value with a much smaller data type.

Sometimes Salesforce metadata is wrong when determining column size — for example, an aggregate column that says it is 80 characters wide may have much wider values when table data is queried (CopyStorm deals with this case automatically).

In all cases, Salesforce metadata can be overridden by including an ExtractionManagerConfig.xml configuration file. The configuration file can be located in:

• The configuration directory referenced by the .copyStorm configuration file.
• The directory “config” in the CopyStorm installation directory.

It is also possible to force CopyStorm to completely ignore a field using directives in an ExtractionManagerConfig.xml file. See the end of this article for details.

A configuration like the following will cause all multipicklist type columns in CopyStorm to be 120 characters wide (rather than the Salesforce default of 4099):

The tables being overridden can be specified by including an optional “table” parameter. In the following example the Contact table multipicklist columns will be 80 characters, with other tables using 120 characters:

A configuration file like the following will cause all string formula type columns in CopyStorm to be 255 characters wide (rather than the Salesforce default of 1300):

The table being overridden can be specified using an optional “table” parameter. In the following example the Contact table string formula columns will be 80 characters with other tables being limited to 120 characters:

If Salesforce has a single digit numeric column (e.g. DECIMAL(1)) and an aggregate column SUMs this column, Salesforce will sometimes declare the SUM column with a DECIMAL(1) type. This causes problems when a SUM() is greater than or equal to 10 — without an override CopyStorm will read Salesforce metadata, create a DECIMAL(1) column, and report a “value too large” error when attempting to insert records where the SUM() value is too large.

This configuration will alter the precision of the “AnnualRevenue” column for the “Account” object from DECIMAL(18,0) to DECIMAL(18,3):

This example increases the length of the Account.Name database column from 80 to 120 and the Account.Description column to 200:

The only tricky part of this configuration is that table and column names must precisely match the definitions in Salesforce (names are case sensitive).

There is no limit to the number of FieldOverride directives. New and changed directives will be applied to existing columns in the target database the next time CopyStorm runs.

Sometimes, but rarely, a company desires to completely ignore Salesforce columns and not transfer them or their data into the CopyStorm backup. Most of this time this comes up during a migration where backing up fields associated with an obsolete managed package is desired.

Here are a few examples of how to completely ignore fields.

• The first IgnoreField directive ignores the field Account.MyCustomField__c
• The second IgnoreField directive ignores all columns added by the Salesforce CPQ package. Note that the special table name default means to apply this rule all tables.

Be careful! An IgnoreField directive tells CopyStorm to pretend that a column no longer exists in Salesforce and, by default, when a column no longer exists in Salesforce it will be dropped in the CopyStorm database.

The options for the IgnoreField directive are:

• table — name of the Salesforce table containing the field(s). The special name default means that the rule will apply to all tables.
• name — a regular expression indicating which columns should be ignored. Of course, just a column name also works since this is  simple regular expression.

Note that this feature was added in CopyStorm 9.47.1. Earlier CopyStorm versions will silently ignore IgnoreField directives.