Tag: Sys ID

Collaboration Store, Part LXIII

Posted on | by snhackery

“One of my most productive days was throwing away 1,000 lines of code.”
— Ken Thompson

Last time, we wrapped up the code to refactor the application publishing process. Now we need to do the same thing with the application distribution process, the process that runs on the Host instance to share recently published application versions to all of the other Client instances in the community. Currently, this involves three separate functions, publishNewVersion, publishVersionRecord, and publishVersionAttachment.

publishNewVersion: function(newVersion, targetInstance, attachmentId) {
	var targetGR = new GlideRecord('x_11556_col_store_member_organization');
	if (targetGR.get('instance', targetInstance)) {
		var token = targetGR.getDisplayValue('token');
		var versionGR = new GlideRecord('x_11556_col_store_member_application_version');
		if (versionGR.get(newVersion)) {
			var canContinue = true;
			var targetAppId = '';
			var mbrAppGR = versionGR.member_application.getRefRecord();
			var request  = new sn_ws.RESTMessageV2();
			request.setHttpMethod('get');
			request.setBasicAuth(this.WORKER_ROOT + targetInstance, token);
			request.setRequestHeader("Accept", "application/json");
			request.setEndpoint('https://' + targetInstance + '.service-now.com/api/now/table/x_11556_col_store_member_application?sysparm_fields=sys_id&sysparm_query=provider.instance%3D' + mbrAppGR.getDisplayValue('provider.instance') + '%5Ename%3D' + encodeURIComponent(mbrAppGR.getDisplayValue('name')));
			var response = request.execute();
			if (response.haveError()) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
				canContinue = false;
			} else if (response.getStatusCode() == '200') {
				var jsonString = response.getBody();
				var jsonObject = {};
				try {
					jsonObject = JSON.parse(jsonString);
				} catch (e) {
					gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + jsonString);
					canContinue = false;
				}
				if (canContinue) {
					var payload = {};
					payload.name = mbrAppGR.getDisplayValue('name');
					payload.scope = mbrAppGR.getDisplayValue('scope');
					payload.description = mbrAppGR.getDisplayValue('description');
					payload.current_version = mbrAppGR.getDisplayValue('current_version');
					payload.active = 'true';
					request  = new sn_ws.RESTMessageV2();
					request.setBasicAuth(this.WORKER_ROOT + targetInstance, token);
					request.setRequestHeader("Accept", "application/json");
					if (jsonObject.result && jsonObject.result.length > 0) {
						targetAppId = jsonObject.result[0].sys_id;
						request.setHttpMethod('put');
						request.setEndpoint('https://' + targetInstance + '.service-now.com/api/now/table/x_11556_col_store_member_application/' + targetAppId);
					} else {
						request.setHttpMethod('post');
						request.setEndpoint('https://' + targetInstance + '.service-now.com/api/now/table/x_11556_col_store_member_application');
						payload.provider = mbrAppGR.getDisplayValue('provider.instance');
					}
					request.setRequestBody(JSON.stringify(payload, null, '\t'));
					response = request.execute();
					if (response.haveError()) {
						gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
						canContinue = false;
					} else if (response.getStatusCode() != 200 && response.getStatusCode() != 201) {
						gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + response.getStatusCode());
						canContinue = false;
					} else {
						jsonString = response.getBody();
						jsonObject = {};
						try {
							jsonObject = JSON.parse(jsonString);
						} catch (e) {
							gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + jsonString);
							canContinue = false;
						}
						if (canContinue) {
							targetAppId = jsonObject.result.sys_id;
						}
					}
				}
			} else {
				gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + response.getStatusCode());
			}
			if (canContinue) {
				this.publishVersionRecord(targetInstance, token, versionGR, targetAppId, attachmentId);
			}
		} else {
			gs.error('CollaborationStoreUtils.publishNewVersion: Version record not found: ' + newVersion);
		}
	} else {
		gs.error('CollaborationStoreUtils.publishNewVersion: Target instance record not found: ' + targetInstance);
	}
},

publishVersionRecord: function(targetInstance, token, versionGR, targetAppId, attachmentId) {
	var canContinue = true;
	var payload = {};
	payload.member_application = targetAppId;
	payload.version = versionGR.getDisplayValue('version');
	payload.built_on = versionGR.getDisplayValue('built_on');
	var request  = new sn_ws.RESTMessageV2();
	request.setBasicAuth(this.WORKER_ROOT + targetInstance, token);
	request.setRequestHeader("Accept", "application/json");
	request.setHttpMethod('post');
	request.setEndpoint('https://' + targetInstance + '.service-now.com/api/now/table/x_11556_col_store_member_application_version');
	request.setRequestBody(JSON.stringify(payload, null, '\t'));
	response = request.execute();
	if (response.haveError()) {
		gs.error('CollaborationStoreUtils.publishVersionRecord: Error returned from Target instance ' + targetInstance + ': ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		canContinue = false;
	} else if (response.getStatusCode() != 201) {
		gs.error('CollaborationStoreUtils.publishVersionRecord: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + response.getStatusCode());
		canContinue = false;
	} else {
		jsonString = response.getBody();
		jsonObject = {};
		try {
			jsonObject = JSON.parse(jsonString);
		} catch (e) {
			gs.error('CollaborationStoreUtils.publishVersionRecord: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + jsonString);
			canContinue = false;
		}
		if (canContinue) {
			targetVerId = jsonObject.result.sys_id;
			this.publishVersionAttachment(targetInstance, token, targetVerId, attachmentId);
		}
	}
},

publishVersionAttachment: function(targetInstance, token, targetVerId, attachmentId) {
	var gsa = new GlideSysAttachment();
	var sysAttGR = new GlideRecord('sys_attachment');
	if (sysAttGR.get(attachmentId)) {
		var url = 'https://';
		url += targetInstance;
		url += '.service-now.com/api/now/attachment/file?table_name=x_11556_col_store_member_application_version&table_sys_id=';
		url += targetVerId;
		url += '&file_name=';
		url += sysAttGR.getDisplayValue('file_name');
		var request  = new sn_ws.RESTMessageV2();
		request.setBasicAuth(this.WORKER_ROOT + targetInstance, token);
		request.setRequestHeader('Content-Type', sysAttGR.getDisplayValue('content_type'));
		request.setRequestHeader('Accept', 'application/json');
		request.setHttpMethod('post');
		request.setEndpoint(url);
		request.setRequestBody(gsa.getContent(sysAttGR));
		response = request.execute();
		if (response.haveError()) {
			gs.error('CollaborationStoreUtils.publishVersionAttachment: Error returned from Target instance ' + targetInstance + ': ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		} else if (response.getStatusCode() != 201) {
			gs.error('CollaborationStoreUtils.publishVersionAttachment: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + response.getStatusCode());
		}
	} else {
		gs.error('CollaborationStoreUtils.publishVersionAttachment: Invalid attachment record sys_id: ' + attachmentId);
	}
}

The corresponding shared functions are pushApplication, pushVersion, and pushAttachment in the CollaborationStoreUtils Script Include. The pushApplication takes the application GlideRecord, the target instance GlideRecord, and the sys_id of the application providing instance on the target instance as arguments. The current function already has code to fetch the target instance GlideRecord and the version GlideRecord, and from the version GlideRecord, we can obtain the application GlideRecord. We also built a function earlier that fetched the sys_id of an instance record on the target instance, but that function assumed that you were looking for the local instance on the target instance. In this case, we are looking for the instance that provided the application, so if we want to leverage that function, we will have to alter it to accept the instance name as one of the arguments. Here is the start of the function as it stands right now.

getRemoteInstanceSysId: function(targetGR) {
	var sysId = '';

	var result = {};
	result.url = 'https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/x_11556_col_store_member_organization?sysparm_fields=sys_id&sysparm_query=instance%3D' + gs.getProperty('instance_name');
	...
}

To add the ability to pass in the instance name, we can change that to this:

getRemoteInstanceSysId: function(targetGR, instanceName) {
	var sysId = '';

	var result = {};
	result.url = 'https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/x_11556_col_store_member_organization?sysparm_fields=sys_id&sysparm_query=instance%3D' + instanceName;
	...
}

Then we just have to go back into the code and find where this function is called and add the appropriate instance name to the argument list. With that little modification out of the way, we should be able to gather up all of the required arguments to call the pushApplication function. That will make our replacement code look like this:

publishNewVersion: function(newVersion, targetInstance, attachmentId) {
	var targetGR = new GlideRecord('x_11556_col_store_member_organization');
	if (targetGR.get('instance', targetInstance)) {
		var versionGR = new GlideRecord('x_11556_col_store_member_application_version');
		if (versionGR.get(newVersion)) {
			var applicationGR = versionGR.member_application.getRefRecord();
			var remoteSysId = this.getRemoteInstanceSysId(targetGR, applicationGR.getDisplayValue('provider.instance'));
			var result = this.pushApplication(applicationGR, targetGR, remoteSysId);
			if (result.error) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + result.error_code + ' - ' + result.error_message);
			} else if (result.parse_error) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + result.body);
			} else if (result.status != 200 && result.status != 201) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + result.status);
			} else {
				var remoteAppId = result.obj.result.sys_id;
			}
		} else {
			gs.error('CollaborationStoreUtils.publishNewVersion: Version record not found: ' + newVersion);
		}
	} else {
		gs.error('CollaborationStoreUtils.publishNewVersion: Target instance record not found: ' + targetInstance);
	}
}

If all goes well, we will be able to obtain the sys_id of the transferred application record on the target system from the result object, which is the only other piece of data that we need in order to call the pushVersion function. In fact, we can make that call on the very next line and check the results right there.

var remoteAppId = result.obj.result.sys_id;
result = this.pushVersion(versionGR, targetGR, remoteAppId);
if (result.error) {
	gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + result.error_code + ' - ' + result.error_message);
} else if (result.parse_error) {
	gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + result.body);
} else if (result.status != 200 && result.status != 201) {
	gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + result.status);
} else {
	var remoteVerId = result.obj.result.sys_id;
}

And again, if all goes well, we will be able to obtain the sys_id of the transferred version record on the target system from the result object, which is the other piece of data that we need in order to call the pushAttachment function. And once again, we can make that call on the very next line and check the results right there.

var remoteVerId = result.obj.result.sys_id;
result = this.pushAttachment(attachmentGR, targetGR, remoteVerId);
if (result.error) {
	gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + result.error_code + ' - ' + result.error_message);
} else if (result.parse_error) {
	gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + result.body);
} else if (result.status != 200 && result.status != 201) {
	gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + result.status);
}

So putting it all together in this way, we can replace the original three functions with a single function that makes three calls to our shared functions. Once again, we have simplified things quite a bit and at the same time added functionality with the inclusion of the logo images and the REST API call logging. Here is the whole thing all put together:

publishNewVersion: function(newVersion, targetInstance, attachmentId) {
	var targetGR = new GlideRecord('x_11556_col_store_member_organization');
	if (targetGR.get('instance', targetInstance)) {
		var versionGR = new GlideRecord('x_11556_col_store_member_application_version');
		if (versionGR.get(newVersion)) {
			var applicationGR = versionGR.member_application.getRefRecord();
			var remoteSysId = this.getRemoteInstanceSysId(targetGR, applicationGR.getDisplayValue('provider.instance'));
			var result = this.pushApplication(applicationGR, targetGR, remoteSysId);
			if (result.error) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + result.error_code + ' - ' + result.error_message);
			} else if (result.parse_error) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + result.body);
			} else if (result.status != 200 && result.status != 201) {
				gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + result.status);
			} else {
				var remoteAppId = result.obj.result.sys_id;
				result = this.pushVersion(versionGR, targetGR, remoteAppId);
				if (result.error) {
					gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + result.error_code + ' - ' + result.error_message);
				} else if (result.parse_error) {
					gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + result.body);
				} else if (result.status != 200 && result.status != 201) {
					gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + result.status);
				} else {
					var remoteVerId = result.obj.result.sys_id;
					result = this.pushAttachment(attachmentGR, targetGR, remoteVerId);
					if (result.error) {
						gs.error('CollaborationStoreUtils.publishNewVersion: Error returned from Target instance ' + targetInstance + ': ' + result.error_code + ' - ' + result.error_message);
					} else if (result.parse_error) {
						gs.error('CollaborationStoreUtils.publishNewVersion: Unparsable JSON string returned from Target instance ' + targetInstance + ': ' + result.body);
					} else if (result.status != 200 && result.status != 201) {
						gs.error('CollaborationStoreUtils.publishNewVersion: Invalid HTTP Response Code returned from Target instance ' + targetInstance + ': ' + result.status);
					}
				}
			}
		} else {
			gs.error('CollaborationStoreUtils.publishNewVersion: Version record not found: ' + newVersion);
		}
	} else {
		gs.error('CollaborationStoreUtils.publishNewVersion: Target instance record not found: ' + targetInstance);
	}
}

That should cover the application distribution process, which should complete the list of features that should be relying on the shared functions. The only thing left at this point would be to hunt down all of the other REST API calls and throw in a call to the new shared logging function. Maybe we will take a look at that next time out.

Collaboration Store, Part LXII

Posted on | by snhackery

“Now I’m a pretty lazy person and am prepared to work quite hard in order to avoid work.”
— Martin Fowler

Last time, we modified the processPhase5 function in the ApplicationPublisher Script Include to use the shared functions for making REST API calls instead of its own code. Now we need to continue with that work and do the same for the processPhase6 and processPhase7 functions. Here is the current script for the processPhase6 function.

processPhase6: function(answer) {
	var versionGR = new GlideRecord('x_11556_col_store_member_application_version');
	if (versionGR.get(answer.versionId)) {
		var host = gs.getProperty('x_11556_col_store.host_instance');
		var token = gs.getProperty('x_11556_col_store.active_token');
		var payload = {};
		payload.member_application = answer.hostAppId;
		payload.version = versionGR.getDisplayValue('version');
		payload.built_on = versionGR.getDisplayValue('built_on');
		var request  = new sn_ws.RESTMessageV2();
		request.setBasicAuth(this.WORKER_ROOT + host, token);
		request.setRequestHeader("Accept", "application/json");
		request.setHttpMethod('post');
		request.setEndpoint('https://' + host + '.service-now.com/api/now/table/x_11556_col_store_member_application_version');
		request.setRequestBody(JSON.stringify(payload, null, '\t'));
		response = request.execute();
		if (response.haveError()) {
			answer = this.processError(answer, 'Error returned from Host instance: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		} else if (response.getStatusCode() != 201) {
			answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + response.getStatusCode());
		} else {
			jsonString = response.getBody();
			jsonObject = {};
			try {
				jsonObject = JSON.parse(jsonString);
			} catch (e) {
				answer = this.processError(answer, 'Unparsable JSON string returned from Host instance: ' + jsonString);
			}
			if (!answer.error) {
				answer.hostVerId = jsonObject.result.sys_id;
			}
		}
	} else {
		answer = this.processError(answer, 'Invalid version record sys_id: ' + answer.versionId);
	}

	return answer;
}

The corresponding shared function is pushVersion, which takes as arguments the version GlideRecord, the target instance GlideRecord, and the sys_id of the application record on the target system. We are already fetching the version GlideRecord, we can use the function that we built last time to go get the target instance GlideRecord, and the sys_id of the application record on the target system was stored in the shared answer object in phase 5, so we should have everything that we need to invoke the appropriate shared function and check the results.

processPhase6: function(answer) {
	var versionGR = new GlideRecord('x_11556_col_store_member_application_version');
	if (versionGR.get(answer.versionId)) {
		var targetGR = this.getHostInstanceGR();
		var csu = new CollaborationStoreUtils();
		var result = csu.pushVersion(versionGR, targetGR, answer.hostAppId);
		if (result.error) {
			answer = this.processError(answer, 'Error returned from Host instance: ' + result.error_code + ' - ' + result.error_message);
		} else if (result.parse_error) {
			answer = this.processError(answer, 'Unparsable JSON string returned from Host instance: ' + result.body);
		} else if (result.status != 200 && result.status != 201) {
			answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + result.status);
		} else {
			answer.hostVerId = result.obj.result.sys_id;
		}
	} else {
		answer = this.processError(answer, 'Invalid version record sys_id: ' + answer.versionId);
	}

	return answer;
}

That should take care of the processPhase6 function. Now, let’s take a look at that processPhase7 function.

processPhase7: function(answer) {
	var gsa = new GlideSysAttachment();
	var sysAttGR = new GlideRecord('sys_attachment');
	if (sysAttGR.get(answer.attachmentId)) {
		var host = gs.getProperty('x_11556_col_store.host_instance');
		var token = gs.getProperty('x_11556_col_store.active_token');
		var url = 'https://';
		url += host;
		url += '.service-now.com/api/now/attachment/file?table_name=x_11556_col_store_member_application_version&table_sys_id=';
		url += answer.hostVerId;
		url += '&file_name=';
		url += sysAttGR.getDisplayValue('file_name');
		var request  = new sn_ws.RESTMessageV2();
		request.setBasicAuth(this.WORKER_ROOT + host, token);
		request.setRequestHeader('Content-Type', sysAttGR.getDisplayValue('content_type'));
		request.setRequestHeader('Accept', 'application/json');
		request.setHttpMethod('post');
		request.setEndpoint(url);
		request.setRequestBody(gsa.getContent(sysAttGR));
		response = request.execute();
		if (response.haveError()) {
			answer = this.processError(answer, 'Error returned from Host instance: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		} else if (response.getStatusCode() != 201) {
			answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + response.getStatusCode());
		}
	} else {
		answer = this.processError(answer, 'Invalid attachment record sys_id: ' + answer.attachmentId);
	}

	return answer;
}

The corresponding shared function is pushAttachment, which takes as arguments the attachment GlideRecord, the target instance GlideRecord, and the sys_id of the version record on the target system. Once again, we are already fetching the attachment GlideRecord, we can use the function that we built last time to go get the target instance GlideRecord, and the sys_id of the version record on the target system was stored in the shared answer object in phase 6, so once again we should have everything that we need to invoke the appropriate shared function and check the results.

processPhase7: function(answer) {
	var gsa = new GlideSysAttachment();
	var attachmentGR = new GlideRecord('sys_attachment');
	if (attachmentGR.get(answer.attachmentId)) {
		var targetGR = this.getHostInstanceGR();
		var csu = new CollaborationStoreUtils();
		var result = csu.pushAttachment(attachmentGR, targetGR, answer.hostVerId);
		if (result.error) {
			answer = this.processError(answer, 'Error returned from Host instance: ' + result.error_code + ' - ' + result.error_message);
		} else if (result.parse_error) {
			answer = this.processError(answer, 'Unparsable JSON string returned from Host instance: ' + result.body);
		} else if (result.status != 200 && result.status != 201) {
			answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + result.status);
		} else {
			answer.hostVerId = result.obj.result.sys_id;
		}
	} else {
		answer = this.processError(answer, 'Invalid attachment record sys_id: ' + answer.attachmentId);
	}

	return answer;
}

So that wraps up all of the refactoring for the application publishing process. All that is left to do now is to do the same thing for the application distribution process, where the Host instance sends out the artifacts for new application versions to all of the other Client instances in the community. We’ll jump right into that next time out.

Collaboration Store, Part LXI

Posted on | by snhackery

“I think it is often easier to make progress on mega-ambitious dreams. Since no one else is crazy enough to do it, you have little competition.”
— Larry Page

Last time, we wrapped up all of the code in the shared functions to include the logo images whenever an instance or application is transferred from one instance to another. Now we need to take a look at those places where the shared functions are not currently being used and replace the code in those existing functions with calls to the shared functions. This will not only consolidate the code and ensure that the logo images will be included, but since we also added a logging feature to the shared functions, it will also ensure that the REST API activity gets recorded.

Our ApplicationPublisher Script Include contains individual functions for the 7 independent phases of the application publishing process. The first four are all internal, but the last three move the artifacts from the Client instance to the the Host, so we will want to rework each one of those. Before we do that, though, we will want to snag the image from the sys_app record and attach it to our application record, which is something that we can do in Phase 2, once the application record is available. We can insert a line into this code:

mbrAppGR.setValue('name', sysAppGR.getValue('name'));
mbrAppGR.setValue('scope', sysAppGR.getValue('scope'));
mbrAppGR.setValue('description', sysAppGR.getValue('short_description'));
mbrAppGR.setValue('current_version', sysAppGR.getValue('version'));
mbrAppGR.setValue('active', true);
mbrAppGR.update();

… and make it look like this:

mbrAppGR.setValue('name', sysAppGR.getValue('name'));
mbrAppGR.setValue('scope', sysAppGR.getValue('scope'));
mbrAppGR.setValue('description', sysAppGR.getValue('short_description'));
mbrAppGR.setValue('current_version', sysAppGR.getValue('version'));
mbrAppGR.setValue('active', true);
if (sysAppGR.getValue('logo') && !mbrAppGR.getValue('logo')) {
	mbrAppGR.setValue('logo', this.copyLogoImage(answer));
}
mbrAppGR.update();

Of course, now that we have done that, we will need to build a new copyLogoImage function. We already have a function that copies an attachment (Phase 4), and we can steal most of the code from that guy.

processPhase4: function(answer) {
	var gsa = new GlideSysAttachment();
	var values = gsa.copy('sys_app', answer.appSysId, 'x_11556_col_store_member_application_version', answer.versionId);
	gsa.deleteAttachment(answer.attachmentId);
	if (values.length > 0) {
		var ids = values[values.length - 1].split(',');
		if (ids[1]) {
			answer.attachmentId = ids[1];
		} else {
			answer = this.processError(answer, 'Unrecognizable response from attachment copy: ' + values);
		}
	} else {
		answer = this.processError(answer, 'Unrecognizable response from attachment copy: ' + values);
	}

	return answer;
}

We want to return the attachment sys_id and not the answer object, and we need to swap out the table names, but other than that, it looks pretty similar:

copyLogoImage: function(answer) {
	var logoId = '';

	var gsa = new GlideSysAttachment();
	var values = gsa.copy('ZZ_YYsys_app', answer.appSysId, 'ZZ_YYx_11556_col_store_member_application', answer.mbrAppId);
	if (values.length > 0) {
		var ids = values[values.length - 1].split(',');
		if (ids[1]) {
			logoId = ids[1];
		} else {
			answer = this.processError(answer, 'Unrecognizable response from logo attachment copy: ' + values);
		}
	} else {
		answer = this.processError(answer, 'Unrecognizable response from logo attachment copy: ' + values);
	}

	return logoId;
}

That will get the logo image from the Scoped Application attached to our application record during the application publishing process. Now we need to take a look at those functions that send the local artifacts over to the Host instance and see if we can alter them to use the newly modified shared function. The first of such functions is processPhase5, and it moves over the application record.

processPhase5: function(answer) {
	var mbrAppGR = new GlideRecord('x_11556_col_store_member_application');
	if (mbrAppGR.get(answer.mbrAppId)) {
		var host = gs.getProperty('x_11556_col_store.host_instance');
		var token = gs.getProperty('x_11556_col_store.active_token');
		var thisInstance = gs.getProperty('instance_name');
		var request  = new sn_ws.RESTMessageV2();
		request.setHttpMethod('get');
		request.setBasicAuth(this.WORKER_ROOT + host, token);
		request.setRequestHeader("Accept", "application/json");
		request.setEndpoint('https://' + host + '.service-now.com/api/now/table/x_11556_col_store_member_application?sysparm_fields=sys_id&sysparm_query=provider.instance%3D' + thisInstance + '%5Ename%3D' + encodeURIComponent(mbrAppGR.getDisplayValue('name')));
		var response = request.execute();
		if (response.haveError()) {
			answer = this.processError(answer, 'Error returned from Host instance: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
		} else if (response.getStatusCode() == '200') {
			var jsonString = response.getBody();
			var jsonObject = {};
			try {
				jsonObject = JSON.parse(jsonString);
			} catch (e) {
				answer = this.processError(answer, 'Unparsable JSON string returned from Host instance: ' + jsonString);
			}
			if (!answer.error) {
				var payload = {};
				payload.name = mbrAppGR.getDisplayValue('name');
				payload.scope = mbrAppGR.getDisplayValue('scope');
				payload.description = mbrAppGR.getDisplayValue('description');
				payload.current_version = mbrAppGR.getDisplayValue('current_version');
				payload.active = 'true';
				request  = new sn_ws.RESTMessageV2();
				request.setBasicAuth(this.WORKER_ROOT + host, token);
				request.setRequestHeader("Accept", "application/json");
				if (jsonObject.result && jsonObject.result.length > 0) {
					answer.hostAppId = jsonObject.result[0].sys_id;
					request.setHttpMethod('put');
					request.setEndpoint('https://' + host + '.service-now.com/api/now/table/x_11556_col_store_member_application/' + answer.hostAppId);
				} else {
					request.setHttpMethod('post');
					request.setEndpoint('https://' + host + '.service-now.com/api/now/table/x_11556_col_store_member_application');
					payload.provider = thisInstance;
				}
				request.setRequestBody(JSON.stringify(payload, null, '\t'));
				response = request.execute();
				if (response.haveError()) {
					answer = this.processError(answer, 'Error returned from Host instance: ' + response.getErrorCode() + ' - ' + response.getErrorMessage());
				} else if (response.getStatusCode() != 200 && response.getStatusCode() != 201) {
					answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + response.getStatusCode());
				} else {
					jsonString = response.getBody();
					jsonObject = {};
					try {
						jsonObject = JSON.parse(jsonString);
					} catch (e) {
						answer = this.processError(answer, 'Unparsable JSON string returned from Host instance: ' + jsonString);
					}
					if (!answer.error) {
						answer.hostAppId = jsonObject.result.sys_id;
					}
				}
			}
		} else {
			answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + response.getStatusCode());
		}
	} else {
		answer = this.processError(answer, 'Invalid Member Application sys_id: ' + answer.appSysId);
	}

	return answer;
}

The corresponding shared function is pushApplication in the CollaborationStoreUtils Script Include, which takes the application GlideRecord, the target instance GlideRecord, and the sys_id of the local instance on the target instance as arguments. We already have the application GlideRecord, but will need to fetch the GlideRecord for the Host instance and grab the sys_id of local instance on the Host instance before we can make the call. Assuming that we can create a couple of functions to gather up the information that we need, we can reduce the new processPhase5 function to this:

processPhase5: function(answer) {
	var applicationGR = new GlideRecord('x_11556_col_store_member_application');
	if (applicationGR.get(answer.mbrAppId)) {
		var targetGR = this.getHostInstanceGR();
		var csu = new CollaborationStoreUtils();
		answer.hostInstanceId = csu.getRemoteInstanceSysId(targetGR);
		var result = csu.pushApplication(applicationGR, targetGR, answer.hostInstanceId);
		if (result.error) {
			answer = this.processError(answer, 'Error returned from Host instance: ' + result.error_code() + ' - ' + result.error_message);
		} else if (result.parse_error) {
			answer = this.processError(answer, 'Unparsable JSON string returned from Host instance: ' + result.body);
		} else if (result.status != 200 && result.status != 201) {
			answer = this.processError(answer, 'Invalid HTTP Response Code returned from Host instance: ' + result.status);
		} else {
			answer.hostAppId = result.obj.result.sys_id;
		}
	} else {
		answer = this.processError(answer, 'Invalid Member Application sys_id: ' + answer.mbrAppId);
	}

	return answer;
}

That simplifies the code quite a bit, and yet we will be doing more work, as we will be moving over the logo image and also logging all the REST API calls. Much better. Of course we still need to build out those function to gather up the required arguments, but those should both be fairly straightforward. To fetch the Host instance GlideRecord, I kept the function in the ApplicationPublisher Script Include, but I put the other one in the main CollaborationStoreUtils Script Include, as that involves another REST API call to the Host instance, and I want to keep all of the functions that do that together in the same place. Here is the getHostInstanceGR function in the ApplicationPublisher Script Include:

getHostInstanceGR: function() {
	var instanceGR = new GlideRecord('x_11556_col_store_member_organization');
	instanceGR.get('instance', gs.getProperty('x_11556_col_store.host_instance'));
	return instanceGR;
}

And here is the getRemoteInstanceSysId function in the CollaborationStoreUtils Script Include:

getRemoteInstanceSysId: function(targetGR) {
	var sysId = '';

	var result = {};
	result.url = 'https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/x_11556_col_store_member_organization?sysparm_fields=sys_id&sysparm_query=instance%3D' + gs.getProperty('instance_name');
	result.method = 'GET';
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint(result.url);
	request.setHttpMethod(result.method);
	request.setBasicAuth(this.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
	request.setRequestHeader('Content-Type', 'application/json');
	request.setRequestHeader('Accept', 'application/json');
	var response = request.execute();
	result.status = response.getStatusCode();
	result.body = response.getBody();
	if (result.body) {
		try {
			result.obj = JSON.parse(result.body);
		} catch (e) {
			result.parse_error = e.toString();
		}
	}
	result.error = response.haveError();
	if (result.error) {
		result.error_code = response.getErrorCode();
		result.error_message = response.getErrorMessage();
	} else if (result.obj && result.obj.result && result.obj.result.length > 0) {
		sysId = result.obj.result[0].sys_id;
	}
	this.logRESTCall(targetGR, result);

	return sysId;
}

So that takes care of the first of the three functions that need to modified to use the shared REST API functions. Now we just need to do the same thing for the other two, processPhase6 and processPhase7. That should be a little simpler now that we have done the first one, but it’s still a bit of work, so let’s save all of that for our next installment.

Collaboration Store, Part LX

Posted on | by snhackery

“Failure is simply the opportunity to begin again, this time more intelligently.”
— Henry Ford

Last time, I had to confess that the code that I put out didn’t actually work. At the time, I had tried several things to make it work, but none of those were successful. Since then, I have tried quite a few other things, but none of those were successful, either. Eventually, I had to actually read the documentation, which helps quite a bit, but for some reason, always seems to be my tactic of last resort. Anyway, as it turns out, I only had to make one small change to get the logo image to actually appear on the other side intact. This line from my original attempt:

request.setRequestBody(gsa.getContentBase64(attachmentGR));

… just had to be changed to this:

request.setRequestBodyFromAttachment(attachmentGR.getUniqueValue());

The setRequestBodyFromAttachment method of the sn_ws.RESTMessageV2 object accepts the sys_id of the attachment as an argument and does all of the heavy lifting of building the request body from the attachment file. Once I replaced the setRequestBody method with the setRequestBodyFromAttachment method, everything worked great. So that takes care of that little problem. Now, where were we?

Now that we have a working function to push over the images for both instances and applications, we need to go into the functions that push over the instances and applications and add a call to this function. Here is the common function created to push over an instance.

pushInstance: function(instanceGR, targetGR) {
	var result = {};

	var payload = {};
	payload.instance = instanceGR.getDisplayValue('instance');
	payload.name = instanceGR.getDisplayValue('name');
	payload.description = instanceGR.getDisplayValue('description');
	payload.email = instanceGR.getDisplayValue('email');
	payload.accepted = instanceGR.getDisplayValue('accepted');
	payload.active = instanceGR.getDisplayValue('active');
	payload.host = instanceGR.getDisplayValue('host');
	result.url = 'https://' + targetGR.getDisplayValue('instance') + '.service-now.com/api/now/table/x_11556_col_store_member_organization';
	result.method = 'POST';
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint(result.url);
	request.setHttpMethod(result.method);
	request.setBasicAuth(this.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
	request.setRequestHeader('Content-Type', 'application/json');
	request.setRequestHeader('Accept', 'application/json');
	request.setRequestBody(JSON.stringify(payload, null, '\t'));
	var response = request.execute();
	result.status = response.getStatusCode();
	result.body = response.getBody();
	if (result.body) {
		try {
			result.obj = JSON.parse(result.body);
		} catch (e) {
			result.parse_error = e.toString();
		}
	}
	result.error = response.haveError();
	if (result.error) {
		result.error_code = response.getErrorCode();
		result.error_message = response.getErrorMessage();
	}
	this.logRESTCall(targetGR, result, payload);

	return result;
}

As we did within the pushImageAttachment function, we can add an else to the if (result.error) condition and check to see if we need to send over the image. In this instance, not only do we need to make sure that the instance record was successfully sent over to the target instance, we also need to check if the instance record actually has a logo image. If it does, then we need to grab the image attachment record so that we can use it to make the call to the pushImageAttachment function.

result.error = response.haveError();
if (result.error) {
	result.error_code = response.getErrorCode();
	result.error_message = response.getErrorMessage();
} else {
	if (instanceGR.getValue('logo')) {
		if (result.status == '201' && result.obj) {
			var attachmentGR = new GlideRecord('sys_attachment');
			attachmentGR.get(instanceGR.getValue('logo'));
			this.pushImageAttachment(attachmentGR, targetGR, 'x_11556_col_store_member_organization', result.obj.result.sys_id);
		}
	}
}

For an application record, things get a little more complicated, as that can be either an insert or an update. Since the application record might already exist on the target system, not only do we need to make sure that the application has a logo image on the source instance, but we also need to check to make sure that the image doesn’t already exist on the target instance. Here is the code that I came up with to add to the pushApplication function.

result.error = response.haveError();
if (result.error) {
	result.error_code = response.getErrorCode();
	result.error_message = response.getErrorMessage();
} else {
	if (applicationGR.getValue('logo') > '') {
		if ((result.status == '200' || result.status == '201') && result.obj) {
			if (!result.obj.result.logo) {
				var attachmentGR = new GlideRecord('sys_attachment');
				attachmentGR.get(applicationGR.getValue('logo'));
				this.pushImageAttachment(attachmentGR, targetGR, 'x_11556_col_store_member_application', result.obj.result.sys_id);
			}
		}
	}
}

With those changes, pushing an instance record will also push over the instance’s logo image and pushing an application record will also push over that application’s logo image. At least, that will happen if you are using the shared functions built for that purpose. We are not yet using those everywhere, though, so now might be a good time to fix that. We built those functions so that they could be called from various places as needed, but we never went back and refactored the code to actually do that in all places. That sounds like a good project for our next installment.

Collaboration Store, Part LVIII

Posted on | by snhackery

“Progress is not in enhancing what is, but in advancing toward what will be.”
— Khalil Gibran

Last time, we laid out all of the work that will need to be done to incorporate the new logo fields into the various processes of our application. Now we need to get busy doing that work. To begin, we can create a common function to move a logo image from one instance to another. We already have a common function to move an XML Update Set attachment from one instance to another, so let’s take a quick look at that guy and see if there is anything there that we can salvage for our new purpose.

pushAttachment: function(attachmentGR, targetGR, remoteVerId) {
	var result = {};

	var gsa = new GlideSysAttachment();
	result.url = 'https://';
	result.url += targetGR.getDisplayValue('instance');
	result.url += '.service-now.com/api/now/attachment/file?table_name=x_11556_col_store_member_application_version&table_sys_id=';
	result.url += remoteVerId;
	result.url += '&file_name=';
	result.url += attachmentGR.getDisplayValue('file_name');
	result.method = 'POST';
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint(result.url);
	request.setHttpMethod(result.method);
	request.setBasicAuth(this.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
	request.setRequestHeader('Content-Type', attachmentGR.getDisplayValue('content_type'));
	request.setRequestHeader('Accept', 'application/json');
	request.setRequestBody(gsa.getContent(attachmentGR));
	var response = request.execute();
	result.status = response.getStatusCode();
	result.body = response.getBody();
	if (result.body) {
		try {
			result.obj = JSON.parse(result.body);
		} catch (e) {
			result.parse_error = e.toString();
		}
	}
	result.error = response.haveError();
	if (result.error) {
		result.error_code = response.getErrorCode();
		result.error_message = response.getErrorMessage();
	}

	return result;
}

Since we want our new function to work for both instance logo images and application logo images, we will want to pass in both the table name and the table sys_id to our new function. Since the table name is part of the end point URL, we will want to change this:

result.url = 'https://';
result.url += targetGR.getDisplayValue('instance');
result.url += '.service-now.com/api/now/attachment/file?table_name=x_11556_col_store_member_application_version&table_sys_id=';
result.url += remoteVerId;
result.url += '&file_name=';
result.url += attachmentGR.getDisplayValue('file_name');

… to this:

result.url = 'https://';
result.url += targetGR.getDisplayValue('instance');
result.url += '.service-now.com/api/now/attachment/file?table_name=ZZ_YY';
result.url += tableName;
result.url += '&table_sys_id=';
result.url += tableSysId;
result.url += '&file_name=';
result.url += attachmentGR.getDisplayValue('file_name');

In addition to using the passed table name in the URL, we also prepend the string ZZ_YY to the value. This is a convention of the Now Platform to hide the attachment icon from the record for that image. When you manually add a logo image to a record and then go take a look at that image record in the sys_attachment table, you can see that the system has automatically prepended the ZZ_YY string to the table name. We want to our process to behave in the same manner, so we do that here as well.

The other difference between our logo image attachment and the XML Update Set attachment is that the XML content is in plain text and our image is stored in binary. Fortunately, the GlideSysAttachment object that we are using has a built-in way of handling that, so we just need to change this:

request.setRequestBody(gsa.getContent(attachmentGR));

… to this:

request.setRequestBody(gsa.getContentBase64(attachmentGR));

Other than these two changes, we should be able to use the rest of the original function intact. That makes our new function now look like this:

pushImageAttachment: function(attachmentGR, targetGR, tableName, tableSysId) {
	var result = {};

	var gsa = new GlideSysAttachment();
	result.url = 'https://';
	result.url += targetGR.getDisplayValue('instance');
	result.url += '.service-now.com/api/now/attachment/file?table_name=ZZ_YY';
	result.url += tableName;
	result.url += '&table_sys_id=';
	result.url += tableSysId;
	result.url += '&file_name=';
	result.url += attachmentGR.getDisplayValue('file_name');
	result.method = 'POST';
	var request = new sn_ws.RESTMessageV2();
	request.setEndpoint(result.url);
	request.setHttpMethod(result.method);
	request.setBasicAuth(this.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
	request.setRequestHeader('Content-Type', attachmentGR.getDisplayValue('content_type'));
	request.setRequestHeader('Accept', 'application/json');
	request.setRequestBody(gsa.getContentBase64(attachmentGR));
	var response = request.execute();
	result.status = response.getStatusCode();
	result.body = response.getBody();
	if (result.body) {
		try {
			result.obj = JSON.parse(result.body);
		} catch (e) {
			result.parse_error = e.toString();
		}
	}
	result.error = response.haveError();
	if (result.error) {
		result.error_code = response.getErrorCode();
		result.error_message = response.getErrorMessage();
	}

	return result;
}

That still does not complete the process, however. In addition to creating the attachment record linked to the base record, the logo field on the base record contains the sys_attachment record sys_id as the value. Once we create the sys_attachment record on the target system using the above function, we need to grab the resulting sys_id and update the logo field value on the base record. For that, we will need yet another function to make an additional REST API call to the target system to make that update. Since the new image field on both tables is named logo, we should again be able to create a single function that will work for both use cases. The payload that we will be sending just needs to contain the one value that we intend to update:

updateLogoField: function(attachmentId, targetGR, tableName, tableSysId) {
	var result = {};
 
	var payload = {};
	payload.logo = attachmentId;
	...
 
	return result;
}

For the URL, we will need to use both the passed table name and the passed table sys_id.

result.url = 'https://';
result.url += targetGR.getDisplayValue('instance');
result.url += '.service-now.com/api/now/table/';
result.url += tableName;
result.url += '/';
result.url += tableSysId;

… and the rest of the function is just our standard REST API call and result check:

updateLogoField: function(attachmentId, targetGR, tableName, tableSysId) {
	var result = {};
 
	var payload = {};
	payload.logo = attachmentId;
	result.url = 'https://';
	result.url += targetGR.getDisplayValue('instance');
	result.url += '.service-now.com/api/now/table/';
	result.url += tableName;
	result.url += '/';
	result.url += tableSysId;
	result.method = 'PUT';
	request  = new sn_ws.RESTMessageV2();
	request.setEndpoint(result.url);
	request.setHttpMethod(result.method);
	request.setBasicAuth(this.WORKER_ROOT + targetGR.getDisplayValue('instance'), targetGR.getDisplayValue('token'));
	request.setRequestHeader('Content-Type', 'application/json');
	request.setRequestHeader('Accept', 'application/json');
	request.setRequestBody(JSON.stringify(payload, null, '\t'));
	response = request.execute();
	result.status = response.getStatusCode();
	result.body = response.getBody();
	if (result.body) {
		try {
			result.obj = JSON.parse(result.body);
		} catch (e) {
			result.parse_error = e.toString();
		}
	}
	result.error = response.haveError();
	if (result.error) {
		result.error_code = response.getErrorCode();
		result.error_message = response.getErrorMessage();
	}
 
	return result;
}

Now all we have to do is call this function from our pushImageAttachment function, but only if all went well and the attachment was successfully sent over. We already have the targetGR, tableName, and tableSysId arguments available, but we will need to extract the remote system’s attachmentId from the response body of our call to send over the attachment. We are already checking for an error condition here:

result.error = response.haveError();
if (result.error) {
	result.error_code = response.getErrorCode();
	result.error_message = response.getErrorMessage();
}

… so we should be able to just add an else condition to that if statement to make the call.

result.error = response.haveError();
if (result.error) {
	result.error_code = response.getErrorCode();
	result.error_message = response.getErrorMessage();
} else {
	if (result.status == '201' && result.obj) {
		this.updateLogoField(result.obj.result.sys_id, targetGR, tableName, tableSysId);
	}
}

So now we have a common function that will send over the logo image for both instance records and application records, and then update the base record with attachment’s local sys_id. That’s a good start, but there is lots more to do, so we will keep plowing ahead next time out.

Refactoring the SNH Data Table Widget

Posted on | by snhackery

“Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.”
— William A Foster

After I posted the SNH Data Table widget collection out on Share, I realized that there were a couple more features that really needed to be in there before it could truly be considered complete, so I started working on adding one of those features, the ability to click on an aggregate column value and see the records represented by that number. While testing out those new additions to the code, I discovered that I was not being consistent in the way in which these various features were being implemented, and decided to take a step back and review the entire set of additional features as a group. I don’t like to see one feature implemented one way and another, similar feature implemented some other way. Just to review, the SNH version of the Data Table widget adds the following configurable features:

  • Reference Field Links – For any field with a type of reference, the value is rendered as a link which can branch to another Portal Page or broadcast the details to a companion widget sharing the page.
  • Aggregate Columns – Aggregate columns are counts of related records, which can also now be rendered as a link which can branch to another Portal Page or broadcast the details to a companion widget sharing the page.
  • Buttons and Icons – Buttons and icons can also be configured, and clicking on a button or icon will branch to another Portal Page or broadcast the details to a companion widget sharing the page.
  • Bulk Actions – When one or more bulk actions are configured, checkboxes will be rendered at the beginning of each line, and a drop-down select list of actions will be rendered in the footer. Checking one or more rows of the table and selecting an action from the drop-down will branch to another Portal Page or broadcast the details to a companion widget sharing the page.

Although each of these features were unique, and developed for a specific purpose over a period of time, they all share a number of characteristics in common, particularly the fact that selecting an item will broadcast a message alerting other widgets of the selection and/or branch out to a different page on the portal. It seemed to me that, even though each feature had its own way of approaching this, and even though they all seemed to work just the way that they were, they all really needed to approach things in a consistent manner. So I decided to go back and review the various implementations and see if I could come up with a way for all of them to be handled in the same manner.

For the reference field links, I had leveraged the existing go() function from the original widget. For the buttons and icons, I had built a new function, which I also did later for the aggregate columns, but using a different approach. For the bulk actions, I also had a separate function, but it is a little different than the other three, as it deals with multiple records and the others all deal with a single record. Still, all four had some very similar characteristics.

The original go() function did a $scope.emit(), and then code in the wrapper widgets listened for that event and did the actual branching to the new page. I don’t want to disturb that existing code, as I would like the enhanced widget to be drop-in compatible with the original, but I don’t really see the need to involve the wrapper widgets in any of the things that I was doing. I had already done that in a few places by copying what was already there, but now that I look at it a little more closely, I would like to rip all of that out and handle everything in the core widget alone.

The easiest way to make sure that everyone uses the same approach is for everyone to share the same code. So I came up with a universal function that would take care of the broadcasting and the navigation for all four use cases, which turned out like this:

function processClick(sys_id, eventName, config, item) {
	spNavStateManager.onRecordChange(c.data.table).then(function() {
		var parms = {};
		parms.table = config.table || c.data.table;
		parms.sys_id = sys_id;
		parms.record = item;
		parms.config = config;
		$scope.ignoreLocationChange = true;
		for (var x in c.data.list) {
			c.data.list[x].selected = false;
		}
		$rootScope.$broadcast(eventName, parms);
		if (config.page_id) {
			var s = {id: config.page_id, table: parms.table, sys_id: parms.sys_id, view: $scope.data.view};
			var newURL = $location.search(s);
			spAriaFocusManager.navigateToLink(newURL.url());
		}
	});
}

The function accepts four arguments, sys_id, eventName, config, and item. The sys_id is the sys_id of the record, either the related record or the record of the active row. The eventName is one of the following:

referenceClick: 'data_table.referenceClick',
aggregateClick: 'data_table.aggregateClick',
buttonClick: 'data_table.buttonClick',
bulkAction: 'data_table.bulkAction'

The config is the configuration object for the reference, aggregate, button, icon, or bulk action, and the item is either the record for the current row, the related record, or in the case of bulk actions, the list of selected records. The function extracts certain elements from the passed arguments, broadcasts the message, and then if there is a portal page specified, branches to that page with URL parameters extracted from the data passed. This will now occur in the same manner, using the same technique for all four of the added features.

To invoke this function, I created four new functions, one for each of the four features. These functions establish the arguments in accordance with the needs of each feature. Here are the four new functions:

$scope.referenceClick = function(field, item) {
	var config = {};
	config.table = item[field].table;
	config.page_id = c.data.refmap[config.table] || 'form';
	var sys_id = item[field].value;
	processClick(sys_id, eventNames.referenceClick, config, item[field].record);
};

$scope.aggregateClick = function(aggregate, item) {
	var config = {};
	for (var a in c.data.aggarray) {
		if (c.data.aggarray[a].name == aggregate) {
			config = c.data.aggarray[a];
		}
	}
	var sys_id = item.sys_id;
	if (config.source && item[config.source].value) {
		sys_id = item[config.source].value;
	}
	processClick(sys_id, eventNames.aggregateClick, config, item);
};

$scope.buttonClick = function(button, item) {
	var config = {};
	for (var b in c.data.btnarray) {
		if (c.data.btnarray[b].name == button) {
			config = c.data.btnarray[b];
		}
	}
	processClick(item.sys_id, eventNames.buttonClick, config, item);
};

$scope.bulkActionSelected = function() {
	if (c.data.bulk_action) {
		var selected = [];
		for (var x in c.data.list) {
			if (c.data.list[x].selected) {
				selected.push(c.data.list[x]);
			}
		}
		if (parms.selected.length > 0) {
			var config = {};
			for (var b in c.data.actarray) {
				if (c.data.actarray[b].name == c.data.bulk_action) {
					config = c.data.actarray[b];
				}
			}
			config.table = c.data.table
			processClick('', eventNames.bulkAction, config, selected)
		} else {
			spModal.alert('You must select at least one row for this action');
		}
	}
	c.data.bulk_action = '';
};

In addition to wanting to consistently handle the various actions, I also wanted to run out and get the latest copy of the original Data Table widget to make sure that I had not missed any useful updates since I initially cloned the widget. At the same time, I wanted to start fresh with the latest copy and then surgically insert the enhancements, disturbing the original code as little as possible, and marking my additions with comments to clearly delineate the new code from the original. Here is how that turned out with the HTML template:

<div class="panel panel-{{options.color}} b" ng-class="{'data-table-high-contrast': accessibilityModeEnabled}">
    <div class="panel-heading form-inline" ng-hide="options.hide_header">
      <span class="dropdown m-r-xs">
        <button aria-label="{{data.title || data.table_plural}} ${Context Menu}" class="btn dropdown-toggle glyphicon glyphicon-menu-hamburger" style="line-height: 1.4em" id="optionsMenu" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false"></button>
        <ul class="dropdown-menu" aria-labelledby="optionsMenu">
          <li ng-repeat="t in ::exportTypes">
            <a ng-if="!tinyUrlEnabled" ng-href="/{{data.table}}_list.do?{{::t.value}}&sysparm_query={{data.exportQueryEncoded}}&sysparm_view={{data.view}}&sysparm_fields={{data.fields}}" target="_new" tabindex="-1">${Export as} {{::t.label}}</a>
            <a ng-if="tinyUrlEnabled" ng-href="/{{data.table}}_list.do?{{::t.value}}&sysparm_tiny={{tinyUrl}}" target="_new" tabindex="-1">${Export as} {{::t.label}}</a>
          </li>
        </ul>
      </span>
      <h2 class="panel-title" style="display:inline"><i ng-if="options.glyph" class="fa fa-{{options.glyph}} m-r"></i>{{data.title || data.table_plural}}<span class="sr-only">${table} - ${page} {{data.p}}</span></h2>
      <button name="new" role="button" class="btn btn-primary btn-sm m-l-xs" ng-click="newRecord()" ng-if="options.show_new && data.canCreate && !data.newButtonUnsupported" aria-label="${Create new record}">${New}</button>
      <div class="pull-right" ng-if="options.show_keywords">
		<form ng-if="data.hasTextIndex" ng-submit="setSearch(true)">
        <div class="input-group" role="presentation">
          <input type="text" name="datatable-search" ng-model="data.keywords" ng-model-options="{debounce:250}" class="form-control" placeholder="${Keyword Search}" aria-label="${Keyword Search}">
          <span class="input-group-btn">
            <button name="search" class="btn btn-default" type="submit" aria-label="${Search}"><span class="glyphicon glyphicon-search"></span></button>
          </span>
        </div>
        </form>
      </div>
      <div class="clearfix"></div>
    </div>
    <!-- body -->
    <div class="panel-body">
      <div ng-if="options.show_breadcrumbs && (data.filter || data.enable_filter)" class="filter-breadcrumbs">
	    		<sp-widget widget="data.filterBreadcrumbs"></sp-widget>
      </div>
      <div class="clearfix"></div>
      <div class="alert alert-info" ng-if="!data.list.length && !data.num_pages && !data.invalid_table && !loadingData">
        ${No records in {{data.table_label}} <span ng-if="data.filter">using that filter</span>}
      </div>
      <div class="alert alert-info" ng-if="loadingData">
          <fa name="spinner" spin="true"></fa> ${Loading data}...
       </div>
      <table class="table table-striped table-responsive" ng-if="data.list.length">
        <caption class="sr-only">{{data.title || data.table_plural}}</caption>
        <thead>
          <tr>
<!-- Start: SNH Data Table enhancements -->
            <th ng-if="data.actarray.length > 0" class="text-nowrap center" tabindex="0">
              <input type="checkbox" ng-model="data.master_checkbox" ng-click="masterCheckBoxClick();"/>
            </th>
<!-- End: SNH Data Table enhancements -->
            <th ng-repeat="field in data.fields_array track by $index" class="text-nowrap" ng-click="setOrderBy(field)"
             scope="col" role="columnheader" aria-sort="{{field == data.o ? (data.d == 'asc'? 'ascending': 'descending') : 'none'}}">
              <div class="th-title" title="${Sort by} {{field == data.o ? (data.d == 'asc' ?  '${Descending}': '${Ascending}') : '${Ascending}'}}" role="button" tabindex="0" aria-label="{{data.column_labels[field]}}">{{data.column_labels[field]}}</div>
              <i class="fa" ng-if="field == data.o" ng-class="{'asc': 'fa-chevron-up', 'desc': 'fa-chevron-down'}[data.d]"></i>
            </th>
<!-- Start: SNH Data Table enhancements -->
            <th ng-repeat="aggregate in data.aggarray" class="text-nowrap center" tabindex="0">
              {{aggregate.heading || aggregate.label}}
            </th>
            <th ng-repeat="button in data.btnarray" class="text-nowrap center" tabindex="0">
              {{button.heading || button.label}}
            </th>
<!-- End: SNH Data Table enhancements -->
          </tr>
        </thead>
        <tbody>
        <tr ng-repeat="item in data.list track by item.sys_id">
<!-- Start: SNH Data Table enhancements -->
           <td ng-if="data.actarray.length > 0" role="cell" class="text-nowrap center" tabindex="0">
              <input type="checkbox" ng-model="item.selected"/>
            </td>
            <td role="{{$first ? 'rowheader' : 'cell'}}" class="sp-list-cell" ng-class="{selected: item.selected}" ng-repeat="field in ::data.fields_array" data-field="{{::field}}" data-th="{{::data.column_labels[field]}}">
              <sn-avatar ng-if="item[field].value && item[field].type == 'reference' && item[field].table == 'sys_user'" primary="item[field].value" class="avatar-small" show-presence="true" enable-context-menu="false"></sn-avatar>
              <a ng-if="$first" href="javascript:void(0)" ng-click="go(item.targetTable, item)" aria-label="${Open record}: {{::item[field].display_value}}">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</a>
              <a ng-if="!$first && item[field].type == 'reference' && item[field].value" href="javascript:void(0)" ng-click="referenceClick(field, item)" aria-label="${Click for more on }{{::item[field].display_value}}">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</a>
              <span ng-if="!$first && item[field].type != 'reference'">{{::item[field].display_value | limitTo : item[field].limit}}{{::item[field].display_value.length > item[field].limit ? '...' : ''}}</span>
            </td>
            <td ng-repeat="obj in item.aggValue" role="cell" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
              <a ng-if="obj.name" href="javascript:void(0)" ng-click="aggregateClick(obj.name, item)">{{obj.value}}</a>
              <span ng-if="!obj.name">{{obj.value}}</span>
            </td>
            <td ng-repeat="button in data.btnarray" role="cell" class="text-nowrap center" ng-class="{selected: item.selected}" tabindex="0">
              <a ng-if="!button.icon" href="javascript:void(0)" role="button" class="btn-ref btn btn-{{button.color || 'default'}}" ng-click="buttonClick(button.name, item)" title="{{button.hint}}" data-original-title="{{button.hint}}">{{button.label}}</a>
              <a ng-if="button.icon" href="javascript:void(0)" role="button" class="btn-ref btn btn-{{button.color || 'default'}}" ng-click="buttonClick(button.name, item)" title="{{button.hint}}" data-original-title="{{button.hint}}">
                <span class="icon icon-{{button.icon}}" aria-hidden="true"></span>
                <span class="sr-only">{{button.hint}}</span>
              </a>
            </td>
<!-- End: SNH Data Table enhancements -->
          </tr>
        </tbody>
      </table>
      <div ng-class="{'pruned-msg-filter-pad': (!options.show_breadcrumbs || !data.filter) && !data.list.length}" class="pruned-msg" ng-if="rowsWerePruned()">
        <span ng-if="rowsPruned == 1">${{{rowsPruned}} row removed by security constraints}</span>
        <span ng-if="rowsPruned > 1">${{{rowsPruned}} rows removed by security constraints}</span>
      </div>
    </div>
    <!-- footer -->
 <!-- Start: SNH Data Table enhancements -->
    <div class="panel-footer" ng-if="data.actarray.length > 0 && data.row_count">
      <div class="btn-toolbar m-r pull-left">
        <select class="form-control" ng-model="data.bulk_action" ng-click="bulkActionSelected();">
          <option value="">${Actions on selected rows ...}</option>
          <option ng-repeat="opt in data.actarray" value="{{opt.name}}">{{opt.label}}</option>
        </select>
      </div>
      <span class="clearfix"></span>
    </div>
<!-- End: SNH Data Table enhancements -->
    <div class="panel-footer" ng-hide="options.hide_footer" ng-if="data.row_count" role="navigation" aria-label="${Pagination}">
      <div class="btn-toolbar m-r pull-left">
        <div class="btn-group">
          <a href="javascript:void(0)" ng-click="setPageNum(data.p - 1)" ng-class="{'disabled': data.p == 1}" class="btn btn-default" aria-label="${Previous page} {{data.p == 1 ? '${disabled}' : ''}}" tabindex="{{(data.p == 1) ? -1 : 0}}"><i class="fa fa-chevron-left"></i></a>
        </div>
        <div ng-if="data.num_pages > 1 && data.num_pages < 20" class="btn-group">
          <a ng-repeat="i in getNumber(data.num_pages) track by $index" ng-click="setPageNum($index + 1)" href="javascript:void(0)" ng-class="{active: ($index + 1) == data.p}" type="button" class="btn btn-default" aria-label="${Page} {{$index + 1}}" ng-attr-aria-current="{{($index + 1) == data.p ? 'page' : undefined}}">{{$index + 1}}</a>
        </div>
        <div class="btn-group">
          <a href="javascript:void(0)" ng-click="setPageNum(data.p + 1)" ng-class="{'disabled': data.p == data.num_pages}" class="btn btn-default" aria-label="${Next page} {{data.p == data.num_pages ? '${disabled}' : ''}}" tabindex="{{(data.p == data.num_pages) ? -1 : 0}}"><i class="fa fa-chevron-right"></i></a>
        </div>
      </div>
      <div class="m-t-xs panel-title">${Rows {{data.window_start + 1}} - {{ mathMin(data.window_end,data.row_count) }} of {{data.row_count}}}</div>

      <span class="clearfix"></span>
    </div>
  </div>

All in all, I had to insert code in four different places, twice in the table header, once for the table rows, and once for the bulk action drop-down in the table footer. Everything else is just as it was in the original HTML template from the latest version of the widget. This I like much better than the way that I had it before, and each block of code that I inserted is clearly marked with a Start and an End comment. I took the same approach with the server script and the client script, attempting wherever possible to leave as much as the original work in place and marking those sections where I inserted additions.

Now that that is done, I need to go back into the wrapper widgets and basically do the same thing after I remove all of the code that is now being handled in the core widget. Once that is done, there is still one more thing that I would like to do before releasing another Update Set, but I need to finish up those wrapper widgets first before we jump into that.

Aggregate List Columns, Part X

Posted on | by snhackery

“You’re always trying to get better. You’re always tinkering. You’re always learning new things.”
— Kyle Korver

The previous installment in this series was supposed to be the last, as I wanted to get back to the Collaboration Store project that was just left hanging after the call for outside testing assistance. But then I decided to post the Update Set out on Share, and now that it is out there, it really disturbs my sense of The Way Things Ought To Be that you cannot click on an aggregate column and pull up a list of the records represented in that number. It doesn’t seem like it would take much to add that feature, and I am not sure why I never included it in the first place, but now that it is out there for all of the world to see, I feel as if I need to correct that omission. So here we go.

There are a couple of different ways to show a list of the records. One would be to simply link to a new page passing the sys_id of the row and the name of the table. Another would be some kind of modal pop-up containing a simple list or data table containing the records. To provide for all of these possibilities, we could add yet one more property to the configuration object for an aggregate column that we could call action. The value of the property could either be a URL query string for the new page, or the word broadcast, which would send out a broadcast message on click so that a companion widget could handle the modal dialog independently, outside the scope of the data table widget. We already have examples of this with the buttons and icons, and this would embrace that same strategy.

The easiest place to start with such a strategy would be with the Content Selector Configuration Editor, adding the new property to the table of aggregate column specifications as well as the pop-up aggregate column specification editor. Let’s start with the main widget and add the following to the list of column headings for that section:

<th style="text-align: center;">${Action}</th>

And then in the repeating rows of that table, let’s insert this line:

<td data-th="${Action}">{{agg.action}}</td>

On the server side, in the Save() function that rebuilds the script, let’s add these lines in the aggregate column specification section:

 script += "',\n                 action: '";
 script += thisAggregate.action;

Finally, on the client side, let’s add the following line to the new record section of the editAggregate() function:

shared.action = aggregate.action;

… and this line to the section that saves the edits in that same function:

aggregate.action = shared.action || '';

That takes care of the main widget, but we also need to add a new input field to the pop-up editor before this will actually work, so we need to add these lines to the HTML of the Aggregate Column Editor widget:

<snh-form-field
  snh-model="c.widget.options.shared.action"
  snh-name="action"/>

That updates the editor to now include a new aggregate column specification property called action. Of course, that doesn’t actually add any functionality to the Data Table widgets just yet, but at least now we can add a value to that property through the editor, which is a start. Now let’s take a look at the actual Data Table widgets and see what we need to do in order to leverage that new property.

Once again, the easiest place to start is with the HTML. Here is the current section of the HTML that deals with aggregate columns:

<td ng-repeat="obj in item.aggValue" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
  {{obj.value}}
</td>

Let’s replace that with this:

<td ng-repeat="obj in item.aggValue" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
  <a ng-if="obj.action" href="javascript:void(0)" ng-click="aggregateclick(obj.name, item)">{{obj.value}}</a>
  <span ng-if="!obj.action">{{obj.value}}</span>
</td>

Our new code now contains two mutually exclusive options, one an anchor and the other a span, which are rendered depending on whether or not there is a value in the new action property. The anchor tag does not directly launch the new page, which is only one of the possible responses to an action, but rather invokes a client-side script, passing the name of the aggregate column and the object containing the data for the row. Here is the code for this new client-side function:

$scope.aggregateclick = function(aggregate, item) {
	spNavStateManager.onRecordChange(c.data.table).then(function() {
		var parms = {};
		parms.table = c.data.table;
		parms.sys_id = item.sys_id;
		parms.record = item;
		for (var a in c.data.aggarray) {
			if (c.data.aggarray[a].name == aggregate) {
				parms.aggregate = c.data.aggarray[a];
			}
		}
		$scope.ignoreLocationChange = true;
		for (var x in c.data.list) {
			c.data.list[x].selected = false;
		}
		item.selected = true;
		$scope.$emit(eventNames.aggregateClick, parms);
		if (parms.aggregate.action && parms.aggregate.action != 'broadcast') {
			if (!parms.aggregate.action.startsWith('?')) {
				parms.aggregate.action = '?' + parms.aggregate.action;
			}
			if (parms.aggregate.action.indexOf('{{sys_id}}')) {
				parms.aggregate.action = parms.aggregate.action.replace('{{sys_id}}', parms.sys_id);
			}
			window.location.search = parms.aggregate.action;
		}
	}, function() {
		// do nothing in case of closing the modal by clicking on x
	});	
};

This function is basically a copy of the existing buttonclick function, adapted for use in the aggregate column section. In all cases where an action has been specified, the click information is broadcast to all of the other widgets on the page. Additionally, if the action is not the word broadcast, it is assumed to be a URL search string, and the window.location.search property is updated with the resolved value, invoking the new portal page.

The last thing that we need to do is to add a couple of new properties to the object returned by the getAggregateValue function on the server side. To support the new HTML, we just need to change this:

return {value: value};

… to this:

return {value: value, name: config.name, action: config.action};

That should do it for the changes. Now all we have to do is to build some test cases for both the new page and the pop-up dialog options to see if this all works. That sounds like a good project for our next installment.

Aggregate List Columns, Part V

Posted on | by snhackery

“The trouble you’re expecting never happens; it’s always something that sneaks up the other way.”
— George R. Stewart

Last time, we took a little side trip on our journey to add this new functionality to the SNH Data Table Widget collection, but now we need to get back on track and finish up modifying the other wrapper widgets that still need to have some changes. Let’s start with the SNH Data Table from Instance Definition, which does not rely on an outside configuration object. When you configure this widget, you use the Edit feature of the Service Portal Page Designer. The edit dialog box that comes up can be customized for a widget using the Option schema field on the widget form, which already contains a number of customizations for the existing version of the widget. Here is how that looks right now:

[{"hint":"If enabled, show the list filter in the breadcrumbs of the data table",
"name":"enable_filter",
"default_value":"false",
"section":"Behavior",
"label":"Enable Filter",
"type":"boolean"},
{"hint":"A JSON object containing the specification for row-level buttons and action icons",
"name":"buttons",
"default_value":"",
"section":"Behavior",
"label":"Buttons",
"type":"String"},
{"hint":"A JSON object containing the page id for any reference column links",
"name":"refpage",
"default_value":"",
"section":"Behavior",
"label":"Reference Pages",
"type":"String"}]

In the current version, there is a string field for entering a JSON specification object for both Buttons/Icons and Reference Pages, so we will want to add one more just like that for our new aggregate columns. We can clean up the labels for all three while we are at it, just to make things a little clearer, so now it looks like this:

[{"hint":"If enabled, show the list filter in the breadcrumbs of the data table",
"name":"enable_filter",
"default_value":"false",
"section":"Behavior",
"label":"Enable Filter",
"type":"boolean"},
{"hint":"A JSON object containing the specifications for aggregate data columns",
"name":"aggregates",
"default_value":"",
"section":"Behavior",
"label":"Aggregate Column Specifications (JSON)",
"type":"String"},
{"hint":"A JSON object containing the specifications for row-level buttons and action icons",
"name":"buttons",
"default_value":"",
"section":"Behavior",
"label":"Button/Icon Specifications (JSON)",
"type":"String"},
{"hint":"A JSON object containing the page id for any reference column links",
"name":"refpage",
"default_value":"",
"section":"Behavior",
"label":"Reference Page Specifications (JSON)",
"type":"String"}]

Looking over the rest of the code, it doesn’t look as if there is anything else that needs to be done, so the next thing that we need to do is to set up a test so that we can try it out. Once again, we can clone the original test page and then make our modifications to the cloned page. The first thing that we will need to do is to swap out the SNH Data Table from JSON Configuration widget with our modified SNH Data Table from Instance Definition widget. Once we do that, we can click on the Edit pencil in the Page Designer and fill out the modified form.

Top half of the Page Designer widget option editor

For this test, I decided to use the Group table instead of the User table, and for our little test, I decided to limit the list to just ITIL groups. The type field can be more than one type, though, so I had to use a CONTAINS filter rather than an = filter, and the values are sys_ids, not names, so I had to look up the sys_id for the ITIL type. I just added a couple of fields, Name and Manager, and then ordered the list by Name. That pretty much took care of the top half of the widget option editor, so then I scrolled down to the bottom half, where our customizations appear.

Bottom half of the Page Designer widget option editor

Here I left the default color, selected an appropriate glyph icon for a group, and left the Link to this page and Enable Filter fields intentionally blank. In the Aggregate Column Specifications field, I entered a JSON String defining three different aggregate columns:

[{
 "label": "Members",
 "name": "members",
 "heading": "Members",
 "table": "sys_user_grmember",
 "field": "group",
 "filter": "user.active=true"
},{
 "label": "Incidents",
 "name": "incidents",
 "heading": "Incidents",
 "table": "incident",
 "field": "assignment_group",
 "filter": "active=true"
},{
 "label": "Catalog Tasks",
 "name": "sc_tasks",
 "heading": "Catalog Tasks",
 "table": "sc_task",
 "field": "assignment_group",
 "filter": "active=true"
}]

I did not define any buttons or icons, but I did set up a reference configuration to send the links on the managers to the User Profile page. All that was left to do at that point was to Save the options, jump out to the Service Portal, and give this baby a try.

First test of the new widget modifications

Now that’s disturbing. There is no data in any of the aggregate columns. I hate it when that happens! Now what? Well, I always try to keep this in mind whenever these things suddenly crop up.

After quite a few trials and tribulations, I finally tracked down the source of the problem. The ng-repeat for the aggregate columns is actually nested underneath another ng-repeat for the rows in the table. Apparently, ng-repeat has some issues with arrays of primitives or strings, and prefers to deal with objects. Why that did not present itself when I was working with an array length of 1 earlier, and only now caused a problem with an array length of 3 is still a mystery to me. Fortunately, I never need to know or understand the why; I just need to know what to do about it. The solution was to convert my integer value to an object containing an integer value. So, in the core SNH Data Table widget, I changed this:

return value;

… to this:

return {value: value};

Of course, that meant that I also had to change the HTML accordingly, so I changed this:

<td ng-repeat="aggValue in item.aggValue" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
  {{aggValue}}
</td>

… to this:

<td ng-repeat="obj in item.aggValue" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
  {{obj.value}}
</td>

Now let’s have another look at that new test page.

Second test of the new widget modifications

That’s better! OK, so that takes care of wrapper widget #2. Now we just need to handle the third wrapper widget, SNH Data Table from URL Definition. That sounds like a good project for our next installment.

Aggregate List Columns, Part IV

Posted on | by snhackery

“Everything can be improved.”
— Clarence W. Barron

Last time, I promised that we would get into providing aggregate column support to the remaining data table wrapper widgets and the Content Selector Configuration Editor, but before we do that, there is one little extra feature that I would like insert. As you may recall, when I set up the filter for the initial test page, I hard-coded the list of sys_ids for the members of the Hardware group. It would have been much easier to use the sys_user_grmember table and set the filter to group.name=Hardware, but the problem with that would be that the sys_id of the row would then be the sys_id of the sys_user_grmember record, and not the sys_id of the sys_user record. The reason that that is a problem is because there are no Incidents assigned to sys_user_grmember records, so the aggregate column values would all be 0.

There is, however, a way to make that work. We just need to add one more property to our aggregate column configuration object. If we add an optional source property, then we can allow developers to configure an alternate source for the sys_id rather than just relying on the sys_id of the primary record of the row. It could be optional, as we could always default to the sys_id of the row if you did not specify an alternate source, but if you did enter a value, then the contents of that field would be the sys_id that we would use in place of the standard behavior. Using that approach, we could develop a configuration that could look like this:

name: 'sys_user_grmember',
displayName: 'User',
all: {
	filter: 'group.name=Hardware',
	fields: 'group,user,user.title,user.email,user.department,user.location',
	aggarray: [{
		label: 'Incidents',
		name: 'incidents',
		heading: 'Incidents',
		table: 'incident',
		field: 'assigned_to',
		filter: 'active=true',
		source: 'user'
	}],
	btnarray: [],
	refmap: {},
	actarray: []
}

Of course, in order to do that, we would have to add some additional logic inside the for loop that puts together the data to be sent to the getAggregateValue function, but the changes should not be that significant. I simply created a new variable called sysId, set it initially to the sys_id of the record, then checked to see if there was a source defined, and if so, overrode the initial value with the value found in the source field of the current record.

for (var j=0; j<data.aggarray.length; j++) {
	var config = data.aggarray[j];
	var sysId = record.sys_id;
	if (config.source) {
		sysId = gr.getValue(config.source);
	}
	record.aggValue.push(getAggregateValue(sysId, config));
}

That’s all there is to that. Now we just need to put together a test case to see if it will actually work. Rather than muck up my original test page, I decided to clone the page and the configuration script so that I could run tests both with and without a source defined. I called my copy of the configuration script AggregateTestConfig2, and it turned out like this:

var AggregateTestConfig2 = Class.create();
AggregateTestConfig2.prototype = Object.extendsObject(ContentSelectorConfig, {
	initialize: function() {
	},

	perspective: [{
		name: 'all',
		label: 'All',
		roles: ''
	}],

	state: [{
		name: 'all',
		label: 'All',
	}],

	table: {
		all: [{
			name: 'sys_user_grmember',
			displayName: 'User',
			all: {
				filter: 'group.name=Hardware',
				fields: 'group,user,user.title,user.email,user.department,user.location',
				aggarray: [{
					label: 'Incidents',
					name: 'incidents',
					heading: 'Incidents',
					table: 'incident',
					field: 'assigned_to',
					filter: 'active=true',
					source: 'user'
				}],
				btnarray: [],
				refmap: {},
				actarray: []
			}
		}]
	},

	type: 'AggregateTestConfig2'
});

Now all we have to do is pull up the new test page and see how it looks.

Test page #2 with alternate sys_id source feature

So now you can define an alternate source for the sys_id that drives the aggregate, or you can choose not to and have the original functionality remain intact. Sweet! I like it. We still don’t have a way to click on the aggregate and pull up a list of the records represented there, but that’s something that we can always add in a future version. For now, we still have work to do involving the remainder of the core data table widget wrappers and the configuration file editor. I know that I said this last time, but this time for sure, we will jump right into that next time out.

Aggregate List Columns, Part III

Posted on | by snhackery

“I want to try it to see what it’s like and see what my stuff looks like when I take it from inception to completion.”
— Charlie Kaufman

Last time, we started the work of modifying the core widget of the data table widget collection. We took care of all of the code that imports the configuration data from the various wrapper widgets in the collection, and now we need to get down to the business of actually putting real data in the new aggregate list columns. To begin, we need to take a look at the code that pulls in the regular data for each row in the list.

	data.list = [];
	while (gr._next()) {
		var record = {};
		$sp.getRecordElements(record, gr, data.fields);
		if (typeof FilteredGlideRecord != 'undefined' && gr instanceof FilteredGlideRecord) {
			// FilteredGlideRecord doesn't do field-level
			// security, so take care of that here
			for (var f in data.fields_array) {
				var fld = data.fields_array[f];
				if (!gr.isValidField(fld))
					continue;

				if (!gr[fld].canRead()) {
					record[fld].value = null;
					record[fld].display_value = null;
				}
			}
		}
		for (var f in data.fields_array) {
			var fld = data.fields_array[f];
			if (record[fld].type == 'reference') {
				var refGr = gr;
				var refFld = fld;
				if (fld.indexOf('.') != -1) {
					var parts = fld.split('.');
					for (var x=0;x<parts.length-1;x++) {
						refGr = refGr[parts[x]].getRefRecord();
					}
					refFld = parts[parts.length-1];
				}
				if (refGr.isValidField(refFld)) {
					record[fld].table = refGr.getElement(refFld).getED().getReference();
					record[fld].record = {type: 'reference', sys_id: {value: record[fld].value, display_value: record[fld].value}, name: {value: record[fld].display_value, display_value: record[fld].display_value}};
				}
			}
		}
		record.sys_id = gr.getValue('sys_id');
		record.targetTable = gr.getRecordClassName();
		data.list.push(record);
	}

Basically, this code creates an array called data.list and then for each row creates an object called record, populates the record object from the GlideRecord, and then pushes the record object into the list. What we will want to do is add data to the record object before it gets pushed onto the list. The sys_id of the record will actually be useful to us, so it seems as if the best place to insert our code would be after the sys_id is established, but before the data.list.push(record) occurs. Here is what I came up with:

record.aggValue = [];
if (data.aggarray.length > 0) {
	for (var j=0; j<data.aggarray.length; j++) {
		record.aggValue.push(getAggregateValue(record.sys_id, data.aggarray[j]));
	}
}

To store the values, I added a value list to the record object. This is done whether or not any aggregate columns have been defined, just so there is something there regardless. Then we check to see if there actually are any aggregate columns defined, and if so, we loop through the definitions and then push a value onto the array for each definition. The value itself will be determined by a new function called getAggregateValue that takes the sys_id of the row and the aggregate column definition as arguments. We will need to build out that function, but for now, we can just return a hard-coded value, just to make sure that all is working before we dive into that.

function getAggregateValue(sys_id, config) {
	return 10;
}

Now that we have an array of values, we will need to go back into the HTML and modify the aggregate column section to pull data from this array. That section of the HTML now looks like this:

<td ng-repeat="aggValue in item.aggValue" class="text-right" ng-class="{selected: item.selected}" tabindex="0">
  {{aggValue}}
</td>

That should be enough to take it out for a spin and see if we broke anything. Let’s have a look.

Test page with hard-coded value array

Cool! So far, so good. Now we just need to go back into that getAggregateValue function definition and replace the hard-code value of 10 with some actual logic to pull the real data out of the database. For that, we will use a GlideAggregate on the configured table using the configured field and the sys_id of the current row. And if there is an optional filter present, we simply concatenate that to the end of the primary query.

function getAggregateValue(sys_id, config) {
	var value = 0;
	var ga = new GlideAggregate(config.table);
	ga.addAggregate('COUNT');
	var query = config.field + '=' + sys_id;
	if (config.filter) {
		query += '^' + config.filter;
	}
	ga.addEncodedQuery(query);
	ga.query();
	if (ga.next()) {
		value = parseInt(ga.getAggregate('COUNT'));
	}
	return value;
}

Now we can take another quick look and see if that finally gets us what we have been after all along.

Finally, the result we have been looking for

Voila! There it is — a column containing a count of selected related records. Now that wasn’t so hard, was it? Of course, we are not done quite yet. The SNH Data Table from JSON Configuration widget is not the only wrapper widget in the collection. We will need to add support for aggregate columns to the SNH Data Table from Instance Definition widget, as well as the SNH Data Table from URL Definition widget, the widget designed to work with the Configurable Data Table Widget Content Selector. Also, we have the Content Selector Configuration Editor, which allows you to create the JSON configuration files for both the Configurable Data Table Widget Content Selector and the SNH Data Table from JSON Configuration widget. That will have to be modified to support aggregate column specifications as well. None of that is super challenging now that we have things working, but it all needs to be done, so we will jump right into that next time out.