Published on February 22, 2021.

I recently developed a custom Web portal for one of my NetSuite clients. The portal, which is for their sales team, included functionality for managing leads. This required developing a few SuiteQL queries, and I thought I'd share a few of those queries in this post.

Leads For A Specific Sales Rep

When sales reps log into the portal, they're presented with a list of the leads that they've been assigned. This SuiteQL query takes the sales rep's employee ID and returns their leads.

SELECT 
	Customer.ID AS CustomerID, 
	Customer.CompanyName,
	Customer.AltName, 
	Customer.IsPerson, 
	Customer.LastName, 
	Customer.FirstName, 
	Customer.Title, 
	Customer.Email, 
	Customer.Phone,
	Customer.DateCreated, 
	Customer.LastModifiedDate
FROM 
	Customer
WHERE
	( Customer.SearchStage = 'Lead' )
	AND ( Customer.SalesRep = 7779311 )
ORDER BY
	Customer.DateCreated DESC,
	Customer.CompanyName,
	Customer.AltName

The query is based on the Customer table, and requires filtering on the SearchStage column for customers in the "Lead" stage. It's a fairly straightforward query.

Lead Summary By Sales Rep

One of the functions that the portal provides to sales managers is a summarized list of sales reps and the number of leads that they've been assigned. Here's the SuiteQL query that the function is based on.

SELECT
	Employee.LastName,
	Employee.FirstName,
	COUNT( Customer.ID ) AS LeadCount
FROM
	Employee
	LEFT OUTER JOIN Customer ON
		( Customer.SalesRep = Employee.ID )
		AND ( Customer.SearchStage = 'Lead' )
WHERE
	( Employee.IsInactive = 'F' )
	AND ( Employee.IsSalesRep = 'T' )
GROUP BY
	Employee.LastName,
	Employee.FirstName
ORDER BY
	Employee.LastName,
	Employee.FirstName

This query is based on employees, and specifically active employee that are configured as sales reps. I've used a LEFT OUTER JOIN to the Customer table, with the join based on the employee assigned to the customer, and as I did in the query above, I'm filtering for customers where SearchStage is "Lead."

Wrapping Up

These are just two of many SuiteQL queries that I used for this project. I'll share additional queries in future posts.

If you have any questions about the queries that I've shared in this post, or SuiteQL in general, please feel free to contact me.