OnBefore Script for transform map help

[Alon Grod]

Hi,

Im trying to create OnBefore script:

Email - Exists?
IF Not exists, create record  in customer_contact table.

IF Email Exists:
Check if (Account == Company)
Just update the record in the customer_contact table.

else:
go to customer_contact relationship and company and contact from the sheet.

What am I doing wrong? 

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var bs = new GlideRecord('customer_contact');
    bs.addEncodedQuery('email=' + source.u_email);
    bs.query();
    if (!bs.next()) {

        var customer_contact = new GlideRecord('customer_contact');
        customer_contact.initialize();
        customer_contact.setDisplayValue('account', source.u_account);
        customer_contact.setValue('first_name', source.u_first_name);
        customer_contact.setValue('last_name', source.u_last_name);
        customer_contact.setValue('user_name', source.u_email);
        customer_contact.setDisplayValue('location', source.u_location__ci_tate__country_);
        customer_contact.setValue('email', source.u_email);
        customer_contact.setValue('country', source.u_country_code);
        customer_contact.setValue('u_infra_user', source.u_infra_user);
        customer_contact.setValue('phone', source.u_business_phone);
        customer_contact.insert();

    } else {

        if (account == source.u_account) {

            bs.setValue('first_name', source.u_first_name);
            bs.setValue('last_name', source.u_last_name);
            bs.setDisplayValue('location', source.u_location__ci_tate__country_);
            bs.setValue('country', source.u_country_code);
            bs.setValue('u_infra_user', source.u_infra_user);
            bs.setValue('phone', source.u_business_phone);
            bs.update();

        }
        else{
        var sn_customer_relationship = new GlideRecord('sn_customer_relationship');
    sn_customer_relationship.setDisplayValue('company', source.u_account);
    sn_customer_relationship.setValue('contact', bs.getUniqueValue());
    sn_customer_relationship.insert();
            current.setAbortAction(true);
        }
     
    }

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

[Mahendra RC]

Hi,

Please check with below script:

I believe the target table on your transform map is customer_contact. if yes then you can set Coalesce true on Email field and Choice action = create

then use the below script in transform script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    if (action == "insert")
        target.setDisplayValue('account', source.u_account);
        target.setValue('first_name', source.u_first_name);
        target.setValue('last_name', source.u_last_name);
        target.setValue('user_name', source.u_email);
        target.setDisplayValue('location', source.u_location__ci_tate__country_);
        target.setValue('email', source.u_email);
        target.setValue('country', source.u_country_code);
        target.setValue('u_infra_user', source.u_infra_user);
        target.setValue('phone', source.u_business_phone);

    } else if (action == "update") {

        if (target.account == source.u_account) {

            target.setValue('first_name', source.u_first_name);
            target.setValue('last_name', source.u_last_name);
            target.setDisplayValue('location', source.u_location__ci_tate__country_);
            target.setValue('country', source.u_country_code);
            target.setValue('u_infra_user', source.u_infra_user);
            target.setValue('phone', source.u_business_phone);
        } else {
            var sn_customer_relationship = new GlideRecord('sn_customer_relationship');
            sn_customer_relationship.setDisplayValue('company', source.u_account);
            sn_customer_relationship.setValue('contact', target.getUniqueValue());
            sn_customer_relationship.insert();
            ignore = true;
        }
    }
})(source, map, log, target);

Please mark my respsone as helpful/correct, if it answer your question.

Thanks

[Mahendra RC]

Else you can use the below script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var bs = new GlideRecord('customer_contact');
    bs.addEncodedQuery('email=' + source.u_email);
    bs.query();
    if (!bs.next()) {

        var customer_contact = new GlideRecord('customer_contact');
        customer_contact.initialize();
        customer_contact.setDisplayValue('account', source.u_account);
        customer_contact.setValue('first_name', source.u_first_name);
        customer_contact.setValue('last_name', source.u_last_name);
        customer_contact.setValue('user_name', source.u_email);
        customer_contact.setDisplayValue('location', source.u_location__ci_tate__country_);
        customer_contact.setValue('email', source.u_email);
        customer_contact.setValue('country', source.u_country_code);
        customer_contact.setValue('u_infra_user', source.u_infra_user);
        customer_contact.setValue('phone', source.u_business_phone);
        customer_contact.insert();

    } else {

        if (bs.account == source.u_account) {

            bs.setValue('first_name', source.u_first_name);
            bs.setValue('last_name', source.u_last_name);
            bs.setDisplayValue('location', source.u_location__ci_tate__country_);
            bs.setValue('country', source.u_country_code);
            bs.setValue('u_infra_user', source.u_infra_user);
            bs.setValue('phone', source.u_business_phone);
            bs.update();

        }
        else{
        var sn_customer_relationship = new GlideRecord('sn_customer_relationship');
    sn_customer_relationship.setDisplayValue('company', source.u_account);
    sn_customer_relationship.setValue('contact', bs.getUniqueValue());
    sn_customer_relationship.insert();
    ignore = true;
        }
    }
})(source, map, log, target);

Please mark my respsone as helpful/correct, if it answer your question.

Thanks

[Alon Grod]

its still not working..

[Alon Grod]

but the insert and update should be decided by checking if the email is already exists or not.

If email does not exists, then i just need to create the record in customer_contact table.

If he already exists, then i need to check if the company is like the company(account) from the sheet. if they are the same, then i need update, if they are different, then i need to insert to customer_contact_relationship table.