Understanding currency field in Microsoft Dynamics CRM

The currency field of CRM is something that we use in almost every project. However I find that there is always some confusion related to this currency field because of the way CRM handles it.

Let us see exactly what happens when you create a currency field in CRM.

I have a custom entity called “Test Entity 2”. I have just created this entity and till now I haven’t created any custom field of the entity. If you check the screenshot below, you would find that as of now the entity has 19 fields.

image

Now I create a new  currency field called “Currency field 1”. As soon as I create this, the system creates two additional fields one to hold the base currency value and the other to hold the currency value that we are using for our application. This is perhaps we all know. Look for the screenshot below. the name of the fields are “crmtr_currencyfield1” and “crmtr_currencyfield1_base”

image

Great!. So far so good. But look carefully at the bottom of the screenshot. From 19 field suddenly the count has increased to 23 fields. Oops. Are we missing something then?

Ok lets look carefully.

When we create a currency field for the first time, then additional two fields also gets created. One is “exchangerate” which is of type “Decimal”. And the other is transactioncurrencyid lookup field. Check for the screenshot below. Please note that these two additional fields are created only for the first time when a currency field in created for the entity. If we add another field, then from the next time onwards only two fields are created.

image

Now let’s see what these fields are storing. I create a record for Test Entity 2 with Currency Field 1 value of $20. Note my base currency and application currency are same.

image 

lets look at the database and check for the values in the fields that we are interested in.

Look at the screenshot below which gives the query and results

image

The TransactionCurrencyId refers to the currency that is used as the default currency for the application which is stored in the TransactionCurrencyBase Table.

 

Hope this helps!