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
- Fields:
Reference Table:
- Customer Account (u_customer_account)
- Fields
- u_customer_account.u_customer_account_number
- u_customer_account.u_customer_account_name
- Fields
The idea is this:
- The user enters the CRM Account Number in the field
- onChange a lookup takes place which compares the "CRM Account Number" value with all known values in the existing Customer Accounts Table
- If there is a match, return the Name of the matching account and populate the Account Name form field
- 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
Something like this:
OnChange for the CRM Account number:
And for your script include:
//Göran
