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

[SupriyaWaghmode]

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

[srohan24]

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

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;
}

[Ankur Bawiskar]

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

[Ankur Bawiskar]

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

[GlideFather]

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?