Syntax Highlighter JS

Monday, March 5, 2012

The case of the missing zero's - Part 1

EDIT: I now recommend the Jitterbit Data Loader which can be found here, over the salesforce data loader. At the time of this edit, Jitterbit had a free version.

So is fast but how do they do it?  Of course they are using big iron in their data centers but they have many tricks built into their software to increase speed and/or decrease size.

One of these tricks is to strip 'extra' zeros from your numbers.  This violates data integrity but they put the extra zero's back in the web GUI.

Don't believe me?  Lets walk thru an example.  I have created an object called Item__c and on that object I have created the following fields:
Notice how each field is named for its type and each one is asking for 2 digits after the decimal.

In the web GUI, we enter 1.00 for currency, 2.00 for number and 30.00 for percent.

Everything looks great right?  Now try to export that data.  I dare you.  If you do an SOQL query in explorer you get this:

Savvy explorer users are now saying something along the lines of "explorer is just dropping the insignificant data".   Right you are, so lets look in Eclipse at the same query.
In the database itself, is dropping the 2nd zero after the decimal (when in 16/2 format and ending in double zero).  Salesforce has hidden this by having the web GUI and reports in your browser (not in exports to excel) by reformatting and adding the zero back.

They didn't however fix this in the data loader (as of version 23 anyway).  This can be a major problem if you are wanting to integrate a system with Salesforce using the Apex dataloader and the other system chokes on files that don't match the defined scale setting

In part 2, I will look at how to create your own customized version of the dataloader to put this zero back using regular expressions.

No comments:

Post a Comment