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?

[ServiceNowSteve]

I setup this code as instructed and it didn't return anything. I think I found one issue:

if(g_form.getValue('category') == 'Incident Alert')  <~~~This client script runs on the incident table but this Incident Alert is on the u_customer_alerts table so it will never find it.

what I need is for it to search the u_customer_alerts table and find a match based off of the company field of the incident form then further limit the results to records on the u_customer_alerts table with a type of "Incident Alert" 

[Shishir Srivast]

I sensed that earlier and modified the code (which i mentioned in this thread). Can you please check if that helps.

[ServiceNowSteve]

I tried the modified code but ultimately nothing happened. Since I don't understand the code it's hard for me to pin point what's wrong. My only idea of what went wrong is that I added the client script using an onChange of my company field which you didn't say to do but I didn't know what type of client script you wanted.

[Shishir Srivast]

Apologies for that, yes it should be onChange of my company field. Please check if you have client callable checkbox checked in script include.

As per my understanding if you want to fetch data from table based upon category and company name then this should work.

Is u_client_profile a reference field in u_customer_alerts table? if yes then let's try with below client script with same client callable script include.

Client Script:

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
if (isLoading || newValue === '') {
return;
}

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

Let me know if it's your personal dev instance, I can have a quick look. 

[ServiceNowSteve]

Client Callable is checked and u_client_profile is a reference field in the u_customer_alerts table so I modified the code accordingly but this still did not return anything. 

I also commented out the Incident Alert line for now until I get it working.

I am afraid it is not a personal dev instance