question Inegration question about coalesce in Community Central forum

Inegration question about coalesce

srohan24 — Thu, 03 Jul 2025 08:35:46 GMT

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

Re: Inegration question about coalesce

Ankur Bawiskar — Thu, 03 Jul 2025 08:51:19 GMT

@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.

Re: Inegration question about coalesce

srohan24 — Thu, 03 Jul 2025 08:59:09 GMT

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');

Re: Inegration question about coalesce

Ankur Bawiskar — Thu, 03 Jul 2025 09:14:37 GMT

@srohan24

so where are you stuck?

Re: Inegration question about coalesce

srohan24 — Thu, 03 Jul 2025 09:17:36 GMT

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

Re: Inegration question about coalesce

GlideFather — Thu, 03 Jul 2025 09:19:59 GMT

Hi @srohan24,
I saw your transform script that you shared.

Can you please explain more the whole ambition?

The coalesce field works like a validation whether that field in the coalesce fields exists, the record is updated, if it doesn't exist, it creates a new record... there can be more than just one coalesce fields if needed.

You want to track the SAP ID or you want to store it? You can create a custom field on User table and receive that value in that field...

Or what exactly is the issue?

Re: Inegration question about coalesce

SupriyaWaghmode — Thu, 03 Jul 2025 09:22:23 GMT

Hello @srohan24

Please verify below points

Is there any other business rule/script to create profiles.??
also make sure that if record not found on target table ignore the insert action.

Thanks & Regards ,

Supriya Waghmode |ServiceNow Consultant

www.linkedin.com/in/supriya-waghmode

Re: Inegration question about coalesce

srohan24 — Thu, 03 Jul 2025 09:35:33 GMT

there is a BR to Enforce unique user for Employee Profile
below section of onBefore is ignoring the the insert action

Re: Inegration question about coalesce

Ankur Bawiskar — Thu, 03 Jul 2025 09:40:24 GMT

@srohan24

where have you written that script?

is that onBefore transform script?

I couldn't find the variable defined anywhere sapEmpNum

Also you need to use next() method to actually go to that gliderecord object

(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;
    }
    userGr.next();

    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');

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

Re: Inegration question about coalesce

Ankur Bawiskar — Fri, 04 Jul 2025 04:39:41 GMT

@srohan24

Hope you are doing good.

Did my reply answer your question?

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