locked
Audit History - The specified field does not exist in Microsoft Dynamics CRM RRS feed

  • Question

  • Hi

    I have deleted several fields from the Opportunity entitiy that are no longer required.

    Now, when I open particular Opportunities, I get the error "The specified field does not exist in Microsoft Dynamics CRM".  It would appear that the deleted fields are referenced in Audit History, hence the error. 

    The deleted fields display name includes the text "deprecated".  I would like to use this in SQL to locate and delete the entries in the AuditBase that reference the deleted fields. 

    I know the "AttributeMask" column is the same as "ColumnNumber" found in metadataschema.attributes table but I do not know how to get this using the attribute display name. 


    Marc Collins www.QGate.co.uk

    Wednesday, June 5, 2013 4:59 PM

Answers

  • It would appear to be only affecting certain Opportunities.  In most cases, Audit history would display deleted fields as "deleted field" which is correct.  I can only assume that we have 1 or 2 troublesome fields that Audit History doesn't like.

    Anyway, here is how I fixed it.

    The metadataschema.localizedLabel table contains all the attributes with their display name so I was able to filter on "WHERE Label like '%deprecated%'".  In took these results in combination with metedataschema.attribute to give me the Column Number for each field that has a display name like Depricated.

    SELECT l.label, a.columnnumber 
    FROM MetadataSchema.Attribute as a join MetadataSchema.LocalizedLabel as l on a.AttributeId = l.objectid 
    WHERE (l.Label like '%deprecated%' or l.Label like '%deprecataed%') and a.EntityId ='30B0CD7E-0081-42E1-9A48-688442277FAE'

    I used ColumnNumber and manually (with the help of excel) created the delete statement for Audit History like this

    DELETE FROM AuditBase where 
    attributemask like '%,10062,%' or
    attributemask like etc. 
    Im sure there are other ways to do this but this work for me.

    Marc Collins www.QGate.co.uk

    Friday, June 7, 2013 1:43 PM

All replies

  • When does the error "the specified field.." appear? Is it when a user opens an opportunity record or examines the audit history?

    Thursday, June 6, 2013 5:40 AM
    Moderator
  • I see the error when I open Audit History.  I have confirmed that the deleted fields are referenced in the AuditBase table.  I intend to identifiy these AuditBase rows in the "Before" database and then delete them from the "after" database.

    Unless there is a better way.


    Marc Collins www.QGate.co.uk

    Thursday, June 6, 2013 8:40 AM
  • Hmm, sounds like a bug to me. The audit system should cope with attributes being deleted.

    Tread carefully when you mess with the base tables.

    Thursday, June 6, 2013 7:34 PM
    Moderator
  • It would appear to be only affecting certain Opportunities.  In most cases, Audit history would display deleted fields as "deleted field" which is correct.  I can only assume that we have 1 or 2 troublesome fields that Audit History doesn't like.

    Anyway, here is how I fixed it.

    The metadataschema.localizedLabel table contains all the attributes with their display name so I was able to filter on "WHERE Label like '%deprecated%'".  In took these results in combination with metedataschema.attribute to give me the Column Number for each field that has a display name like Depricated.

    SELECT l.label, a.columnnumber 
    FROM MetadataSchema.Attribute as a join MetadataSchema.LocalizedLabel as l on a.AttributeId = l.objectid 
    WHERE (l.Label like '%deprecated%' or l.Label like '%deprecataed%') and a.EntityId ='30B0CD7E-0081-42E1-9A48-688442277FAE'

    I used ColumnNumber and manually (with the help of excel) created the delete statement for Audit History like this

    DELETE FROM AuditBase where 
    attributemask like '%,10062,%' or
    attributemask like etc. 
    Im sure there are other ways to do this but this work for me.

    Marc Collins www.QGate.co.uk

    Friday, June 7, 2013 1:43 PM