This article is relevant if you need to schedule sending emails of NetSuite SuiteQL (SQL) queries just like you can with Saved Search.
Background
In 2021, I discussed how you can now take a NetSuite SQL Query and render it just like you can with a Saved Search. We received a strong response for this new capacity in our existing client community. Naturally, as our clients began to enjoy the benefits of connecting NetSuite SQL queries to forms to use as sublists and use them like saved searches, they then asked logical next questions. “Can I email the NetSuite SQL query like I can the Saved Search?”
That question led us to a number of internal discussions. We have another tool, the Content Renderer Engine, that allows us to mix and match linked Saved Searches, SQL Queries, record loads, and even other https/file retrieved content to define a data universe that can then be highly formatted for presentation and distribution. See my 2020 article where I show you how you can use this tool to get control over NetSuite’s reply-to address.
Thus, we effectively could answer our client’s questions immediately because we could take a SQL query and distribute it by moving the SQL query to the Content Renderer Engine, formatting it, adding distribution information and finally creating a scheduled script to execute.
However, we took a different approach when working with Boban D., a senior technical analyst on our team.
Using the Prolecto Query Renderer to Send NetSuite SQL Queries
While we had a solution with our existing tools, we considered how our clients were thinking about this new free-of-license-charge tool. In their minds, the tool was a replacement capacity for NetSuite Saved Search. Thus, they wanted relative ease to work in that mode of thinking. So here is what we need to extend the Query Renderer.
Scheduling NetSuite SQL Email
NetSuite’s native scripts offer a natural scheduler capacity definition with options for timing and frequency. Thus, we started with that definition and designed several parameters that would allow an administrator to get the same, if not more, capacity than NetSuite’s native saved search. Here are the parameters:
- Query: The reference to an existing SQL query definition to be rendered and emailed.
- Sender: The sender of the email. You can use either an integer that is the internal id of an employee or a reference like {x} which means that the first value found in column “x” in the result set. That makes it dynamic.
- Recipients (and CC): The list of recipients for the email. A comma-separated list that consists of either literal email addresses, or integers (which represent the internal id of an entity, or references like {x} which extracts all unique values from column “x”. For example, anon@gmail.com,3,{email} where {email} will include all the people in the result in the “email” result set. CC works the same as recipients; you are simply manipulating the recipients’ message role.
- Subject: a description of the email subject. It would be easy to make this dynamic when needed.
- Email Body: similar to the Subject but will be used at the top of the email message before delivered results are also pushed into the body (if applicable).
- Query Results Format: CSV, HTML and JSON are currently supported. NetSuite native currently has the advantage because it can send Excel. But this advantage would be easy to close because NetSuite’s Excel format is simply based on Excel XML 2004. See my 2019 related article, Learn How to Teach NetSuite to Generate Dynamic Excel Spreadsheets.
- Send Email If No Results?: If checked, it will send an email even if the query produces zero results.
- Results as Attachment?: If checked, the result set will be attached as a file to the email. Since it uses attachments, we specify where the file’s filename and folder should be stored (see subsequent parameters). If not checked, the results will appear inline in the Body of the email below the description (discussed above).
- File Name: If the results are to be attached, provide a filename for the attachment; it can include variables for the various date/time fields. For example: sample_file_{yyyy}_{mm}_{dd}.csv
- Folder Name: like the File Name, the folder where the file should be stored. For example: /temp/sample/{yyyy} where {yyyy} would equal the date year.
- Filters: we link to the Query Render’s definition of filters so that we can pass in additional data to filter the results. While more technical JSON format, we can specify something like {“customer”: 1234} to indicate a customer with the internal ID of 1234 so that the actual query definition remains flexible.
Click the related to see a definition in play.
NetSuite SQL Results Arrive as Email
Once you have the definition, you can test it by scheduling an immediate email and reviewing the execution log to confirm the results. After you are satisfied with the setup, update the desired schedule and effectively forget it.
Perhaps you are the type of individual that seeks to be appreciated for your ability to invent solutions on the NetSuite platform. You also desire to work with a high-caliber team committed to holding high standards for care. Or do you simply want to be connected with professionals who value a great working relationship but do not want to pay for already existing software? If this resonates with you, let’s have a conversation.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them.
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
