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.

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.

The extraPrecision attribute overrides how many additional precision and scale digits CopyStorm adds to a numeric field when Strict Decimal Mode is NOT enabled.

Example: If a field override such as the below override is provided, the resulting column definition in the database would be “AccountRevenue__c decimal(22,6)” — 18 + 4 precision, 2 + 4 scale.

Important Notes:

• If extraPrecision is defined for a field, it overrides the system-wide Extra Scale setting for the specified field.
• When using extraPrecision, the total precision for a field must NOT exceed the database limit of 38.
• extraPrecision is ignored when Strict Decimal Mode is enabled.

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 configuration will alter the custom decimal field “MonthlyRevenue__c” column for the “Account” object to DECIMAL(24, 4):

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

• Use digits for fields that store whole numbers (integers). This maps closely to how Salesforce defines numeric length for non-decimal types.
• Use precision for decimal or floating-point fields. This ensures the appropriate structure is generated in the target database.

There is different behavior for the creation of numeric fields in the CopyStorm database depending on if the Use Strict DECIMALs configuration paramter is utlized. For more information on this option, see here.

• Strict Decimal Mode: CopyStorm will create or update numeric fields in the database using the exact precision and scale as provided by Salesforce.
• Non-Strict Decimal Mode: CopyStorm will create or update numeric fields in the database with additional precision as denoted by the Extra Precision field on the Database Schema section of the Configuration tab (By default this value is set to 2).

The behavior of precision and scale can vary depending on the target database. Here is a quick breakdown:

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.