Today’s blog will cover a specific error that you may receive when trying to make changes to a calculated field that affects CRM 2016 on-premises environments.
When you have an existing calculated field and you want to make changes, you begin by editing the field, and then clicking the edit button by the calculated field type:
And then you make a change, try to click the save button but instead receive this error message:
“The [SCHEMA NAME] field has an invalid XAML formula definition.”
Yikes! That sounds scary. But think of XAML as the definition behind the scenes of how that calculated field is designed. So while this error points out there is a problem with our field, it doesn’t tell us what specifically is wrong. So we tried it again, but this time doing a platform trace (https://support.microsoft.com/en-us/help/907490/how-to-enable-tracing-in-microsoft-dynamics-crm)
Within the trace file, we noticed the following error message for our field. In your case, you can replace [SCHEMA] with the field schema name of your calculated field:
System.Data.SqlClient.SqlException (0x80131904): The index ‘custom_[SCHEMA]’ is dependent on column ‘[SCHEMA]’.
ALTER TABLE DROP COLUMN [SCHEMA] failed because one or more objects access this column.
What this means it that as part of the process of altering the calculation, one of the background steps involves dropping a column, which is failing because of an index.
In order to successfully save the change, you will need to drop the index to make the change, and then re-create it.
Making direct SQL updates is an unsupported method. And while we have tested and verified the changes work in our environment, we always recommend proceeding with caution when making a change like this, such always taking a full database backup prior, and applying the changes to a non-production environment and testing it out fully as a first step.
1. First, you will need to have full access to the CRM database. Expand your CRM database, and expand the table of your entity that contains the calculated field. Then expand indexes, and locate the index mentioned in the error message contained in the trace file. It will contain the name of your calculated field:
2. Right-click on it, and select Script Index as -> Create To -> New Query Editor Window. This will allow us to quickly recreate the index afterwards. Leave this window in the background for now.
3. Now right click the index, and select delete, and press OK on the next screen.
4. With the index gone, you can now successfully save your calculated field changes. So go back to your calculated field, make the change you desire, and the save it.
5. Now you can re-create your index by running the query that was created in step 2.
There you have it! We hope this helps. For more Dynamics 365 tips and tricks, be sure to subscribe to our blog!
Happy Dynamics 365’ing!