Using AI to help craft complex SQL in NetSuite Saved Searches

Lots of creative and technical folks are showcasing really amazing examples for how AI can be used to create really amazing outputs.  And that’s great!  But I find that the average business leader is still having a hard time figuring out practical applications of AI tools to build legitimate points of efficiency beyond some written copy.  To that end, I’ve been creating a series of small posts to highlight some small, practical ways that we’ve leveraged AI for ourselves and our clients to earn some incremental wins in the AI space.  To be a success the tool doesn’t have to be revolutionary.  But if we’re able to incorporate AI and automation as a habit in an organization, those savings of a couple hours here and a couple hours there, over time, add up to organization wide productivity gains.   

This example is one we’ve used for ourselves and our clients a LOT in the last couple years. 

I love NetSuite.  I think it’s one of the greatest and most flexible pieces of SaaS software in the mid-market space today.  Part of what makes NetSuite great is the combination of user interface controls for most day-to-day things, with more sophisticated development tools for achieving more complex and unique objectives.  

Saved searches are a great example of this.  The saved search is a really powerful tool that lets you query just about any record type in NetSuite.  You pick the record type (ie: Transactions), apply some criteria (ie: only transactions matching rules 1, 2, and 3), and then define the results you want to see (for matching transactions, I want these 10 attributes).  Where saved searches get really powerful is when you leverage the “formula” Criteria or Result option.  

Formulas in NetSuite saved searches allow a user to define SQL formulas as a part of criteria, results, or conditional highlighting.  If you’re fluent in PL SQL (Oracle's flavor of SQL used in NetSuite), you can extend far beyond the point-and-click configuration of the user interface.  But as with all things NetSuite, there are of course limitations (sometimes maddening limitations).  Historically, teams either needed to have an in-house person who was really solid with PL SQL, or to pay an external consultant to help on an ad hoc basis.   

Here are a couple examples that showcase how you can leverage AI tools as a productivity boost for creating complex NetSuite saved searches.  In these particular examples, we’re getting a double boost – we're getting more value out of NetSuite through leveraging more sophisticated capabilities, and we’re doing it more quickly and efficiently than we could have in the past. 

One thing to note:  In all of these examples, you’ll need to know the internal ID of the fields you’re working with.  In a Saved Search you can do that via the field picker in the formula modal.   

Example 1:  Combining two results into a single column 

Screen real estate is always at a premium when displaying data to users.  Sometimes, especially with Dashboard Portlets in NetSuite, we need to limit the number of columns we present.  One trick to doing this can be combining (or concatenating) multiple values into a single result column.  For example:  I’m making a list of open Projects for a Project Manager’s dashboard.  I want to display the originally planned Start Date and originally planned End Date for all those projects, so I can see if we’re drifting on our timeline.  Combining those into a single column can be really helpful for saving space in my Dashboard portlet.  There are multiple ways of doing this, but we’ll go with the simple one here.  Here’s the prompt I used in ChatGPT:  

I'm in NetSuite, creating a saved search. I want to combine together two fields into a single result column, stacked on on top of the other, with this format:  

Value  

Value  

The fields are Original Start Date and Original End Date. What is the SQL formula I would use to accomplish this? 

The resulting formula that came back was:  

{custentity_orig_start_date}||’<br>’||{custentity_orig_end_date} 

A couple things to note:  

• I’ve asked ChatGPT similar questions over time, and get some variation here.  Instead of the ‘<br>’ for a line break, I’ll often get (Chr)10. I’ve found that works as well.  
• If you’re using Date fields, you probably want to specify that in your results, so that you’ll get back a SQL formula that converts the dates to text strings that can be manipulated for display properly.  

Example 2:  Using a CASE statement to conditionally display results 

There are a lot of examples where I only want information to display in my results if it’s relevant.  We see this come up a lot with Time Entries, where I only want to sum Billable time or Non-Billable time, so I can easily see the totals simultaneously (without having to flip back and forth using the Available Filters feature).   

To accomplish this, we want to use a SQL CASE statement (if this, then that).   

Here’s the prompt I put into ChatGPT:  

I'm in NetSuite creating a saved search of Time Entries. If the time entry is billable, I want to show the duration of that time entry (duration decimal). If the time entry is non-billable, I want to return 0. My ultimate goal is that I can check the "Show Totals" checkbox on my saved search, and the column will tally all of the Billable Time entries. Can you help me create a SQL formula to accomplish this? 

The resulting formula that came back is below. Note that chatGPT also directed me to the correct formula type (Numeric) to support my output goals.    

CASE  

WHEN {isbillable} = 'T'  

THEN {durationdecimal}  
ELSE 0  

END 

This is probably one of the most frequently used formulas I use in NetSuite.  I find that I use it (or a variation on other fields) multiple times in every client’s account.  Being able to leverage this structure allows us to quickly pivot and analyze data, in addition to provide helpful metrics more efficiently to our users on an ongoing basis.   

Example 3:  Using Date formulas as criteria to narrow our results 

The first two examples were about the output or results of our search.  This final example is about narrowing the list of results by building more sophisticated criteria.   

In some use cases, the user won’t be able to use filters on a search to dynamically change the date range.  Examples include dashboard metrics or saved searches uses to populate fields on records.  In these scenarios, we have to build the logic into the criteria of the search so that every time it runs it provides us with the correct information.  

We have a client that wanted to build a series of dashboard metrics based on utilization.  Their utilization data was driven from Time Entries.  While NetSuite offers a number of out-of-the-box values for how to filter date-based records, their needs were keyed to a specific 4-week lookback period.  The client wanted the data based on Week, and only wanted to evaluate data from the Monday four weeks ago, through the Sunday of the week that just ended.  NetSuite’s standard date filters don’t allow for this level of precision, so we opted to build a SQL formula for our criteria instead.   

Here’s the prompt we used in ChatGPT:  

In a NetSuite saved search on the Project record, I want to include only projects that have time entries logged in the past 4 full ISO weeks (Monday to Sunday), up through the current week. Write a Formula (Numeric) that returns 1 if the associated time entry date is within that date range, and 0 otherwise. I’ll then use this formula in the criteria to filter for records where the formula is 1. 

Because we’re using this as criteria, we don’t want just any result.  There are two key elements we’re calling for here.  

The first is that we’re leading ChatGPT down the path of a case statement with a 1 or 0 as the only outcomes of the formula, which allows us to specify this as our criteria.  In this case, we’re only returning results where the formula results in 1.   

The second is that we’re using ISO weeks.  This will ensure that we’re evaluating to the week of the year, instead of to a specific relative number of days in the past.  When a user looks at this metric on a Tuesday or a Thursday, they’ll get the same results because we’re still looking back an equal number of weeks.   

Here’s the formula ChatGPT provided us:  

CASE  

WHEN {time.date} <= TRUNC({today}, 'IW')+6  

AND {time.date} >= TRUNC({today}, 'IW')-28  

THEN 1  

ELSE 0  

END

Basically if the time entry date is within our specified window of time, it returns the number 1.  If it’s not, it returns 0.  And our criteria in the Saved Search only displays results when this formula results in 1.   

Ultimately the same approach can be leveraged in a thousand use cases in NetSuite.  You can imagine using this principle in Analytics workbooks to create custom data points, in Workflows to create complex logic for action triggers, and in custom fields populated via saved search to tally specific data from a record into a visible data point on the record for users to see.   

In the past, a user only had a couple options for this type of sophistication.  Potentially the company was large enough to have someone on staff with this knowledge, and the request would go in their queue.  Alternatively, they would pay an outside consultant to create this logic on their behalf.  Both are expensive options.  By leveraging AI, we’re able to implement customized PL SQL in NetSuite saved searches much more efficiently, providing the double efficiency boost of both getting the work done faster, and cheaper, while also getting more out of NetSuite.