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

Daryll Conway · ‎08-19-2015

You can create an on change client script on the Caller field something like this...

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


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


     return;


   }


     var gr = new GlideRecord(<your_hospital_table>);


     gr.addQuery(<matching_field>, newValue);


     gr.query();


     if(gr.next()){


   g_form.setValue(<your_hospital_field_on_the_form>, gr.sys_id);


   }


}

rickw1 · ‎08-19-2015

Thanks everybody for the great help so far!

So what I'm reading is that a Business Rule or Client Script will do the job.

What would be the main difference between those two?

If a record would be created by the inbound email script instead of manual creation, would both solutions work too?

Daryll Conway · ‎08-19-2015

A client script will make the change on the form as you go where as a business rule will update upon save / submission

Daryll Conway · ‎08-19-2015

Also a client script is less efficient you may notice more of a lag when using client side lookups as it'll have to query the server.

A business rule will do all this on the server so load times will be quicker.

Really depends how big the tables are and whether you want live updates for the user.

Ashish Kumar Ag · ‎08-19-2015

If the record will be created through inbound email script, better go with BR.