Pulling data from one table and displaying it on another.

[ServiceNowSteve]

Good Morning,

I have been attempting to piece together a client script that helps me pull data from one table and display it as an alert on the incident form. The business reason behind this is that our managers need to alert the technicians about problems in locations without relying on the technician to go look at a note somewhere in the company’s information.

First a little reference as to the structure of our instance:

The Incident Form

 

The company profile for Willacy County, TX with an custom alert made by a manager.
This alert is stored in our u_customer_alerts table.

 

I am open to any alternative suggestions but what I would like to do is when the Company/Facility field changes on the incident form I would like the script to search the u_customer_alerts table for a matching name (in this case Willacy County, TX) then find an alert where the alert category is Incident Alert and finally get the note on the alert and return it to the incident form via an alert(u_note); method so it pops up for the technician.

Here is the client script I have to do this so far but it’s just not returning any results.

Type: ON CHANGE

Field Name: Company / Client

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

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

   {

      return;

   }

               
//The only way I could figure out how to search was to use this line plus the function
var comp = g_form.getReference('company', getCompany);

               

function getCompany(comp)
{

//Look at the customer alerts
var findAlerts = new GlideRecord('u_customer_alerts');

//Limit by where the name is the same as the one in the company field
findAlerts.addQuery('u_client_profile', comp.name);                      

//Limit by ones with type Incident Alert
findAlerts.addQuery('u_alert_category', 'Incident Alert');

//Run it
findAlerts.query();

                         
//Return the alert
alert('ALERT: ' + findAlerts.getDisplayValue(u_notes));
}

So there we have it, I am at a loss because nothing comes back…any ideas how I can fix this?

[Bhawana Upreti]

Here is your script,

var comp = g_form.getReference('company', getCompany);

function getCompany(comp) { 
var findAlerts = new GlideRecord('u_customer_alerts'); 
findAlerts.addQuery('u_client_profile.name', comp.name); // just make it reference by adding .name
findAlerts.addQuery('u_alert_category', 'Incident Alert'); // change the bolded value if u'll find it different
findAlerts.query(); //Look at the u_notes field on the record and show it in an alert
while(findAlerts.next()){
alert('ALERT: ' + findAlerts.u_notes.toString());
}
}

Please make sure that alert category has the same choice value by going to dictionary you need to check the value part and change it if it is different. See the screenshot for reference 

Thanks.

[Ashutosh Munot1]

HI,

First off all. I will suggest not to use glideRecord and get reference in client side code. Always go with GlideAjax and Script includes.

This helps in performance as well as getting data in proper formats.

So, now here are the things which you should look into your code first you do findAlerts.next() before your alert in function company Code.

Also let me know the type of this field comp.name.

Thanks,
Ashutosh

[ServiceNowSteve]

comp is a field reference to the core_company table on the incident form

[ServiceNowSteve]

Sorry, u_client_profile is a table attached to the Company table. It's essentially expanded information for the company so we didn't overload the core_company table. We are alerting based on the Note in the client profile table not the company table. 

[Shishir Srivast · Accepted solution]

Did you get a chance to check the response here. Putting the response here again with little modification. Please check if this helps.

Client Script:
var gAjax = new GlideAjax("getAlert");
gAjax.addParam("sysparm_name", "findAlerts");
gAjax.addParam("sysparm_company", g_form.getDisplayBox('company').value);
gAjax.addParam("sysparm_category",  'Incident Alert');
gAjax.getXML(ajaxResponse);
function ajaxResponse(serverResponse) {
var answer = serverResponse.responseXML.documentElement.getAttribute("answer");
alert('ALERT: ' + answer);
}

Client callable Script Include:

var getAlert = Class.create();
getAlert.prototype = Object.extendsObject(AbstractAjaxProcessor, {
findAlerts : function() {
var gr = new GlideRecord('u_customer_alerts');
gr.addQuery('u_client_profile', this.getParameter('sysparm_company'));
gr.addQuery('u_alert_category', this.getParameter('sysparm_category'));
gr.query();
if(gr.next())
return gr.u_notes;
},
type: 'getAlert'
});