Transform script not working properly

[Ujjwala1]

Hello Experts,

I have been trying to insert data on a custom table using transform map script, but every time it is inserting records 

I want that script should check if that id exists in the custom table and user table or not , if exists it should update the existing record otherwise it should insert new record on custom and user table.

I am using below script as onbefore transform map script section

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    var gr = new GlideRecord('u_tsc_restaurants');//custom table
    gr.addQuery('u_tsc_restaurant_number', source.u_carrier);
    gr.query();
    if (gr.next()) {

        gr.u_operational_fbl_contact_name = source.u_fbl_ad_contact_name;
        gr.u_operational_fbl_contact_email = source.u_fbl_ad_contact_email;
        gr.update();
        var user = new GlideRecord('sys_user');
        user.addQuery('sys_domain', '');
        user.addQuery('last_name', source.u_carrier);
        if (user.next()) {
            target.u_tsc_store_user = user.sys_id;
            target.u_pos_environment='best';
            target.update();
        } else {

            user.first_name = 'ID';
            user.last_name = source.u_carrier;
            user.company='';
            user.insert();
            target.u_tsc_store_user = user.sys_id;
            target.u_pos_environment='best';
            target.update();
        }
    }

})(source, map, log, target);

Any kind of help is highly appreciated.

Thanks & Regards,

Ujjwala

[Tom Sienkiewicz]

The insert will happen any time there is no coalesce defined. So in the transform map, you should probably set the Coalesce attribute to "true" for "u_tsc_restaurant_number".

Can you share the full transform map so we can see how it is set up.

[Ujjwala1]

hi @Tomasz Sienkiewicz ,

I have made coalesce field true for "u_tsc_restaurant_number",

I want same thing to work for user table as well 

[Tom Sienkiewicz · Accepted solution]

So basically, it seems from your script that any time you do ot find a user by the last name, which is provided as one of the source attributes, you then create a new user.

The code misses a "user.query()" line after you do the .addQuery part I think 😉

It is not a good idea generally to try and locate a user by their last name only. I would strongly recommend to try to change that to an email address or perhaps unique LDAP attribute if possible.