This article is relevant if you want to learn how to retrieve street addresses from NetSuite entities that are connected to a specific project.

Background

During a recent client NetSuite implementation, the sponsor wanted to offer gratitude to our team members.  Their request was simple. “Please provide the names and home addresses of each member of the project team.” Conceptually, this is very easy to understand.  However, it’s not at all easy to answer this question using NetSuite native Saved Search.  Those that have been in the NetSuite community for a bit of time understand the multi-hop join challenge. See my related article, Learn NetSuite Two Dot Saved Search Notation for Distant Joins.

Since this was a one-time request, and now that we have a new license-free SQL tool, I decided to take a stab at writing the query using SQL.

Write NetSuite SQL Queries to obtain CSV (or JSON) Responses

Since I knew the Project internalID of our client’s project, I could have started with the Project table. But instead, I considered the Project Task as the starting point as it is a child of the Project; I just supplied the Project Task with the Project internalID as a foreign key.

Click the related image to understand the join structure. I decided to use some aliases for tables along the way to make it a bit easier as I crafted the query. Here is the resulting SQL query:

SELECT ta.resource, entity.email, entityAddressbook.defaultbilling, a.addr1, a.city, a.state, a.zip
	FROM projectTask task
		INNER JOIN projectTaskAssignee ta on ta.projecttask = task.id
		INNER JOIN entity on ta.resource = entity.id
		INNER JOIN entityAddressbook on entityAddressbook.entity = entity.id
		INNER JOIN EntityAddress a on entityAddressbook.addressbookaddress = a.nkey
	WHERE task.project = 37985 AND entityAddressbook.defaultbilling = 'T'
GROUP BY ta.resource, entity.email, entityAddressbook.defaultbilling, a.addr1, a.city, a.state, a.zip

Sharpen your SQL skills working with NetSuite

After working 10+ years exclusively in NetSuite and being “dumbed down” by Saved Search lookups, I am pleased to say that we can go back to writing SQL queries with NetSuite. Yes, we could do this if the account had the extra add-on module, Suite Analytics Connect.   We work with so many client accounts — we can’t rely on the feature activated.  But now, with our license-free SQL query tool, you can produce queries and download data in all NetSuite accounts with no extra add ons.

If you found this article meaningful, feel free to click here to get notifications of new articles as I publish them.  If you are looking for help to expand your capacities in NetSuite, we can help you extend your capacities with our license-free intellectual property, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube