Sage CRM offers Multi Currency features. This allows, for example, a Sales user in the UK to quote in Sterling for their British Customers and to Quote in Euros for their German customers. The Sales Manager in Ireland can then see both quotes expressed in Euros or a financial controller in the US could run a forecast report in US dollars.

Sage CRM offers a field type called 'Currency'. These currency fields allow you to specify a value and a currency ID. They are actually held as two separate fields in the database. It is important to release that if you have quoted and stored a value in a currency that value will remain the same on the record regardless of exchange rate changes. If a customer is quoted USD 100,000 for a project, it will remain USD 100,000. This is the value stored in the database.

However, a user reporting on the data in another currency may see a change in the project value, if the exchange rates have been changed since the last time the report was run.

The converted values of currency fields are calculated by triangulation. The monetary value entered by the user is divided by the currency specified to get the value in the base currency, then multiplied by the exchange rate specified for the user's preferred currency.

The calculation uses the rates that are stored in the system and is done on demand using those stored rates.

These rates are controlled in the Administration screens under

Administration -> Data Management -> Currency

If the Multi-currency features are being used then some form of process for maintaining the currency rates against the base currency will need to be decided. How often will the stored rates be changed, and by whom? For some organisations, a quarterly update may be sufficient. Others may decide on daily or weekly.

But!

This can be a pain because when you report on Sales made last year the exchange rate used would be the current exchange rate at the time of the report not the historic rate at the time of the sale. Sage CRM does not take a snap shot of the alternative currency value. It will report the actual value in say Euros the goods were sold in, but if you are reporting in Dollars you could get a very different picture of the value of the items sold.

What if we want to allow a US user report on Sales in Euros with confidence that the values seen were the value in Dollars of the goods at that time?

To do this we would need to store the a currency conversion at a particular point in time. An ideal time would be when the quote is raised.

So how can we do this?

This is one way...

What I did was

1) Add a new field quot_custom_CID (of type integer) for the currency
2) Add another field quot_custom (of type number) for the value
3) I then created a table level script called 'customquote' and added the following in an updaterecord event function

function UpdateRecord()
{
// Handle update record actions here
var numFromValue = Values("quot_grossamt");
var numFromCurr = Values("quot_grossamt_CID");
if (numFromValue >0 & & numFromCurr >0)
{
var numToCurr = 1; //curr_currencyid in currency table
Values("quot_custom_cid") = numToCurr;
Values("quot_custom") = CRM.ConvertValue(numFromValue, numFromCurr,numToCurr)*1;
}
}

I then tested and checked that as the record is updated the currency value is stored as a 'snap shot'. This script uses the CRM.ConvertValue() function. You can change the script to use a PostInsertRecord event function.

The principle is the same if you want store the value in the Opportunity record.

You could also use the idea in workflow using the Set Column Value workflow action with Hidden fields, setting the value using the create script and the DefaultValue property.

The above scenario will work best if you have a single reporting currency rather than having a range of reporting currencies.