Track Template usage

[Giles Lewis]

Here is a simple customization that allows you to track whenever a template is applied to an Incident, Change Request or other Task based table.

Step 1:

Add a custom field to the Task table named u_template (label "Template"), which is a reference to sys_template. We will use this field to hold the sys_id of the template that was applied to the task.

Step 2:

Create a Before Insert/Update Business Rule on sys_template as follows: 

    var table = current.getValue('table');
    var gr = new GlideRecord(table);
    if (gr.instanceOf('task')) {
        var newTemplate = current.getValue('template');
        if (newTemplate) {
            // remove existing u_template field and add it back
            newTemplate = newTemplate.replace(/\^EQ$/, '');
            newTemplate = newTemplate.replace(/\^u_template=\w*/, '');
            newTemplate += '^u_template=' + current.getUniqueValue();
        }
        else {
            // Template was empty
            newTemplate = 'u_template=' + current.getUniqueValue();
        }
        newTemplate += '^EQ';
	current.setValue('template', newTemplate);
    }

What does this Business Rule do? It causes u_template to be appended to any template (for any table extended from Task) when that template is created or modified. The value will be the sys_id of the current record. We remove u_template from the template (if it exists), and then add it back, so it will always appear as the last field.

Try making some changes to a template, so that you can see how this clever Business Rule behaves.

Step 3:

Add the field u_template to the Incident, Change Request and any other forms where templates might be applied.

Why is this necessary? A template will only update those fields which appear on the form. If the field is not on the form, then it will not be set. 

Now apply your modified template to a record, and watch the u_template field get set.

Step 4:

Create a UI Policy on the Task table which causes u_template to never be visible. Be sure to check the "Inherit" box on the UI Policy.

I told you in Step 3 that the field needed to be on the form. But it does not need to be visible!

Voila! When the user applies a template to the record, the hidden field u_template will be set to the sys_id of that template.

Step 5:

We are almost done. The Business Rule will ensure that any new or updated template has a u_template field which points to itself. But what about our existing templates?

Let's create and run a Fix Script to update them all. We will use the same code as above, with a few tweaks. Note that when reading through the sys_template table we cannot call next() because sys_template contains a field named "next". We must instead call _next().

var grTemplate = new GlideRecord('sys_template');
grTemplate._query();
while (grTemplate._next()) {
    var table = grTemplate.getValue('table');
    var gr = new GlideRecord(table);
    if (gr.instanceOf('task')) {
        var newTemplate = grTemplate.getValue('template');
        if (newTemplate) {
            // remove existing u_template field and add it back
            newTemplate = newTemplate.replace(/\^EQ$/, '');
            newTemplate = newTemplate.replace(/\^u_template=\w*/, '');
            newTemplate += '^u_template=' + grTemplate.getUniqueValue();
        }
        else {
            // Template was empty
            newTemplate = 'u_template=' + grTemplate.getUniqueValue();
        }
        newTemplate += '^EQ';
        grTemplate.setValue('template', newTemplate);
        grTemplate.setWorkflow(false);
        grTemplate.autoSysFields(false);
        grTemplate.update();
    }
}

These scripts were modified 7/11/2021 to accommodate empty templates.

[GB14]

Hello @Giles Lewis 
Thank you very much for these steps. Very helpful. 

I have a few questions.

1) I am getting template names populated in the u_temaplte field instead of the sys_id. 

2) How do I create a report to count the template usage? 

Thanks and Happy New Year in Advance 🙂

[Seth_I_D_]

+1 GB

• Would love to see an example report of this in use.
• Also would love to ensure GB gets a fix and we share how the solution was found for posterity.

[Mike Foreman]

Hello @Giles Lewis  - this is a great solution and it's really great to see a familiar name and face on the forum! I hope all is well!

[Su522]

For any form, you can mirror the below steps:

On the Project form > add a new String Field

On all your Project Templates > add the same String Field > populate it with the Template Name

On the project form, make the new String field 'Read Only' and you can even hide it.

You can now report on Projects created by which Template