User input to trigger a lookup, data match and setting of a different field value

[dmj]

Hi All,

I would like to see if any of you can help with a challenge I am working through.   I have a form where I am attempting to align a user supplied account number from one table, with the existing customer account record from another table and I am having trouble sorting out the best way to do this.  

To give you some context, this is part of an import set which is retrieved on a scheduled basis and then a provisioning engineer needs to reconcile customer sites with their hardware using the account number supplied from the provisioning process.

The components are as follows:

Primary Table:

• m_network
  • Fields:
    • CRM Account Number (m_crm_account_number)
    • Account Name - Reference - Customer Account

Reference Table:

• Customer Account   (u_customer_account)
  • Fields
    • u_customer_account.u_customer_account_number
    • u_customer_account.u_customer_account_name

The idea is this:

1. The user enters the CRM Account Number in the field
2. onChange a lookup takes place which compares the "CRM Account Number" value with all known values in the existing Customer Accounts Table
3. If there is a match, return the Name of the matching account and populate the Account Name form field
4. If there is no match post an error

I am assuming this is best done in a server side script however I have tried various permutations of script includes, business rules and client scripts yet I cannot seem to get the results I am looking for.   Either I only get List Lookup selections, "incorrect reference" messages or nothing...

Here are some samples of a few of the attempts just to see if I am on the right track or totally off the reservation...

Business Rule:

------------------------------------------------------------------------------------------------------------------------

function executeRule(current, previous /*null when async*/) {

// get the corrisponding customer account   sys_id

var crmAccount = current.m_crm_account_number;

var custAccount_sys_id = current.m_account_name.u_customer_account_sys_id;

// check to see if a matching customer account exists in this system

var custLookup = new GlideRecord('cmdb');

custLookup.addQuery('u_customer_account_number', custAccount_sys_id);

custLookup.addQuery('u_customer_account_name', custAccount_sys_id);

custLookup.query();

var custAccountName = custLookup.u_customer_account_name;

if (crmAccount == custLookup.u_customer_account_number){

g_form.setValue('m_account_name', custAccountName);

gs.info('Success, the accounts match for CRM Account {0} and Customer Account {1}',crmAccount,custAccountName);

current.setAbortAction(true);

return;

}

return;

})(current, previous);

----------------------------------------------------------------------------------------------------------------------------------

Client Script:

-------------------------------------------------------------------------------------------------------------------------------------------------

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

var crmAccount = g_form.getValue('m_crm_account_number', loadManf);

}

function loadManf(m_crm_account_number) {

if (m_crm_account_number.crmAccount.m_account_name != null)

      g_form.setValue('m_account_name',m_crm_account_number.crmAccount.m_account_name);

}

-------------------------------------------------------------------------------------------------------------------------------------------------

Any help is much appreciated!!!

Thank you - DJ

[dwada1]

Hi DJ,

For this you'll want a client script which makes a GlideAjax call to a Script Include. I've written a skeleton here which hopefully is somewhat useful for you.

Client Script:

function onChange(control, oldValue, newValue, isLoading) {
var crmAccount = g_form.getValue('m_crm_account_number');

var ga = new GlideAjax('CustomerAccountUtil');
ga.addParam('sysparm_name','accountLookup');
ga.addParam('sysparm_account',crmAccount);
ga.getXML(getCallback);
}




function getCallback(response) {
var answer = response.responseXML.documentElement.getAttribute("answer");

if (answer != null)
g_form.setValue('m_account_name',answer);
}

Script Include:

var AccountUtil = Class.create();
AccountUtil.prototype = Object.extendsObject(AbstractAjaxProcessor, {
         accountLookup: function() {
                   var accountNumber = this.getParameter('sysparm_account');
         // Query Customer Account table and use accountNumber to find a match. Return sys_id of Customer Account record to client script
},
       type: 'AccountUtil'
});

[Goran WitchDoc · Accepted solution]

Something like this:

OnChange for the CRM Account number:

And for your script include:

//Göran

[dmj]

Works great with a few tweaks!!

Thank you very much Goran!   Very much appreciated.

DJ

[Sai Anna]

small doubt before answering, One of your image says CRM Account Number as Reference field, and dictionary entry shows it as String. can you please give clear table name, field names and types. I can help with Script

Thanks,

Sai