Ribbon Customizations (Show Schema Names, Copy Guid to Clipboard)

When we first create new field names in crm we try to name them so they make sense to our users, but as time goes on a number of fields get classified by other names. This makes it hard to get the field names on forms without opening the form and finding it there. On larger forms this is a time consuming way to get the fieldname. Adding a ribbon button with some custom js fixes this. The js grabs all attributes on the form and replaces their label with the schemaname.


function SetAllLabelsToSchema() {
   try {
      var attributes = Xrm.Page.data.entity.attributes.get();
      for (var ix = 0; ix < attributes.length; ix++) {
         var attribute = attributes[ix];
         var FieldName = attribute.getName();
         if (Xrm.Page.getControl(FieldName) !== null) {
            Xrm.Page.getControl(FieldName).setLabel(FieldName);
         }
      }
   } catch (ex) {
      //this is a non critical failure.
   }
}


Another must have is a way to get the current records Id. In 2011 this was still easy as the Id was in the url. With the later updates you need to send a link in an email to steal the guid. The below js uses some DOM commands to copy the current record guid to the clipboard for quick use in development or queries.

function copyToClipboard(text) {
   if (window.clipboardData && window.clipboardData.setData) {
      // IE specific code path to prevent textarea being shown while dialog is visible.
      return clipboardData.setData("Text", text);

   } else if (document.queryCommandSupported && document.queryCommandSupported("copy")) {
      var textarea = document.createElement("textarea");
      textarea.textContent = text;
      textarea.style.position = "fixed";  // Prevent scrolling to bottom of page in MS Edge.
      document.body.appendChild(textarea);
      textarea.select();
      try {
         return document.execCommand("copy");  // Security exception may be thrown by some browsers.
      } catch (ex) {
         console.warn("Copy to clipboard failed.", ex);
         return false;
      } finally {
         document.body.removeChild(textarea);
      }
   }
}

Dynamics CRM Audit Query

Due to a large number of records in our system we find that many times trying to open an audit log from a record results in a sql timeout error in CRM. Below I finally spent some time in coming up with a SQL query to get similar data. Replace the @objectid value with the record that you are attempting to view the audit for and you should get a returned table with the specified data.

NOTE: dates/times in the database are stored UTC, so some additional care should be taken with any dates returned in below query.


--drop table #audit

declare @objectid uniqueidentifier = '{AA2B1B39-02E7-E611-80C5-0050569E2CDA}'
select auditid, useridname, createdon, operation, AttributeMask, '~' + ChangeData + '~' ChangeData, objectid
into #audit
from audit a
where a.objectid = @objectid

declare @objecttypecode int = (select top 1 objecttypecode from audit where objectid = @objectid)

;with #tmp(auditid, useridname, createdon, operation, Attribute, AttributeMask, ChangeDataStr, ChangeData) as (
select auditid, useridname, createdon, operation,
LEFT(AttributeMask, CHARINDEX(',',AttributeMask+',')-1), STUFF(AttributeMask, 1, CHARINDEX(',',AttributeMask+','), ''),
LEFT(ChangeData, CHARINDEX('~',ChangeData+'~')-1), STUFF(ChangeData, 1, CHARINDEX('~',ChangeData+'~'), '')
from #audit
union all
select auditid, useridname, createdon, operation,
LEFT(AttributeMask, CHARINDEX(',',AttributeMask+',')-1), STUFF(AttributeMask, 1, CHARINDEX(',',AttributeMask+','), ''),
LEFT(ChangeData, CHARINDEX('~',ChangeData+'~')-1), STUFF(ChangeData, 1, CHARINDEX('~',ChangeData+'~'), '')
from #tmp
where ChangeData > ''
)


select auditid, useridname, createdon, operation, a.TableColumnName, ChangeDataStr
from #tmp t
join attribute a on t.attribute = a.ColumnNumber
join entity e on a.entityid = e.entityid and e.ObjectTypeCode = @objecttypecode
order by createdon desc
OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option