HRSD Employee Forms are forms (re-purposed surveys) that can be sent to employees as a task in a case. OOB, the fields on the form can be mapped back to fields on tables. The problem is, they can only be mapped to tables where there is one record per user, so most commonly, this would be the HR Profile table. OOB, they can’t be mapped back to the HR Task or the HR Case that the HR Task belongs to. Fortunately, Filipe Cruz has a great article on a customization which allows mapping back to the HR Task or HR Case.
This implementation of this customization varies slightly from the article in three ways:
1. All the customizations are done in the sn_hr_sp.EmployeeDataCollection script include. The BR Reset invoking task status is not modified.
2. This implementation allows attachments to be mapped from the Employee Form.
3. This implementation supports bi-drectional mapping — fields and attachments can be mapped from the HR Task or HR Case to the Employee Form. This would allow for pre-filling of certain fields.
Note that because this implementation doesn’t modify the BR Reset invoking task status is not modified, you always have to specify a condition when mapping the HR Task. I put an annotation in the Employee Form form with the following:
You can map the responses to the employee form back to the HR task that originated it by choosing HR Task in the table field above. When the HR Task table is selected, the Conditions field below will not apply, however you must enter a value in this field. Simply specify Active = True. Keep in mind that the user must have write access to the mapped fields. Likewise, you can map the responses to the employee form to the case that is the parent of the HR task by choosing the appropriate case table in the field above. You must also specify a condition, even if it won’t apply. Simply specify Active = True. Keep in mind that the user must have write access to the mapped fields.
Below are two code blocks, the first for the EmployeeDataCollection script include with the customizations and the second is a script include in Global for copying attachments.
EmployeeDataCollection
var EmployeeDataCollection = Class.create();
mployeeDataCollection.prototype = {
initialize: function () {},
mapTaskDataToSurvey: function (taskData) {
var tableName = taskData.employee_form.table_Name;
var condition = taskData.employee_form.condition;
var numRecords;
var message;
if (!gs.nil(condition)) {
var recordGr = new GlideRecord(tableName);
//start custom code block for mapping fields from HR Task / HR Case to the Employee Form
var tableType = this.getTableType(tableName); //custom function to get the table type
if (tableType == 'hrTask') {
recordGr.addQuery('sys_id', taskData.sys_id); //if hr task, get the sysid of the task record
} else if (tableType == 'hrCase') {
recordGr.addQuery('sys_id', taskData.parent); //if hr case, get the sysid of the parent of the hr task
} else {
recordGr.addEncodedQuery(condition); //all other tables use the condition from the employee form record. This line is OOB
}
//end custom code block
recordGr.query();
numRecords = recordGr.getRowCount();
if (numRecords != 1) {
message = gs.getMessage("Cannot display the employee form because the associated condition returned {0} records. The condition must return one record.", [numRecords]);
this.addError(taskData, message);
return;
}
if (recordGr.next()) {
if (!recordGr.canRead()) {
message = gs.getMessage("The user doesn't have read permission for the record. Please check the ACLs of the associated table.");
this.addError(taskData, message);
return;
}
mapFields();
}
}
function mapFields() {
var tempInt;
var mappingGr = new GlideRecord('sn_hr_core_employee_form_field_mapping');
mappingGr.addQuery('employee_form', taskData.employee_form.id);
mappingGr.query();
while (mappingGr.next()) {
var field = mappingGr.getValue('field');
var qaGr = new GlideRecord('asmt_assessment_instance_question');
qaGr.addQuery('metric', mappingGr.question);
qaGr.addQuery('instance', taskData.employee_form.survey_instance);
qaGr.query();
if (qaGr.next()) {
var typeString = recordGr.getElement(field).getED().getInternalType();
if (typeString == 'reference')
qaGr.reference_id = recordGr.getElement(field).toString();
else if (typeString == 'integer' || typeString == 'decimal' || typeString == 'longint' || typeString == 'float' || typeString == 'choice') {
tempInt = parseInt(recordGr.getElement(field).toString());
if (isNaN(tempInt))
qaGr.string_value = recordGr.getElement(field).toString();
else
qaGr.value = tempInt;
}
//begin custom code block for mapping attachment fields from HR Task / HR Case to the Employee Form
else if (typeString == 'file_attachment') {
if (recordGr.getElement(field).toString()) { //make sure there is a value in the attachment field
//remove attachment(s) on question instance if there is one, otherwise multiple copies of the same attachment will be visible
var gsa = new GlideSysAttachment();
var att = new GlideRecord('sys_attachment');
att.addQuery('table_name', qaGr.getTableName());
att.addQuery("table_sys_id", qaGr.sys_id);
att.query();
while (att.next()) {
gsa.deleteAttachment(att.sys_id);
}
var attachmentSysId = new global.HRSDGlobalUtils().copyAttachmentToGlideRecord(qaGr, recordGr.getElement(field).toString(), false);
qaGr.string_value = recordGr.getElement(field).toString(); //put the original attachment sysid here
}
}
//end custom code block
else
qaGr.string_value = recordGr.getElement(field).toString();
qaGr.update();
}
}
}
},
saveEmployeeForm: function (employeeFormGr, isChanged) {
var surveyGr;
// Create Form definition
if (gs.nil(employeeFormGr.form_definition)) {
surveyGr = new GlideRecord('asmt_metric_type');
surveyGr.initialize();
surveyGr.name = employeeFormGr.name;
surveyGr.description = employeeFormGr.description;
surveyGr.introduction = '';
surveyGr.end_note = '';
surveyGr.evaluation_method = 'survey';
surveyGr.active = true;
surveyGr.duration.setDisplayValue('365 00:00:00');
surveyGr.not_show_intro_note = true;
return surveyGr.insert();
} else {
if (isChanged) {
surveyGr = new GlideRecord('asmt_metric_type');
if (surveyGr.get(employeeFormGr.form_definition)) {
surveyGr.name = employeeFormGr.name;
surveyGr.description = employeeFormGr.description;
surveyGr.active = employeeFormGr.active;
surveyGr.update();
return null;
}
}
}
},
addError: function (taskTicket, eMessage) {
var errorGr = new GlideRecord('sn_hr_core_employee_form_error');
errorGr.initialize();
errorGr.setValue('case', taskTicket.parent);
errorGr.setValue('task', taskTicket.sys_id);
errorGr.setValue('user', gs.getUserID());
errorGr.setValue('description', eMessage);
errorGr.insert();
},
saveUpdatedData: function (taskId) {
var numRecords;
var message;
var inError = false;
var outError = false;
var employeeEnteredData = {};
employeeEnteredData.fieldArray = [];
var fieldData;
//check if the survey is finished
var current = new GlideRecord('sn_hr_core_task');
if (current.get(taskId) && current.survey_instance.state == 'complete' && current.assigned_to) {
var tableName = current.employee_form.table;
var condition = current.employee_form.condition;
employeeEnteredData.tableName = String(tableName);
if (!gs.nil(condition)) {
//var recordGr = new GlideRecordSecure(tableName);
var recordGr = new GlideRecord(tableName);
//start custom code block for mapping fields from the Employee Form to HR Task / HR Case
var tableType = this.getTableType(tableName); //custom function to get the table type
if (tableType == 'hrTask') {
recordGr.addQuery('sys_id', current.sys_id); //if hr task, get the sysid of the task record
} else if (tableType == 'hrCase') {
recordGr.addQuery('sys_id', current.parent); //if hr case, get the sysid of the parent of the hr task
} else {
recordGr.addEncodedQuery(condition); //all other tables use the condition from the employee form record. This line is OOB
}
//end custom code block
recordGr.addEncodedQuery(condition);
recordGr.query();
numRecords = recordGr.getRowCount();
if (numRecords != 1)
inError = true;
if (recordGr.next())
saveData();
else {
message = gs.getMessage("Cannot display the employee form because the associated condition returned no records. The condition must return one record.");
addError(message);
return;
}
if (inError || outError) {
//update task work notes
var learnMoreMessage = gs.getMessage('Click here to learn more.');
var taskMessage = gs.getMessage('The task:{0} completed with one or more errors.[code]<a href="/nav_to.do?uri=%2Fsn_hr_core_employee_form_error_list.do%3Fsysparm_query%3Dtask%253D{1}" target="_blank"> {2}</a> [/code]', [current.number, taskId, learnMoreMessage]);
current.work_notes = taskMessage;
current.update();
}
if (inError) {
message = gs.getMessage("Cannot save the employee form because the associated condition returned {0} records. The condition must return one record.", [numRecords]);
addError(message);
}
}
}
function saveData() {
var field;
var needUpdate = false;
var updateList = [];
var mappingGr = new GlideRecord('sn_hr_core_employee_form_field_mapping');
mappingGr.addQuery('employee_form', current.employee_form);
mappingGr.query();
while (mappingGr.next()) {
field = mappingGr.getValue('field');
//get all non read only mapped mappings
if (mappingGr.question.read_only)
continue;
fieldData = {};
var qaGr = new GlideRecord('asmt_assessment_instance_question');
qaGr.addQuery('metric', mappingGr.question);
qaGr.addQuery('instance', current.survey_instance);
qaGr.setLimit(1);
qaGr.query();
if (qaGr.next())
needUpdate = setGlideRecordValue(recordGr, field, qaGr, updateList) || needUpdate;
else {
message = gs.getMessage("One or more questions were not found on the employee form. Were the questions deleted from the associated survey?");
addError(message);
}
} //while mappingGr.next()
if (needUpdate) {
if (!recordGr.update()) {
message = gs.getMessage("Glide record update failed on this table: {0}. Verify that the user has write access to the record. Also check the ACLs of the associated table, as well as the system logs.", [recordGr.getTableName()]);
addError(message);
}
}
//update dot walk fields
for (var updateId in updateList) {
if (updateList.hasOwnProperty(updateId)) {
if (!updateList[updateId].update()) {
message = gs.getMessage("Glide record update failed on this table: {0}. Verify that the user has write access to the record. Also check the ACLs of the associated table, as well as the system logs.", [recordGr.getTableName()]);
addError(message);
}
}
} //for updateList
} // saveData function
function setGlideRecordValue(recordGr, field, qaGr, updateList) {
var needUpdate = false;
var glideElement = recordGr.getElement(field);
var typeString = glideElement.getED().getInternalType();
var datatype = qaGr.metric.datatype.toString();
if (typeString == 'reference') {
if (qaGr.reference_id)
needUpdate = setValue(recordGr, field, glideElement, qaGr.reference_id, updateList);
} else {
// for 'string', 'date', and 'datetime' metric types, the value is stored as 0
if (['string', 'date', 'datetime'].indexOf(datatype) === -1 && !gs.nil(qaGr.value))
needUpdate = setValue(recordGr, field, glideElement, qaGr.value, updateList);
//start custom code block for mapping attachment fields from the Employee Form to HR Task / HR Case
else if (datatype == 'attachment') {
var mrGr = getMetricResultRecord(qaGr);
if (mrGr) { //make sure there is a record before doing the below, otherwise it will error out.
var attachmentSysId = new global.HRSDGlobalUtil().copyEmployeeFormAttachmentToField(mrGr.getTableName(), mrGr.getUniqueValue(), recordGr.getTableName(), recordGr.getUniqueValue());
needUpdate = setValue(recordGr, field, glideElement, attachmentSysId, updateList);
}
//end custom code block
} else if (qaGr.string_value)
needUpdate = setValue(recordGr, field, glideElement, qaGr.string_value, updateList);
}
return needUpdate;
}
function setValue(recordGr, field, glideElement, value, updateList) {
var refFields = field.split('.');
var isDotWalkField = refFields.length > 1;
var needUpdate = false;
var recordValue = String(glideElement).trim();
var tempValue = String(value).trim();
fieldData.field = field;
fieldData.oldValue = recordValue;
fieldData.employeeNewValue = tempValue;
employeeEnteredData.fieldArray.push(fieldData);
if (recordValue != tempValue && !glideElement.canWrite()) {
message = gs.getMessage("Failed to update the field:{0} on the table:{1}. Verify that the user has write access to the column. Also check the ACLs of the associated table, as well as the system logs.", [field, recordGr.getTableName()]);
addError(message);
return false;
}
if (!inError && recordValue != tempValue) {
if (isDotWalkField)
updateReferenceRecord(recordGr, refFields, value, updateList);
else {
recordGr.setValue(field, value);
needUpdate = true;
}
}
return needUpdate;
}
function updateReferenceRecord(parentGr, refFields, value, updateList) {
var message = '';
if (refFields.length < 2)
return;
var ge = parentGr.getElement(refFields[0]);
var referenceGr = ge.getRefRecord();
if (referenceGr.isNewRecord() || !referenceGr.isValidRecord()) {
message = gs.getMessage("The reference field, {0}, is empty or invalid on this table: {1}. Make sure that the record reference is set.", [refFields[0], parentGr.getTableName()]);
addError(message);
return;
}
if (refFields.length == 2) {
referenceGr.setValue(refFields[1], value);
updateList[referenceGr.getUniqueValue()] = referenceGr;
} else {
refFields.splice(0, 1);
updateReferenceRecord(referenceGr, refFields, value, updateList);
}
}
function addError(eMessage) {
outError = true;
var errorGr = new GlideRecord('sn_hr_core_employee_form_error');
errorGr.initialize();
errorGr.setValue('case', current.parent);
errorGr.setValue('task', current.getUniqueValue());
errorG^\s*$\n.r.setValue('user', current.getValue('assigned_to'));
errorGr.setValue('description', eMessage);
//log field info
if (employeeEnteredData.fieldArray.length > 0) {
var payload = JSON.stringify(employeeEnteredData);
errorGr.setValue('payload', payload);
}
errorGr.insert();
}
//begin custom function for attachments
//returns the gliderecord of the asmt_metric_result table for the asmt_assessment_instance_question record
function getMetricResultRecord(qaGr) {
var resGr = new GlideRecord('asmt_metric_result');
resGr.addQuery('instance', qaGr.instance);
resGr.addQuery('metric', qaGr.metric);
resGr.query();
if (resGr.next()) {
return resGr;
}
}
//end custom function for attachments
},
//start custom function for mapping. Returns the type of table: HR task, HR Case or Other
getTableType: function (tableName) {
if (tableName == 'sn_hr_core_task') {
return 'hrTask';
} else if ((tableName.indexOf('sn_hr_core_case') == 0) || (tableName.indexOf('sn_hr_le_case') == 0) || (tableName.indexOf('sn_hr_er_case') == 0)) {
return 'hrCase';
} else {
return 'other';
}
},
//end custom function for mapping
type: 'EmployeeDataCollection'
;Create a script include called HRSDGlobalUtils and put the two methods below into it.
copyAttachmentToGlideRecord
copyAttachmentToGlideRecord: function(targetGlideRecord, attachmentSysId, fieldAttachment) {
//copies a single attachment from one record to another (or within the same record)
//fieldAttachment = if true, the new attachment is a field attachment visible only in fields not the attachments at the top of the form.
// Get record from sys_attachment table
var sourceAttachmentGlideRecord = new GlideRecord('sys_attachment');
sourceAttachmentGlideRecord.get(attachmentSysId);
// Get field values from retrieved sys_attachment record
var fileName = sourceAttachmentGlideRecord.getValue('file_name');
var contentType = sourceAttachmentGlideRecord.getValue('content_type');
var sourceAttachmentSysId = sourceAttachmentGlideRecord.getValue('sys_id');
// Attach sys_attachment record content stream to test_table record
var gsa = new GlideSysAttachment();
var newAttachmentSysId = gsa.writeContentStream(
targetGlideRecord,
fileName,
contentType,
gsa.getContentStream(sourceAttachmentSysId));
//make it a field attachment (ZZ_YY) if requested
if (fieldAttachment) {
var newAttachmentGR = new GlideRecord('sys_attachment');
newAttachmentGR.get(newAttachmentSysId);
newAttachmentGR.setValue('table_name', 'ZZ_YY' + targetGlideRecord.getTableName());
newAttachmentGR.update();
}
return newAttachmentSysId;
}copyEmployeeFormAttachmentToField
copyEmployeeFormAttachmentToField: function(sourceTable, sourceSysId, targetTable, targetSysId) {
// can't write to the sys_attachment table from outside of Global so this function gets called from scopes to do it
var attachment = new GlideSysAttachment();
var copiedAttachments = attachment.copy(sourceTable, sourceSysId, targetTable, targetSysId);
var attachGR = new GlideRecord('sys_attachment');
attachGR.addQuery('table_name', targetTable);
attachGR.addQuery('table_sys_id', targetSysId);
attachGR.addQuery('sys_id', 'IN', copiedAttachments.toString().substring(1).slice(0, -1));
attachGR.query();
if (attachGR.next()) {
attachGR.setValue('table_name', 'ZZ_YY' + targetTable);
attachGR.update();
return attachGR.getUniqueValue();
}
}