+ Start a Discussion
ChandlerChandler 

How to convert currency using formula

Hi,

 

   I am working on one workflow which sends out some notfication email to user depending on some data from opportunity.

 

   One of the triggering rule is ...the Opportunity Amount > 15000 USD.

   Since the currency in the opportunity varies, it could be USD or maybe not.

   How can i conver the Opportunity Amount in other currency into USD by using formula?...

 

 

Ispita_NavatarIspita_Navatar

Conversion of currency in different denomination to USD can be achieved by enabling multi-currency. Date multicurrency is applicable for for standard objects like opportunity, since your requirement involves opportunity you can make use of multicurrency to achieve your requirement. You can use USD as the organization Currency and the user currency. As all amounts are automatically converted to the currency of the logged in user. When one creates an instance of a record of opportunity you can specify the currency of that record, and salesforce does the conversion internally.

But multicurrency once activated cannot be reverted.

In case multicurrency does not suit you, you can have 3 custom field amount ,denomination and exchange rate, amount gives the quantity of currency and denimonation gives type of currency and exchange rate the conversion rate so the product of amount and exchange rate will serve your purpose--> via a formula field and thus your WFR can be triggered.

 

Did this answer your question? if so, please mark it solved.

 

 

ChandlerChandler

Hi Saha,

 

     Thanks for you patient and detailed explanation.

 

     The system i am working for is already multi-currency enabled and i do see in the webpage for opportunities

     account etc, currency fields are converted automatially. ( generally they will be displayed in both user currency and

     organizational currency )

 

     But the problem i am facing is ..i don't see a convenient way to get the currency converted,

     in the  "Rule criteria" section of the workflow, in "Forumla evaluates to ture" manner.

 

     I need to write a formula that judge Opportunity Amount > 15000 USD, the value in the oppty amt field could be

     USD or EURO JPY ..etc, so i need to convert that amount to USD before i do the comparison with 15000.

     But i can't find any FUNCTIONS that provide the currency conversion function.

 

     Thanks,

     Chandler.

     

yudhvirmoryudhvirmor

Hi Chandler,

 

You raised very valid question. I didn't get time to test it myself. I think system must be intelligent enough to do the conversion and then compare currency fields. However, it has to tested. Salesforce can't afford these type of gaps. We may need to test it by creating validation rule and then creating records in different currency to see if its validating the record. I am also curious to see the outcome.

MissouriAdminMissouriAdmin

Did anyone find an answer for this.  Yes the conversion is automatic if you use the simple form of WOrkflow criteria.  However, if you need an advanced formula, the Compare becomes a direct numerical compare without any conversion.

 

HOW DO YOU PERFORM currency conversion in a "Formula Evalutes to True" form of workflow or Formula Field definition rather than the "criteria are met" form of the rule.

thuskerthusker

We ran into this last year when we were trying to enable a discount approval process--we have multicurrency enabled but found that SFDC does not apparently have the ability to handle conversion rates automatically in validation rules.  It's maddening that the system can't/won't do that, but I won't get into that.

 

Anyway . . . what we found we had to do was to create our validation rules with entries to do the conversions.  Otherwise the validation rules with the other currencies won't work as they should.  So we had to use the following for every field that we needed to validate a dollar limit.  It's a pain because when currency conversion rates change we have to mnually update every validation rule.  Would be nice if SFDC automatically handles conversion within validation rules. 

 

This suggestion came directly from SFDC support last year--so as far as I know this is the only way to do it.  If anyone else has a better way I'd love to hear it.

 

 

OR(
AND( ISPICKVAL (CurrencyIsoCode , "USD"),
ISPICKVAL( Alternative_NST_Type__c , "8k - 8pct"),
Service_Incremental_Discount__c > 8000),
AND( ISPICKVAL (CurrencyIsoCode , "EUR"),
ISPICKVAL( Alternative_NST_Type__c , "8k - 8pct"),
Service_Incremental_Discount__c > 5840),
AND( ISPICKVAL (CurrencyIsoCode , "GBP"),
ISPICKVAL( Alternative_NST_Type__c , "8k - 8pct"),
Service_Incremental_Discount__c > 5160))

 

 

MissouriAdminMissouriAdmin

THanks, this would in general work, however we have 17 currencies active, so this will quickly exceed the number of characters for a formula.

 

Yes, this is madening that this is not directly supported.  We typically use a trigger, access the COnversion table, and perform the conversion ourselves and set a field.  This however required double the currency fields.

 

Hopefully someone else has come up with something or Salesforce recognizes the gap here.

ChandlerChandler

Hi all, glad to see this thread has some new discussions :)

           from my side, this problem is still not resolved in our org...

 

Chandler.

VeevaEmsVeevaEms

Does anyone know if this same issue exists in APEX code and aggregate functions?  I'm running the following aggregate function in  a trigger (simplified for easy reading):

 

SELECT Order__c, SUM(Total__c)sum FROMExpense__cwhere Order__c = "an id here"

 

My code works great in US $, but different currencies seem to get converted improperly along the way somehow....

 

Any ideas how to handle currencies in Aggregate functions?

VasantVasant
We recently encountered this problem i.e. we are comparing the oppty amount across the world against 100K USD. So for e.g. if there is a Indian Opportunity with 150000 INR it qualifies as 150000 > 100000 but 150000 INR is really only 2500 USD so it should not qualify. To overcome this we created custom field where we store the Amount from Opportunity currency converted to USD. The conversion is done through Trigger and it fetches the exchange rate from the conversion table. We would then use this custom field in the Process builder condition instead of the Oppty Amount field. Thanking you.  
Bing Maletz 11Bing Maletz 11
In both SFDC Approval Process and Workflow, you can specify entry criteria as combination of Amount and Currency.

SFDC will automatically convert the Currency Value from Record Currency to the Currency that you specified in Entry criteria using exchange rate from CurrencyType object

Example 2: Workflow Rule => Entry Criteria
The following workflow rule is triggered if Opportunity Amount >USD 10000

Assuming that Exchange Rate (in CurrencyType object) is  1 EUR = 1.1 USD

If you have an Opportunity with  Amount= EUR 9200, then this worklflow will be triggered, because EUR 9200 *1.1 =USD 10,120 .

User-added image
Example 2: Approval Process => Step Criteria

User-added image