Need to pull the variables value

[is_12]

1. zipcode is entered on the catalog form, check the Location table in SN and validate the country,state and city. If its incorrect populate the Country,State, City but leave the field editable.
2. If the zipcode is not existing in Location table then API call to be triggered to MySite. From the response payload, under best suggestion block validate the country, state, city, if incorrect populate the values from the response and leave the fields editable.

1 --> is working correctly

2--> need help to pull the country code from the core_country table

all other variables country & county, town are present in the catalog form, so directly pulled it from the form & validated

request payload {
  "country": "India",
  "countryCode": "IN",
  "postZipCode": "560066",
  "county": "Karnataka",
  "town": "Bangalore"
}

able to get the success response in the from the rest message, but not getting to pull the dynamic value of country code 

getLocationDetails: function() {

        //gs.info('insidefunction ');

        var zipcode = this.getParameter('sysparm_zipcode');

        //var countryCode = this.getParameter('syspram_countryCode');

        var city = this.getParameter('sysparm_city');

        var state = this.getParameter('sysparm_state');

        var country = this.getParameter('sysparm_country');

        var location = new GlideRecord('cmn_location');

        location.addQuery('zip', zipcode);

        location.query();

        //gs.info('zipcode' + zipcode);

        if (location.next()) {

            return JSON.stringify({

                "country": location.getValue('country'),

                "state": location.getValue('state'),

                "city": location.getValue('city')

            });

            //gs.info('Final answer is: ' + JSON.stringify(locationDetails));

        } else {

            var reqObj = {

                "country": country,

                //"countryCode": countryCode,

                "postZipCode": zipcode,

                "county": state,

                "town": city

            };

            //gs.info("Request object for MySite is "+JSON.stringify(reqObj));

            var r = new sn_ws.RESTMessageV2('BT- GetLocation(mySite)', 'Default Post');

            r.setRequestHeader("APIGW-Tracking-Header", gs.generateGUID());

            r.setRequestBody(reqObj);

            var response = r.execute();

            var responseBody = response.getBody();

            var httpStatus = response.getStatusCode();

            if (httpStatus == '200' || httpStatus == '201') {

                //gs.info('insidefunction ');

                JSON.stringify({

                    "country": responseBody.suggestions[0].country, 

                    "state": responseBody.suggestions[0].county,

                    "city": responseBody.suggestions[0].locality

                });

            }

        }

        return null;

    },

and in the response body instead of the response body.suggestion[0], need to call the  "bestSuggested":{
      "country":"India",
      "countryCode":"IN",
      "postZipCode":"560066",
      "county":"Karnataka",
      "town":"BENGALURU"
   }

this above object

Please can anyone help with the above functionality, it is bit urgent requirement, 

Thanks,

looking forward for an solution

[KKM]

Hi Is_12,

You need to update your script to correctly extract the country code from the core_country table and adjust your API response parsing to pull values from "bestSuggested" instead of "suggestions[0]".

Fix 1: Fetch Country Code from core_country Table
In ServiceNow, the core_country table (sys_country) holds country details, including the country code. You need to query this table using the country name to get the countryCode.

Modify your function to retrieve countryCode like this:

function getCountryCode(countryName) {
var countryRec = new GlideRecord('core_country');
countryRec.addQuery('name', countryName);
countryRec.query();

if (countryRec.next()) {
return countryRec.getValue('code'); // Fetch the country code
}
return null; // Return null if not found
}

Fix 2: Parse API Response from bestSuggested
Your API response parsing should be updated to extract values from "bestSuggested" instead of "suggestions[0]".

Modify this section:

var responseBody = JSON.parse(response.getBody()); // Convert response to JSON
var httpStatus = response.getStatusCode();

if (httpStatus == '200' || httpStatus == '201') {
// Extract from 'bestSuggested' instead of 'suggestions[0]'
return JSON.stringify({
"country": responseBody.bestSuggested.country,
"countryCode": responseBody.bestSuggested.countryCode, // Now correctly pulling the country code
"state": responseBody.bestSuggested.county,
"city": responseBody.bestSuggested.town
});
}

Final Updated Function
Here's your fully updated getLocationDetails function:

getLocationDetails: function() {
var zipcode = this.getParameter('sysparm_zipcode');
var city = this.getParameter('sysparm_city');
var state = this.getParameter('sysparm_state');
var country = this.getParameter('sysparm_country');

var location = new GlideRecord('cmn_location');
location.addQuery('zip', zipcode);
location.query();

if (location.next()) {
return JSON.stringify({
"country": location.getValue('country'),
"state": location.getValue('state'),
"city": location.getValue('city'),
"countryCode": getCountryCode(location.getValue('country')) // Fetch country code
});
} else {
var reqObj = {
"country": country,
"postZipCode": zipcode,
"county": state,
"town": city
};

var r = new sn_ws.RESTMessageV2('BT- GetLocation(mySite)', 'Default Post');
r.setRequestHeader("APIGW-Tracking-Header", gs.generateGUID());
r.setRequestBody(JSON.stringify(reqObj));

var response = r.execute();
var responseBody = JSON.parse(response.getBody()); // Parse JSON
var httpStatus = response.getStatusCode();

if (httpStatus == '200' || httpStatus == '201') {
return JSON.stringify({
"country": responseBody.bestSuggested.country,
"countryCode": responseBody.bestSuggested.countryCode,
"state": responseBody.bestSuggested.county,
"city": responseBody.bestSuggested.town
});
}
}
return null;
}

How this fixed?
* Fetches countryCode dynamically from the core_country table.
* Extracts data from "bestSuggested" instead of "suggestions[0]".
* Returns correct JSON format with updated fields.

This should solve your issue!

Kindly mark it as "Accepted Solution"/"helpful", as it resolves your query. Please press like button for the resolution provided.

With Regards,
Krishna Kumar M - Talk with AIT3ch
LinkedIn: https://www.linkedin.com/in/mkrishnak4/
YouTube: https://www.youtube.com/@KrishAIT3CH
Topmate: https://topmate.io/mkrishnak4 [ Connect for 1-1 Session]

[is_12]

Hello @KKM 

in the core_country table i need to fetch the  ISO Short (iso3166_2) field currently replaced with return countryRec.getValue('code'); // Fetch the country code with iso3166_2 value and here also done the same thing "countryCode": getCountryCode(location.getValue('country')) // Fetch country code with iso3166_2 (I believe only this are two place that I need to change an backend value of the country code) and this iso3166_2 should be mapped to the countrycode of the request payload meaning : 

else {
var reqObj = {
"country": country,
"postZipCode": zipcode,
"county": state,
"town": city
};

Here in the above script only there are 4fields response body, need to include an country code also :   "country":"India",
      "countryCode":"IN",
      "postZipCode":"560066",
      "county":"Karnataka",
      "town":"BENGALURU"

Thanks,

I**bleep**a

[is_12]

currently made an changes highlighed in bold 

getLocationDetails: function() {

        //gs.info('insidefunction ');

        var zipcode = this.getParameter('sysparm_zipcode');

        //var countryCode = this.getParameter('syspram_countryCode');

        var city = this.getParameter('sysparm_city');

        var state = this.getParameter('sysparm_state');

        var country = this.getParameter('sysparm_country');

        var countryCode = '';

        var countryGR = new GlideRecord('core_country');

        countryGR.addQuery('name', countryName);

        countryGR.query();

        if (countryGR.next()) {

            countryCode = countryGR.getValue('iso3166_2'); // Assuming 'country_code' is the field name

        }

        var location = new GlideRecord('cmn_location');

        location.addQuery('zip', zipcode);

        location.query();

        //gs.info('zipcode' + zipcode);

        if (location.next()) {

            return JSON.stringify({

                "country": location.getValue('country'),

                "state": location.getValue('state'),

                "city": location.getValue('city'),

                "countryCode": getCountryCode(location.getValue('iso3166_2'))

            });

            //gs.info('Final answer is: ' + JSON.stringify(locationDetails));

        } else {

            var reqObj = {

                "country": country,

                "countryCode": countryCode,

                "postZipCode": zipcode,

                "county": state,

                "town": city

            };

            //gs.info("Request object for MySite is "+JSON.stringify(reqObj));

            var r = new sn_ws.RESTMessageV2('BT- GetLocation(mySite)', 'Default Post');

            r.setRequestHeader("APIGW-Tracking-Header", gs.generateGUID());

            r.setRequestBody(reqObj);

            var response = r.execute();

            var responseBody = JSON.parse(response.getBody()); // Convert response to JSON

            var httpStatus = response.getStatusCode();

            if (httpStatus == '200' || httpStatus == '201') {

                // Extract from 'bestSuggested' instead of 'suggestions[0]'

                return JSON.stringify({

                    "country": responseBody.bestSuggested.country,

                    "countryCode": responseBody.bestSuggested.countryCode, // Now correctly pulling the country code

                    "state": responseBody.bestSuggested.county,

                    "city": responseBody.bestSuggested.town

                });

            }

But not working 

[is_12]

Normally I should pass the country code var from the form(client script) ideally that var is present in the form, So need to get it form the country table, So now how to pass this ist short(country code) in the request payload & also in the repsonse. 

here is an client script which runs onchange of zipcode variable : 

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading || newValue == '') {

        return;

    }

    //alert('script got executed');

    var zipcode = newValue;

    var city = g_form.getValue('city');

    var state = g_form.getValue('state_county_province');

    var country = g_form.getValue('country');

    // Call the GlideAjax script include

    var ga = new GlideAjax('BT_CSR_SSR_CatalogUtils');

    ga.addParam('sysparm_name', 'getLocationDetails');

    ga.addParam('sysparm_zipcode', zipcode);

    ga.addParam('sysparm_city',city);

    ga.addParam('sysparm_state',state);

    ga.addParam('sysparm_country',country);

    ga.getXMLAnswer(function(response) {

        var locationDetails = JSON.parse(response);

        if (locationDetails) {

            if (city != locationDetails.city) {

                g_form.setValue('country', locationDetails.city);

            }

            if (state != locationDetails.state) {

                g_form.setValue('state_county_province', locationDetails.state);

            }

            if (country != locationDetails.country) {

                g_form.setValue('city', locationDetails.country);

            }

            //g_form.setValue('country', locationDetails.country);

            //g_form.setValue('state_county_province', locationDetails.state);

            //g_form.setValue('city', locationDetails.city);

        }

    });

}

Thanks,

I**bleep**a 

Looking forward for the fast replay