Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sudhirn@merunetworks.comsudhirn@merunetworks.com 

Convert amount into USD

In Opportunity we have amount field with multi-currencies such as USD, EUR, JPY, etc have created a custom field called usd_amount__c I need to store this amount in USD please suggest me how to convert this multicurrecny value in this custom field.

Thanks
Sudhir
Best Answer chosen by sudhirn@merunetworks.com
srlawr uksrlawr uk
Unfortunately you can't access multicurrency values in formula fields (and thus workflow evaluations) otherwise this would be a piece of cake. Can I suggest you "vote" on this idea here to encourage salesforce to make this possible:

https://success.salesforce.com/ideaView?id=08730000000Br1GAAS

we all need this in our lives.

To solve your problem then, I would probably suggest a simple trigger (sorry for the code solution, I know it means you have to write tests)

  
trigger populateUSD on Opportunity (before insert, before update) {

    // Get USD exchange rate from multicurrency table
    Decimal cts = [SELECT Id, IsoCode, ConversionRate FROM CurrencyType WHERE IsoCode = 'USD' LIMIT 1];

    for(Opportunity thisOpp : trigger.new) {
        if(thisOpp.Amount != null && thisOpp.usd_amount__c == null) {
            thisOpp.usd_amount__c = thisOpp.Amount * cts;
        }
    }

}

of course, I have set this up so it ONLY sets the USD value the FIRST time an amount exists in the opportunity (see the if criteria) you might want to play a little harder with when the conversion is done.



 

All Answers

srlawr uksrlawr uk
Unfortunately you can't access multicurrency values in formula fields (and thus workflow evaluations) otherwise this would be a piece of cake. Can I suggest you "vote" on this idea here to encourage salesforce to make this possible:

https://success.salesforce.com/ideaView?id=08730000000Br1GAAS

we all need this in our lives.

To solve your problem then, I would probably suggest a simple trigger (sorry for the code solution, I know it means you have to write tests)

  
trigger populateUSD on Opportunity (before insert, before update) {

    // Get USD exchange rate from multicurrency table
    Decimal cts = [SELECT Id, IsoCode, ConversionRate FROM CurrencyType WHERE IsoCode = 'USD' LIMIT 1];

    for(Opportunity thisOpp : trigger.new) {
        if(thisOpp.Amount != null && thisOpp.usd_amount__c == null) {
            thisOpp.usd_amount__c = thisOpp.Amount * cts;
        }
    }

}

of course, I have set this up so it ONLY sets the USD value the FIRST time an amount exists in the opportunity (see the if criteria) you might want to play a little harder with when the conversion is done.



 
This was selected as the best answer
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thanks for your reply I am getting Error: Compile Error: Illegal assignment from List<CurrencyType> to Decimal
 
sudhirn@merunetworks.comsudhirn@merunetworks.com
I wrote a cutom trigger with the same logic for some reasons decimal values are not comming proerly can you please suggest what might be the issue here 
  
trigger convertToUSD on Opportunity (before insert, before update){
     
    
    List<CurrencyType> currencyTpyeList = [select id,IsoCode,ConversionRate from CurrencyType where isActive = true] ;
    
    
    Map<String , Decimal> isoWithRateMap = new Map<String, Decimal>();
    for(CurrencyType c : currencyTpyeList) {
        isoWithRateMap.put(c.IsoCode , c.ConversionRate) ;
    }
    
    for(Opportunity opp : trigger.new){
        if(opp.CurrencyIsoCode != 'USD' && isoWithRateMap.containsKey(opp.CurrencyIsoCode)){
        
            if(opp.usd_amount__c != null)
            {
            opp.usd_amount__c = opp.amount / isoWithRateMap.get(opp.CurrencyIsoCode);
            }
            if (opp.Annual_Contract_Value__c != null ) 
            {
            opp.usd_acv_c__c = opp.Annual_Contract_Value__c / isoWithRateMap.get(opp.CurrencyIsoCode);
            }
        }
    }
    
 
}

Thanks
Sudhir
srlawr uksrlawr uk
ah bollocks. in my example, change line 04 to 
Decimal cts = [SELECT Id, IsoCode, ConversionRate FROM CurrencyType WHERE IsoCode = 'USD'LIMIT 1].ConversionRate;

 
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thanks for you reply 
 
Example if ERU exchange rate is 0.896861 and amount 2  according to your trigger it shows 2 but it should be 2.19 can you please help me to validate 

Thanks
Sudhir
srlawr uksrlawr uk
how many decimal places have you set on your number field?
User-added image
sudhirn@merunetworks.comsudhirn@merunetworks.com
It is two decimal places
 
Sourabh KhoslaSourabh Khosla

Hi Guys, 

I need a little help . I am almost there ... but need a little hand-holding.  Agenda is to ALWAYS display the value converted in EUR - does not matter what the record currency is. 

I am using this code below: 

trigger convertToEuro on CustomObject(before update) {

List<CurrencyType> currencyTypeList = [select id,IsoCode,ConversionRate from CurrencyType where isActive = true] ;

    Map<String , Decimal> isoWithRateMap = new Map<String, Decimal>();

    for(CurrencyType c : currencyTypeList) {

        isoWithRateMap.put(c.IsoCode , c.ConversionRate) ;

    }

     

    for(CustomObject ce: trigger.new){

        if(ce.CurrencyIsoCode != 'EUR' && isoWithRateMap.containsKey(ce.CurrencyIsoCode)){

            ce.Amount_Converted__c = ce.ffps_iv__Amount__c/ isoWithRateMap.get(ce.CurrencyIsoCode);

        }

    }
 


It is doing everything fine except the fact that the Amount_Converted__c field's values are displayed as USD (& EUR) but EUR seems like display only value - the USD is what the Amount_Converted__c field is holding. I want to use Amount_Converted__c  converted in EUR in my workflow but instead I am getting the USD value - how could I just get the EUR value? I don't really need the Record Currency type - I need my EUR currency to be used in my workflows/approvals. 



User-added image


Please help. 

 
Garrett Southworth 1Garrett Southworth 1
In Spring 18, they released a new formula function CURRENCYRATE. I needed to levelset all opportunities, meaning have them all reportable in USD.  Here is my formula
  
IF( 
TEXT(CurrencyIsoCode) = 'USD', Amount, 
Amount / CURRENCYRATE(TEXT(CurrencyIsoCode)))

To be clear, this is not for advanced multi-currency. That adds some complexity but is still doable. Rather than referencing the CurrencyIsoCode in the third line directly, you'd reference a new hidden number field (we'll call it Exchange_Rate__c) that is updated by a process builder. The process builder would update the Exchange_Rate__c field in certain situations (on creation, closed won, etc) and lookup the exchange rate at that time, update Exchange_Rate__c and your formula would look like this
  
IF( 
TEXT(CurrencyIsoCode) = 'USD', Amount, 
Amount / Exchange_Rate__c)