How to join Audit table to Entity table using FetchXML in Dynamics 365 Customer Engagement

Question

I would like to add a link to the Entity table in this query:

<fetch version="1.0" >
  <entity name="audit" >
    <all-attributes/>
  </entity>
</fetch>

I have tried to use the ObjectTypeCode attribute but link to the Product table, but there seems to be no corresponding value (ObjectTypeCode for Product is 1024).

Thank you for your help.

Answer 1

Unfortunately, you cannot join the related tables just like that with audit table. Moreover the query may need UNION to join based on objecttypecode with different tables. FetchXML does not support UNION.

The audit table is little tricky, it is not like any other entity - as some of the data is stored in denormalized and delimiter separated values in columns like changedata. Also relationship is not maintained between audit and other tables other than systemuser. Read more

You can play around the audit table using XrmToolBox FetchXML builder and observe them.

Web api endpoint for audit entity is below:

https://crmdev.crm.dynamics.com/api/data/v9.2/audits?$top=50&$select=_objectid_value,attributemask,operation,transactionid,useradditionalinfo,createdon,_userid_value,_regardingobjectid_value,objecttypecode,action,auditid,_callinguserid_value&$filter=objecttypecode eq 'account'

Very interesting part is $filter=objecttypecode eq 'account' in above web api query. I don't know how is this even working. This is contradicting between web api and fetchxml.

Answer 2

objectypecode is the identifier of the entity for that audit record and should be used on your <filter> section. If what you want is to tie and show information about the related entity you need to know where that entity is being tied from. It won't show you before and after values, its just a link to the existing record.

For example:

<link-entity name="product" from="productid" to="objectid" >
        <attribute name="attributenametoshow" />
        <filter>
            <condition attribute="attributenametofilter" operator="" value="" />
         </filter>
</link-entity>

Object id is the lookup to the record that is being audited. For reference

If what you want is to see before and after values for the audit record, you might need to use the RetrieveRecordChangeHistory function from the WebApi