Process account and contact from json response

[arunm04]

Hello Community,

I'm new to integrations. We are integrating ServiceNow with a third party tool, where we get the JSON (attached). I need to process this JSON and insert the records in to account and contact table.

Any help on this is appreciated.

[AnveshKumar M · Accepted solution]

Hi @arunm04 

You can try the following code to extract the data from JSON and insert it into customer_account and customer_contact tables.

var data = "YOUR_JSON"; //something like request.body.data, if it is stringifed JSON, parse it using JSON.parse()

var accGr = new GlideRecord("customer_account");
accGr.addQuery("identification_number", data.customerId);
accGr.query();
var acc_sys_id;
if (accGr.next()) {
    accGr.identification_number = data.customerId;
    accGr.name = data.customerName;
    //Address
    accGr.street = data.address.street + ", " + data.address.street2;
    accGr.city = data.address.city;
    accGr.state = data.address.state;
    accGr.zip = data.address.zipCode;
    accGr.country = data.address.country;
    accGr.update();
    acc_sys_id = accGr.getUniqueValue();
} else {
    accGr.initialize();
    accGr.identification_number = data.customerId;
    accGr.name = data.customerName;

    //Address
    accGr.street = data.address.street + ", " + data.address.street2;
    accGr.city = data.address.city;
    accGr.state = data.address.state;
    accGr.zip = data.address.zipCode;
    accGr.country = data.address.country;
    acc_sys_id = accGr.insert();
}

if (acc_sys_id) { //Account successfully inserted/updated
    //Fetch contatcs
    var contacts = data.customerContactViewList;
    for (key in contacts) {
        var con_sys_id;
        var conGr = new GlideRecord("customer_contact");
        conGr.addQuery("email", contacts[key].email);
        conGr.query();
        if (conGr.next()) { //If contact already exists, update the data
            conGr.phone = contacts[key].phone;
            conGr.email = contacts[key].email;
            var con_name = contacts[key].contactName;
            con_name = con_name.split(" ");
            conGr.first_name = con_name[0];
            conGr.last_name = con_name[1];
            conGr.account = acc_sys_id;
            conGr.update();
            con_sys_id = conGr.getUniqueValue();
        } else { //Contact not found, create new contact
            conGr.initialize();
            conGr.phone = contacts[key].phone;
            conGr.email = contacts[key].email;
            var con_name = contacts[key].contactName;
            con_name = con_name.split(" ");
            conGr.first_name = con_name[0];
            conGr.last_name = con_name[1];
            conGr.account = acc_sys_id;
            con_sys_id = conGr.insert();
        }
    }
}

Please mark my answer helpful and accept it as a solution, if it helped you 👍✅

[AnveshKumar M · Accepted solution]

@arunm04 Try the following updated code to update the primary contact based assignmentType attribute in the JSON.

var data = "YOUR_JSON"; //something like request.body.data, if it is stringifed JSON, parse it using JSON.parse()

var accGr = new GlideRecord("customer_account");
accGr.addQuery("identification_number", data.customerId);
accGr.query();
var acc_sys_id;
if (accGr.next()) {
    accGr.identification_number = data.customerId;
    accGr.name = data.customerName;
    //Address
    accGr.street = data.address.street + ", " + data.address.street2;
    accGr.city = data.address.city;
    accGr.state = data.address.state;
    accGr.zip = data.address.zipCode;
    accGr.country = data.address.country;
    accGr.update();
    acc_sys_id = accGr.getUniqueValue();
} else {
    accGr.initialize();
    accGr.identification_number = data.customerId;
    accGr.name = data.customerName;

    //Address
    accGr.street = data.address.street + ", " + data.address.street2;
    accGr.city = data.address.city;
    accGr.state = data.address.state;
    accGr.zip = data.address.zipCode;
    accGr.country = data.address.country;
    acc_sys_id = accGr.insert();
}

if (acc_sys_id) { //Account successfully inserted/updated
    //Fetch contatcs
    var contacts = data.customerContactViewList;
    for (key in contacts) {
        var con_sys_id;
        var conGr = new GlideRecord("customer_contact");
        conGr.addQuery("email", contacts[key].email);
        conGr.query();
        if (conGr.next()) { //If contact already exists, update the data
            conGr.phone = contacts[key].phone;
            conGr.email = contacts[key].email;
            var con_name = contacts[key].contactName;
            con_name = con_name.split(" ");
            conGr.first_name = con_name[0];
            conGr.last_name = con_name[1];
            conGr.account = acc_sys_id;
            conGr.update();
            con_sys_id = conGr.getUniqueValue();
        } else { //Contact not found, create new contact
            conGr.initialize();
            conGr.phone = contacts[key].phone;
            conGr.email = contacts[key].email;
            var con_name = contacts[key].contactName;
            con_name = con_name.split(" ");
            conGr.first_name = con_name[0];
            conGr.last_name = con_name[1];
            conGr.account = acc_sys_id;
            con_sys_id = conGr.insert();
        }

        if(contacts[key].assignmentType == "Primary Customer"){
            var pConGr = new GlideRecord("customer_account");
            pConGr.get(acc_sys_id);
            pConGr.primary_contact = con_sys_id;
            pConGr.update();
        }
    }
}

[arunm04]

Thanks again 👍