0

We extract data from Salesforce (Source) and load into Oracle (Target) Datamart using Informatica Cloud data Integration.

Few columns with datatype as Formula(Currency) & Formula(Percent) on Salesforce return the high precision than what is expected. We couldn't fix the downstream application's data type & precision due to the above undetermined precision size of output data from these columns from salesforce.

1) How a return type and PRECISION SIZE of Formula columns (Currency & Percent) can be DETERMINED?

2) Since these are mathematical accumulated columns, will precision DEPEND on any of the operand columns? If so what will be the column (operand column) based on which the return precision can be determined?

3) The metadata for these formula columns shows the precision as (18,2) on salesforce but we receive data of around precision 20. Due to undetermined precision from source, we couldn't fix the data type & precision on Oracle environment.

Note: Datatype & precision for downstream system is defined as per the SFDC metadata as of now as number(18,2).

Our company Salesforce developers team says "Since these are formula & custom fields, we do not control over return precision size".

ANY SUGGESTIONS TO CONTROL THE RETURN PRECISION ON SALESFORCE (OR) INDICATIONS TO ESTIMATE THE MAXIMUM RETURN PRECISION FROM THESE FIELDS WOULD HELP US.

2 Answers 2

0

You just round them before load in your ETL process. You also have to watch (enforce rounding) for any numeric/currency field as Salesforce precision is restricted in UI only and data loaded with data loads or after apex calculation can be way off from how the number of decimals predefined in datatype. And this could cause error in your target database.

2
  • From the discussion, I understand that the precision after apex calculation cannot be controlled by defining data type & size. One quick question. Since Salesforce limits formula field upto 18 digits, doesn't round off happens on Salesforce. Apr 18, 2017 at 17:54
  • Formula field's precision is used to display it on the page or in the report - if you are pulling the data with some ETL tools I don't think it's enforced
    – o-lexi
    Apr 18, 2017 at 17:58
0

Return values will be as precise as they need to be. I don't think there's any internal limit we could devise besides "really really precise." The precision defined in the metadata only applies to the UI. The return values are likely at least as precise as Decimal, which allows an insane amount of precision. If you need rounding, use the FLOOR, CEILING, or ROUND formula functions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .