Retrieving Sales Rep Quota in NetSuite Script: Step-by-Step Guide

When working in NetSuite, it’s often necessary to retrieve and manage quota information for sales representatives. In this example, we’ll show how to use SuiteQL to extract quota data for specific sales reps and map it accordingly. The provided script snippet demonstrates how to query the quota amounts and return the results in a structured way.

Key Steps:

1. Writing the SQL Query
2. The sql variable contains a SQL query that retrieves the entityId, entity name, and the mamount (monetary amount) for the quota assigned to each sales representative. Here’s a breakdown of the query:

• BUILTIN_RESULT.TYPE_INTEGER(employee.ID) retrieves the ID of the employee (sales rep).
• BUILTIN.DF(QUOTA.entity) retrieves the name of the entity (sales rep).
• BUILTIN.CONSOLIDATE(QUOTA.mamount, 'INCOME') retrieves and consolidates the monetary amount for the quota.
• FROM QUOTA, employee indicates the tables from which the data is retrieved.
• WHERE QUOTA.entity = employee.ID(+) is used to join the tables on the employee.ID and QUOTA.entity.
• AND employee.ID IN (${salesRepIdsString}) ensures that only the sales reps with specific IDs are included.

1. Running the SuiteQL Query
2. Once the SQL query is prepared, it is executed using query.runSuiteQL(). This method runs the SQL query against the NetSuite database and retrieves the results.
3. Processing the Results
4. The resultSet object contains the results of the query, and the quotaResults array holds the data returned. Each record is processed to map the sales rep’s entityId to their respective quota amount. This is done using a forEach loop, where the quota amount is parsed and stored in the quotaMap object.
5. Returning the Mapped Quota
6. Finally, the quotaMap object is returned. This object maps each sales rep’s entityId to their quota amount, making it easy to access the quota information for further processing.

Updated Code Snippet

var sql = `
    SELECT BUILTIN_RESULT.TYPE_INTEGER(employee.ID) AS entityId,
           BUILTIN_RESULT.TYPE_STRING(BUILTIN.DF(QUOTA.entity)) AS entity,
           BUILTIN_RESULT.TYPE_CURRENCY(BUILTIN.CONSOLIDATE(QUOTA.mamount, 'INCOME')) AS mamount 
    FROM QUOTA, employee
    WHERE QUOTA.entity = employee.ID(+) 
    AND employee.ID IN (${salesRepIdsString})
`;

var resultSet = query.runSuiteQL({ query: sql });
var quotaResults = resultSet.results || [];

// Create a map to store the quota amounts by sales rep entity ID
var quotaMap = {};

// Process each result and map the entity ID to its corresponding quota amount
quotaResults.forEach(function(quotaResult) {
    var entityId = quotaResult[0];       // Sales rep entity ID
    var quotaAmount = parseFloat(quotaResult[2]);  // Quota amount
    quotaMap[entityId] = quotaAmount;    // Store in map
});

// Return the map of quota amounts by sales rep ID
return quotaMap;

Explanation

• SQL Query: Retrieves the ID and quota amount (mamount) for each sales rep specified in the salesRepIdsString.
• Query Execution: The query is run using SuiteQL, and the results are stored in resultSet.
• Quota Mapping: Each result is processed, converting the quota amount to a number (parseFloat) and mapping it to the corresponding sales rep entity ID.
• Final Output: The quotaMap object is returned, which maps sales rep IDs to their respective quota amounts for easy access.

This method ensures a seamless and efficient way to retrieve and manage sales rep quotas in NetSuite through SuiteQL, enhancing your ability to analyze and utilize quota data in custom scripts.