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.
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”
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.
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.
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
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 helped!
Debajit Dutta
Business Solutions MVP
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?
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
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.
Pingback: Record currency in Dynamics CRM | msdynamicslab
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.
Awesome. And thanks for sharing the solution as well. Really appreciate it.