amHow to fetch reference field data using widgets server script

[Muru]

I am trying to create a custom widget which will take customer order number input and need to display account details of that customer order. I am struggling with fetching the customer account reference from customer order table. please is my code snippet 

(function() {

  /* populate the 'data' object */

  /* e.g., data.table = $sp.getValue('table'); */

var orders = new GlideRecord('sn_ind_tmt_orm_order');

  orders.addQuery('sys_id', '1ea9eea91b9f25104583ba63cc4bcb2e');

  orders.query();

// Instantiate the GlideRecord object for the customer_account table

var gr = new GlideRecord('customer_account');

// Add any necessary conditions to filter the records

//gr.addQuery('sys_id', 'd1ddfc501b5d2010f7c0dd33dd4bcb7e');

accountGR.addQuery('name','=',orders.getDisplayValue('account'));

// Execute the query

gr.query();

// Process the fetched records

if (gr.next()) {

  // Access the field values and assign them to variables

  var username = gr.getValue('name');

  var email = gr.getValue('email');

  var phone = gr.getValue('phone');

var address = gr.getValue('street') +','+ gr.getValue('city') +','+ gr.getValue('state') +','+ gr.getValue('zip');

var businessStructure = gr.getValue('business_structure');

var industry = gr.getValue('industry');

  // Pass the values to the widget's client script or HTML template for rendering

  data.username = username;

  data.email = email;

  data.phone = phone;

data.address = address;

data.businessStructure = businessStructure;

data.industry = industry;

}

})();

any idea why its not working ?

[Muru · Accepted solution]

Replaces order.customer_account with order.account 

its working now. Thanks a lot for your help 

[Sai Shravan]

Hi @Muru ,

Below is updated code, can you please try once

(function() {
  /* populate the 'data' object */
  /* e.g., data.table = $sp.getValue('table'); */

  var orders = new GlideRecord('sn_ind_tmt_orm_order');
  orders.addQuery('sys_id', '1ea9eea91b9f25104583ba63cc4bcb2e');
  orders.query();

  // Instantiate the GlideRecord object for the customer_account table
  var gr = new GlideRecord('customer_account');

  // Add any necessary conditions to filter the records
  // gr.addQuery('sys_id', 'd1ddfc501b5d2010f7c0dd33dd4bcb7e');
  
  // Fetch the customer account reference from the customer order
  if (orders.next()) {
    var accountRef = orders.customer_account;

    // Query the customer_account table using the accountRef
    if (accountRef) {
      gr.get(accountRef);

      // Process the fetched record
      if (gr.isValid()) {
        // Access the field values and assign them to variables
        var username = gr.getValue('name');
        var email = gr.getValue('email');
        var phone = gr.getValue('phone');
        var address = gr.getValue('street') + ',' + gr.getValue('city') + ',' + gr.getValue('state') + ',' + gr.getValue('zip');
        var businessStructure = gr.getValue('business_structure');
        var industry = gr.getValue('industry');

        // Pass the values to the widget's client script or HTML template for rendering
        data.username = username;
        data.email = email;
        data.phone = phone;
        data.address = address;
        data.businessStructure = businessStructure;
        data.industry = industry;
      }
    }
  }
})();

Added a check using if (accountRef) to ensure that the accountRef value exists before querying the customer_account table.

Regards,

Shravan

Please mark this as helpful and correct answer, if this helps you

[Muru]

Hi Shravan,

Still account details are not coming. I have verified order number against given sys_id is having account filed with proper value

[Sai Shravan]

Hi @Muru ,

Add some logs to your code snippet to identify the issues.

if (orders.next()) {
  gs.info('Order found with sys_id: ' + orders.sys_id);
  var accountRef = orders.customer_account;
  gs.info('Account Reference: ' + accountRef);

  // ...
}

Instead of using gr.get(accountRef), try executing a direct query on the customer_account table using the account reference (accountRef) to see if any records are returned:

if (accountRef) {
  gr.addQuery('sys_id', accountRef);
  gr.query();

  if (gr.next()) {
    // ... Process the fetched record ...
  }
}

[Muru]

Order found with sys_id: 08746ae11b972510f7c0dd33dd4bcb78
Account Reference: undefined