Auto populate reference field based on other reference field

rickw1 · ‎08-19-2015

Hi there,

I'm figuring out the best way to do this..

In my form, I have 2 fields: caller and hospital.

Caller refers to the users table, Hospital refers to the company table.

My goal is: whenever I populate the caller field, the system needs to lookup the hospital of that caller and needs to auto-populate that.

Example: Customer X is part of Hospital X. Customer X is the caller so will be populated in the caller field. The system recognizes that Customer X is part of Hospital X so hospital will be Hospital X.

Hope you guys can help me out!

rickw1 · ‎08-21-2015

We got it working!

Client script:

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


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


           return; 


     } 


     


     var ga = new GlideAjax('GetCustomerData'); 


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


   ga.addParam('sysparm_field','u_reference_1');


   ga.addParam('sysparm_user_id', g_form.getValue('caller'));


       ga.getXML(DoSomething); 


         


       function DoSomething(response) { 


             var company = response.responseXML.documentElement.getAttribute("answer");


             g_form.setValue('company',company); 


       } 


}

Script include:

var GetCustomerData = Class.create(); 


GetCustomerData.prototype = Object.extendsObject(global.AbstractAjaxProcessor, { 


     getFieldValue: function() { 


               var user = new GlideRecord('x_medt2_ihs_customers'); 


               user.get(this.getParameter('sysparm_user_id')); 


               if(! user.sys_id){ 


                                 return false; 


               } 


         return user[this.getParameter('sysparm_field')]; 


     } 


});

Thanks everybody for the great help on this!

View solution in original post

aveguemartinpin · ‎08-20-2015

Ok Rick we are close to the solution .

Now, the only thing that you need is the name of the column of the field Caller on the form with number IHSRXXXX. I though It was the [incident] table, but maybe is a custom table.

So if you put on g_form.getReference('[NAME OF THE COLUMN]', getHospital); the above code should work.

rickw1 · ‎08-20-2015

Antonio Vegue Martinpintado wrote:

I though It was the [incident] table, but maybe is a custom table.

Nope, the record is part of table x_medt2_ihs_it_support_request,

the caller record is part of table x_medt2_ihs_customers (extended from User table) and

the hospital record is part of table x_medt2_ihs_hospitals.

Antonio Vegue Martinpintado wrote:




So if you put on g_form.getReference('[NAME OF THE COLUMN]', getHospital); the above code should work.

The name of the column of Caller? That would be caller ..

aveguemartinpin · ‎08-20-2015

Umm. Interesting. Looking at the last code you post, it seems like getRererence is returning a [sys_user] record instead of [x_medt2_ihs_customers] table. That would explain the output "Undefined".

Could you check the dictionary of field Caller? Where is it pointing to? Maybe is pointing to User[sys_user] table, so it would shows all records on the "Parent" table. Think about if you what to show the records on [sys_user] table or if you want to show records on [x_medt2_ihs_customers] table. If you want to show only records on [x_medt2_ihs_customers] table, then you should change the dictionary record making Caller field point directly to [x_medt2_ihs_customers] table instead of [sys_user].

Check it and give us a feedback!

rickw1 · ‎08-20-2015

Antonio Vegue Martinpintado wrote:

Umm. Interesting. Looking at the last code you post, it seems like getRererence is returning a [sys_user] record instead of [x_medt2_ihs_customers] table. That would explain the output "Undefined".

Could you check the dictionary of field Caller? Where is it pointing to? Maybe is pointing to User[sys_user] table, so it would shows all records on the "Parent" table. Think about if you what to show the records on [sys_user] table or if you want to show records on [x_medt2_ihs_customers] table. If you want to show only records on [x_medt2_ihs_customers] table, then you should change the dictionary record making Caller field point directly to [x_medt2_ihs_customers] table instead of [sys_user].

Check it and give us a feedback!

That absolutely right.

The thing is: I need to be able to log requests for employees and customers. Employees are in sys_user, customers are in x_medt2_ihs_customers. Caller is a reference to sys_user, but also shows records of the x_medt2_ihs_customers table. I would assume that would also work for now.

If there a way of providing a reference to sys_user and x_medt2_ihs_customers ?

aveguemartinpin · ‎08-20-2015

Ok! Look, you can do this as a temporary workaround at the moment:

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

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

return;

}

var gr = new GlideRecord('x_medt2_ihs_customers');

if(gr.get(newValue))

g_form.setValue('company',gr.u_reference_1);

else

alert('User is not a customer and does not have Hospital assigned');

}

The best solution for a good performance is, as Paul Morris and Pradeep Sharma said, create and Ajax script and call it from the client script. Check this link out http://wiki.servicenow.com/index.php?title=Client_Script_Best_Practices#Example:_Asynchronous_GlideA...

Did it work?