How to modify a custom field DataTable (sorting and appearance) from an extension?

Question

I am having difficulty customising the display of a DataTable for a contact custom field group (multiple-record tab). I am trying to write an extension that would use a bit of JavaScript to interact with the DataTable to do the following:

• Order the table on one of its columns (date descending) when the page is first opened
• Conditionally change the colour of rows based on rules and cell data values

I use multiple-record custom field group tabs extensively for contacts (a very useful feature that initially attracted me to CiviCRM). Although reports could be used to present data with more control, users are looking at the contact's tabs a lot more than they are reports and have asked for the above.

I have attempted to add some script in a hook. The form hooks such as buildForm don't fire so I found that I needed to use alterTemplateFile. (If it worked it would need extra conditions to apply it to certain tables only and sort on different columns for each.)

function customtablemods_civicrm_alterTemplateFile($formName, &$form, $context, &$tplName) {
  if ($tplName == "CRM/Contact/Page/View/CustomData.tpl") { 
    $script = " CRM.$('#records-2').dataTable().fnSort([1, 'desc']); ";
    CRM_Core_Resources::singleton()->addScript($script);
  }
}

This gives an error pop-up in the browser: DataTables warning: table id=records-2 - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

I guess that this means that my script is being executed before the DataTable is fully initialised by CiviCRM. I went digging to see how CiviCRM creates the DataTable. There is inline script that comes from templates/CRM/common/jsortable.tpl but I found that for these tables it doesn't seem to do anything--modifying the JS in this template had no effect. It seems that it is actually js/jquery/jquery.crmAjaxTable.js that creates the datatable. Modifying jquery.crmAjaxTable.js looks difficult and am hoping to avoid modifying core files due to maintainability.

I then tried using the crmLoad event:

function customtablemods_civicrm_alterTemplateFile($formName, &$form, $context, &$tplName) {
  if ($tplName == "CRM/Contact/Page/View/CustomData.tpl") { 
    $script = "
    CRM.$(function($) {
      $('#crm-main-content-wrapper').crmSnippet().on('crmLoad', function(e, data) {
        $('#records-2').dataTable().fnSort([1, 'desc']);
      } );
    } );
    ";

    CRM_Core_Resources::singleton()->addScript($script);
  }
}

It gives a "too much recursion" error in the browser console. The table is never sorted although the correct indication of sorting is given in the column heading. It looks like the sort causes the data to be re-loaded by Ajax which fires the crmLoad event again.

I also tried the DataTables API version in both of the above and it gives the same errors: CRM.$('#records-2').DataTable().order([1, 'desc']).draw();

For the row conditional formatting, the DataTables documentation gives an example using the rowCallback option. (I would use addClass more like in this example.)

$('#example').dataTable( {
  "rowCallback": function( row, data, index ) {
    if ( data.grade == "A" ) {
      $('td:eq(4)', row).html( '<b>A</b>' );
    }
  }
} );

However I can't see a feasible approach for an extension to include such an option for the DataTable at the time that it is first created by CiviCRM, and DataTable options generally can't be modified later.

Answer 1

I managed to sort on the first column of a dataTable like this:

In the main file of your extension, add the code to add a javascript file. I used the hook "civicrm_alterTemplateFile" for that.

function myextension_civicrm_alterTemplateFile($formName, &$form, $context, &$tplName) {
  if ($formName == 'CRM_Contact_Page_View_CustomData' && $context == 'page' && $tplName == 'CRM/Contact/Page/View/CustomData.tpl') {
    Civi::resources()->addScriptFile( 'com.myorg.myextension', 'js/alterDataTables.js' );
  }
}

Then I created a javascript file named "alterDataTables.js" in the "js" folder of my extension.

It contains this code:

CRM.$(function($) {
  $(document).ajaxSend(function(event, jqxhr, settings) {
    // get the datatable
    let myDataTable = $('#custom-30-table-wrapper .dataTable');
    if (myDataTable.length !== 0) {
      myDataTable.on('xhr.dt', function (e, settings, json, xhr) {
          // add sorting if there is no sorting
          if (settings.aaSorting.length === 0) {
            myDataTable.dataTable().api().order([0, 'desc']).draw();
          }
        })
        .dataTable();
    }
  });
});

Replace #custom-30 with the id of your custom group.