Example script that queries the Opportunity table

  • Release version: Xanadu
  • Updated August 1, 2024
  • 2 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Example Script that Queries the Opportunity Table

    This ServiceNow example script demonstrates how to query the Salesforce Opportunity table by utilizing three custom spoke actions:Get All Opportunities,Get Opportunities for Account Id, andGet Opportunity Details. The script is designed to integrate Salesforce data into a ServiceNow remote table, enabling customers to retrieve and display relevant opportunity records within their ServiceNow environment.

    Show full answer Show less

    How the Script Works

    The script operates in three main stages:

    • Selecting the spoke action and preparing inputs: Based on the parameters passed in the vquery argument, the script selects the appropriate Salesforce spoke action to call. It supports querying all opportunities, opportunities filtered by Salesforce Account ID, or details for a specific opportunity by its Salesforce record ID. It includes validation to handle cases where the Salesforce account ID is undefined, preventing unnecessary queries and informing the user accordingly.
    • Calling the spoke action: The selected action is invoked through the snfd.FlowAPI.executeAction method, passing the prepared inputs to retrieve opportunity data from Salesforce.
    • Processing the action outputs: The script checks for errors in the action response, throwing an error if needed. When successful, it iterates over the returned opportunity records and maps Salesforce fields such as Amount, CloseDate, Name, Probability, AccountId, StageName, Type, and Id to corresponding columns in the ServiceNow remote table. The Salesforce record ID is assigned to the remote table’s sysid to maintain consistent record identification across queries. Any informational messages returned by the action are displayed to the user.

    Error Handling

    The entire script runs inside a try-catch block, which catches exceptions that may occur during the query process. Errors are logged as messages for the user with instructions to contact the system administrator, ensuring transparency and easier troubleshooting.

    Practical Benefits for ServiceNow Customers

    • This script enables seamless integration of Salesforce Opportunity data into ServiceNow, supporting real-time data retrieval and display within remote tables.
    • By selecting the appropriate query dynamically, it provides flexibility to retrieve comprehensive opportunity lists, filter by account, or access detailed opportunity records.
    • Error checking and informative messaging improve user experience by clearly communicating issues related to missing Salesforce account relationships or action failures.
    • Assigning Salesforce record IDs to the remote table sysid ensures that record references remain consistent, enabling features like forms and lists to function correctly.

    This example script queries the opportunity table using the Get All Opportunities, Get Opportunities for Account Id, and Get Opportunity Details custom actions.

    The example script consists of three distinct parts:
    1. The first part selects the correct custom action and prepares inputs for it.
    2. The second part makes a call to the action.
    3. The third part processes the outputs of the action.

    Selecting a spoke action and preparing the inputs

    In this section of the script, select one of the three custom actions that you prepared to get opportunities from the Salesforce application:
    • Get All Opportunities
    • Get Opportunities for Account Id
    • Get Opportunity Details
    You can decide which action to call based on the parameters included in v_query function argument.
    /****** Choose action and prepare action inputs *****/
var action = null;
var inputs = {};

// look up opportunity by salesforce record id
if (v_query.isGet()) {

  action = "get_opportunity_details";
  inputs.salesforce_opportunity_record_id = v_query.getSysId();
      

// look up opportunities by salesforce account id
} else if (v_query.getParameter("u_sf_account_id")) {

    if (v_query.getParameter("u_sf_account_id") == "undefined") {
      gs.addInfoMessage(“Opportunities cannot be retrieved because “ +
                        “this “Account does not have associated “    +
                        “Salesforce Account. Please contact System “ +
                         “Administrator.");
      return;

    } else {
      action = "get_opportunities_for_account_id";
      inputs.salesforce_account_id = v_query.getParameter("u_sf_account_id");
    }

// look up all opportunities
} else {
  action = "get_all_opportunities";
}

    Note that this script configures an information message if the Salesforce account is undefined when it is required by the action. The undefined value comes from the relationship that is described in Using a related list to create the connection between the Customer Account and the Salesforce Opportunities.

    When the Salesforce account is undefined, there is nothing to query for in this case and the function returns without calling the spoke action.

    Calling the spoke action

    In this section of the script, call the action using the names of the Salesforce spoke and the selected action and store the outputs of the call.

    /***** Call action *****/
var outputs =
        sn_fd.FlowAPI.executeAction("sn_salesforce_spok." + action, inputs);

    Processing the action output

    In this section of the script, process the outputs starting with the check for errors.

    /***** Process action outputs *****/
if (outputs.status != "Success") {
throw new Error(outputs.error_message);
}

    If the query does not return any errors, the script needs to process the returned records and add them as rows into the remote table. Map the Salesforce Opportunity fields into the remote table columns.

    var opportunities = outputs.opportunities.data;

 for (var i = 0; i < opportunities.length; i++) {
  var opportunity = opportunities[i];
  v_table.addRow({
    "u_sf_amount": opportunity.Amount,
    "u_sf_close_date": opportunity.CloseDate,
    "u_sf_name": opportunity.Name,
    "u_sf_probability": opportunity.Probability + "%",
    "u_sf_account_id": opportunity.AccountId,
    "u_sf_stage": opportunity.StageName,
    “u_sf_type": opportunity.Type,
    "sys_id": opportunity.Id,
  });
}

    Note that the Salesforce opportunity record Id is assigned to the remote table sys_id. This ensures that lists and forms for the remote table function properly and that we are able to extract the record Id using v_query.getSysId() the next time that the remote table script is invoked.

    Then display the information message if it was passed by the query.

    if (outputs.info_message) {
  gs.addInfoMessage(outputs.info_message);
}

    Putting the remote table script sections together

    The three sections of the script are included in the try-catch block to provide for error handling.

    (function executeQuery(v_table, v_query) {

  try {

    // place code here from: <Selecting a spoke action and preparing the inputs>

    // place code here from: <Calling the spoke action>

    // place code here from: <Processing the action output>  	
        
  } catch (error) {
    gs.addErrorMessage("Error retrieving  Salesforce Opportunities. “ +
                       “Please contact System Administrator.");
    gs.addErrorMessage("System Error: " + error.message);
  }

})(v_table, v_query);