Develop Pluggable Reusable Audit History

January 18, 2021 By Shahaji No comments

AgilePoint provides excellent real-time visualization of the workflow/process with an out-of-the-box feature called “View Process” through the work center. However, some business use cases demand textual audit history; something like below.

It would be great if this kind of audit history is available through a generic action on the task context menu as depicted below.

Picture B – Custom Task Context Menu Action

This is what we are building here. Let’s break this out into digestible pieces. We would like to develop a generic app, which shows results like Picture ‘A’. Also, we need to develop a custom context menu action for tasks as shown in Picture ‘B’. When a custom menu action ” Audit History” is clicked, it should open up Picture ‘A’ into a separate browser tab/window [Note – this can be shown as a popup model dialog as well]. The generic app will be expecting a process instance id to pull the process instance-specific audit history and this process instance id will be passed as a querystring parameter from the custom action menu to the generic app (Picture ‘A’).

A. Development of Audit History application

Let’s develop the app, which generates a tabular audit history. Technically, there are two ways to develop this app. 1 -> Execute a SQL query against the AgilePoint database using eForm lookup feature. 2 -> Execute a SQL query against the AgilePoint database using AgilePoint API. We will develop the app using the 1st approach. I might write an another post to demonstrate the 2nd approach. Let’s navigate to the Build Apps.

Create a new Form-Based Application and choose ‘Widget’ at the time of choosing a data source. We need a non-transactional form where audit data is going to be displayed retrieved from the AgilePoint system’s database.
Drag and drop a DataGrid (kendo) control on the form. Name it “AuditHistoryGrid”. This is to display the audit history data.
Drag and drop a TextBox control on the form. Name it “PIID” and make it invisible. This is to store the query string.
Drag and drop an Auto-Lookup control on the form. This is to trigger the lookup and bind the result data to the Data Grid.
Configure the AutoLookup as per the below steps
- Under the Lookup Source, create a new lookup of type Database
- If there is no existing access token against the AgilePoint database then create one (preferably global access token) and complete the first part of the lookup configuration as shown below.

6. The next step is to configure the SQL Query. We need a manual query here. Below is the query…

SELECT usr.FULL_NAME as ‘TaskOwner’,
task.NAME as ‘TaskName’ , concat(‘Date: ‘, CONVERT(date,task.ASSIGNED_DATE,120),’ Time: ‘, CONVERT(varchar(5),task.ASSIGNED_DATE,108)) as ‘AssignedDate’, concat(‘Date: ‘, CONVERT(date,task.DUE_DATE,120),’ Time: ‘, CONVERT(varchar(5),task.DUE_DATE,108)) as ‘DueDate’ , concat(‘Date: ‘, CONVERT(date,task.COMPLETED_DATE,120),’ Time: ‘, CONVERT(varchar(5),task.COMPLETED_DATE,108)) as ‘CompletionDate’ , task.Status as ‘Status’
FROM WF_MANUAL_WORKITEMS task inner join WF_REG_USERS usr on task.USER_ID = usr.USER_NAME
WHERE task.PROC_INST_ID=’${PIID}’ ORDER BY case when task.ASSIGNED_DATE is null then 1 else 0 end, task.ASSIGNED_DATE

Notice, all three date fields have been formatted to get data into the desired format. Also, the field name “${PIID}” is used in the WHERE clause to filter the data for a specific process instance.

Validate the query and Finish the lookup configuration. This will take us on the Auto-Lookup configuration screen. Select Data Grid for the “Bind Lookup Values to A Repeating Control…” dropdown. And Finally, select “Custom Trigger” value for the “Execute This Lookup On” dropdown. This completes the Lookup and Auto-Lookup configuration.

Now, let’s take on the custom trigger of the auto-lookup through JavaScript.


function getUrlParameter(name) {
    name = name.replace(/[\[]/, '\\[').replace(/[\]]/, '\\]');
    var regex = new RegExp('[\\?&]' + name + '=([^&#]*)');
    var results = regex.exec(location.search);
    return results === null ? '' : decodeURIComponent(results[1].replace(/\+/g, ' '));
}

function triggerLookup(){
	var piid = getUrlParameter('piid');
  	eFormHelper.setFieldValue( {fieldId: 'PIID', value: piid });
  	var options = {};
	options.fieldId = 'GetAuditHistory';
	
  	eFormHelper.triggerAutoLookup(options, function(result) {
      if(result.isSuccess){
  		$("#AuditHistoryGrid thead [data-field=TaskOwner] a").text('Task Owner');
      	$("#AuditHistoryGrid thead [data-field=TaskName] a").text('Task Name');
        $("#AuditHistoryGrid thead [data-field=AssignedDate] a").text('Assigned Date');
        $("#AuditHistoryGrid thead [data-field=DueDate] a").text('Due Date');
        $("#AuditHistoryGrid thead [data-field=CompletionDate] a").text('Completion Date');
      }
      else
      {
        console.log(result.error);
      }
  	});
    
}

The code above is quite self-explanatory. It has two functions; one is to read a QueryString parameter and the other one is for triggering the auto-lookup. Also, in the Auto-Lookup trigger function, we have a script for renaming column headers of the Data Grid control.

The final step in this Forms-Based Widget application is to setup a form rule, which calls the “triggerLookup” JavaScript function. Open up the Rule Builder from the Forms designer and configure the rule as shown below.

The final step is to “Save and Check In” the form and publish the application. This completes part ‘A’ portion outlined at the beginning. The portion ‘B’ is to develop the custom context menu action.

B. Development of custom context menu action

Navigate to Settings -> Work Center -> Custom Menu Actions … as shown below.

Click on “Add Custom Context Menu” button on the top. Fill up the data shown below in the configuration fields.

Menu Item Text – text to appear on the context menu
Menu Item Ext – what places this new custom context menu item needs to be shown.
Menu Item Index – what position the new custom context menu item needs to appear in the context menu.
Menu Item Image URL – this is an image that appears on the Context Menu for this new custom menu item. Generate Base64 of an image you would like to see and put it in this field.
Menu Item Validation Method – the name of a JavaScript method, which can be used for any validation before this new context menu action item is shown.
Menu Item OnClick Method – the name of a JavaScript method, which gets executed on the click of the new custom action menu item.

We need two JavaScript methods; ValidateHistory and ShowHistory. Click on the “Add JavaScript Methods” button. This provides a popup editor. Copy the below code and put in the editor.

(function (global) {

    global.ShowHistory = function(task, ap){
        this.window.open("/appbuilder/formrender?FormApplication=Audit%20History_Audit%20Report&piid=" + task.ProcInstID);        
    };
    global.ValidateHistory = function(task, ap){
        return true;
    };
})(window)

There is no validation performed in the ValidateHistory method. However, this method can be leveraged to add filters if this new custom menu action needs to be shown in certain conditions or for certain applications. The “task” and “ap” arguments in the method provide complete context about the task as well as AgilePoint portal related configuration.

The ShowHistory JavaScript method is opening the Forms-Based Widget form (Audit History app developed above in part ‘A’) in a new browser tab. Also, this method access the process instance id of a task where user has clicked and pass it to the the Audit History application. The url of the Audit History app can be copied by going to Build Apps as shown below.

Use the relative portion of the url copied from the above and put it in the ShowHistory JavaScript method and append it with a querystring ‘piid’.

this.window.open(“/appbuilder/formrender?FormApplication=Audit%20History_Audit%20Report&piid=” + task.ProcInstID);

This completes part ‘B’ of configuring the Custom Menu Action. The result is, when clicked on any task in “My Task” or “In Queue”, it shows a new Context Menu Action Item with the name “AuditHistory” as shown in Picture ‘B’. On the click of “AuditHistory” menu option, it shows Picture ‘A’.

The Forms-Based Widget application is available to download from the below download link.

Audit_History Download