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.
- 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.
Example Problem 1: Multipicklist Columns Are Too Wide
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:
Example Problem 2: String Formulas Are Too Wide
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:
Example Problem 3: Salesforce Lies
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.
Fixing Salesforce Metadata Inconsistencies
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.
|Option Name||Value Type||Description|
|table||string||Salesforce table to which the override applies. This option is case sensitive.|
|column||string||Salesforce column to which the override applies. This option is case sensitive.|
|length||integer||Number of characters for a string column. This option only applies to string type columns.|
|digits||integer||Total number of digits for a numeric column.|
|scale||integer||Number of digits after the decimal point for a numeric column|