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!

Advertisements

Author: Debajit

I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

6 thoughts on “Understanding currency field in Microsoft Dynamics CRM”

  1. I recently faced an issue due to which a roll up field (of currency type) in my custom entity was not working and giving errors for the records which were pre-existing before I created the roll up field. It turned out that the entity had no currency field prior to this roll up field, and so there was no “transactioncurrencyid” field in the entity. I could see the roll up field working fine for the new records. This makes me assume that while creating an entity, even if there is no immediate requirement of any currency field, we must add one dummy currency field in the entity so the entity gets its “transactioncurrencyid” field, and later on we can delete that dummy field. So in future if there is a requirement of any rollup currency field in the table, it can work for pre-existing records as well. What is your opinion here?

    1. Hi Nirman,

      Transactioncurrencyid field gets created after the first currency field you create. For the pre-existing records even when you defined the custom currency field then the transactioncurrencyid field for them was created. However for pre-existing records those field value would be blank!

      I would like to know your exact implementation seems it sounds interesting

      Regards
      Debajit

      1. Hi Debajit,

        The exact implementation somewhat similar to this – There are two entitities. Member and Expenses. Member has 1:N relation with expenses. Expenses entity has an Amount field. There were already some records existing in the Member and Expenses entities when I created a rollup field in Expenses entity to calculate and show the Total Expense done by each member. But as this was the first currency field in Member entity, the rollup field was not working for pre-existing records, as there was no “TransactionCurrencyId” field. So I had to clear all records from both these entities. I had no issues in doing so, as this isn’t a production instance for me. Next time, I removed rollup field from Member entity, and recreated the rollup field after adding some records in both these entities, and this time, I could see the Rollup field working properly for all records (i.e., new and old both). So based on my observation, I believe if there exist a “TransactionCurrencyId” field, it holds currency irrespective to whether the entity contains any currency field or not.

  2. Thank you for pointing me towards a solution. My issue was slightly different and I wanted to share for others to find.

    I recently ran into a similar error when adding a rollup field for invoices to account. I was receiving “Provide a currency to calculate rollup value.” error on form and underlying records had a state value of 7. After some digging in the underlying stored procedures for this new field I found the section of code that was setting the state to 7 and prompting the error. It was located in the p_Rollup_Inc_ce30dce83603e61180c7005056090229 procedure.

    — Checking for Exchange Rate Is Null or Not
    SELECT @exchRate = [ExchangeRate] FROM [Account] WHERE [AccountId]=@id
    IF (@exchRate IS NULL ) BEGIN

    IF (@calculateNow = 1)
    BEGIN
    RAISERROR(‘-2147164122’,11,0)
    END
    SET @result = NULL
    SET @state = 7 –CurrencyFieldMissing
    END

    The records were missing the ExchangeRate value even though the TransactionCurrencyId column was populated. I updated the ExchangeRate column to the correct value and the field calculates correctly now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s