Inegration question about coalesce

[srohan24]

I have a requirement Employee data integration from SAP to ServiceNow in a custom Employee Profile table.

SAP has unique field Employee ID.

Employee Profile do not have any such field, whereas it has field "User" which is a reference to User table (sys_user).

User table has Employee ID field.

I’m trying to use the COALESCE function, but I’m running into issues. Could you please help or suggest a better approach, any script or something

[Ankur Bawiskar]

@srohan24 

how are you loading? are you using transform map etc?

you can use onBefore transform script and check if that Employee ID is present for any of the user on that table and then allow insert/update

Don't use field maps here.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[srohan24]

Yes, I am using transform map and below onBefore script -

(function transformRow(source, target, map, log, isUpdate) {

if (!sapEmpNum) {
gs.warn('[onBefore] Missing or invalid employee number in source');
ignore = true;
return;
}

var userGr = new GlideRecord('sys_user');
userGr.addQuery('employee_number', sapEmpNum);
userGr.query();

if (!userGr.next()) {
gs.warn('[onBefore] No matching sys_user for employee_number: ' + sapEmpNum);
ignore = true;
return;
}

var userSysId = userGr.getUniqueValue();

var profileGr = new GlideRecord('sn_employee_profile');
profileGr.addQuery('user', userSysId);
profileGr.query();

if (profileGr.next()) {
target.setValue('sys_id', profileGr.getUniqueValue());
gs.info('[onBefore] Updating Employee Profile with sys_id: ' + profileGr.getUniqueValue());
} else {
gs.warn('[onBefore] No matching sn_employee_profile for user sys_id: ' + userSysId);
ignore = true;
}
})(source, target, map, log, action === 'update');

[Ankur Bawiskar]

@srohan24 

so where are you stuck?

[srohan24]

it keeps creating records in the target table, instead of updating