A Linchpin's Journey

REGEXP_COUNT is quite interesting, especially when you combine it with NS_CONCAT. Doing so will allow you to count the number of elements in the NS_CONCAT results, allowing for further flexibility to your searches. Further, you can perform mathematical operations between multiple REGEXP_COUNT expressions. You'd normally think to create a calculated measure in the Analytics Workbook in order to perform this, but it appears there are times when a saved search is all you need. Here's an example: Say you want the search to show each sales rep, and the number of customers they have that are from referrals (marked via referral checkbox in the customer record). Sales orders, and phone calls have the same checkbox, and you want to count these too. To add, you have another checkbox, that indicates if an upsell was offered. The checkbox appears in sales order, call, and task records. Then you'd also want to count each instance the checkbox is marked. Using NS_CONCAT(DISTINCT) will re...

Customizing the standard Campaign Response Summary report has its limits. Meanwhile, you can create a Campaign search, allowing you to add formulas in the criteria, and get much more targeted results. Once you have defined your criteria, head over to the Results tab and simply follow the table below. Field Summary Type Formula Summary Label Recipient Group     Campaign ID Count     Response Detail       Formula (Numeric)...

User wants to send an email that pulls the creator of a sales order via Freemarker. It is currently not possible to pull the creator of a sales order onto an email without customization. Navigate to Lists > Search > Saved Searches > New Click Transaction Search Title: Enter a title Public: Enter Checkmark Click Criteria Filter: Select System Notes System Notes Filter: Select Type Type: Select Create Click Set Click Results Click Remove All Field: Select Formula (Text) Summary Type: Select Maximum Formula: Enter {systemnotes.name} Click Available Filters Filter: Select Internal ID Click Save & Run Navigate to Customization > Lists, Records, & Fields > Transaction Body Fields > New Label: Enter a label Type: Select Free-Form Text Store Value: Remove Checkmark Click Applies To Sale: Enter Checkmark Click Display Subtab: Select Main Display Type: Select Inline Text Click Validation & Defaulting Search: Select the search that was created Click Save View any Sal...

Saved Searches have the capability to send email alerts when records are created or updated. But what if you only need email alerts to be sent when records are updated. To be more specific, if you want to be notified when the Sales Rep field on a customer record is updated, then you would set the relevant fields like so: If you try this setup, you will likely see that notifications still get sent when records are created. Shouldn't adding a field on the Updated Fields tab prevent that from happening? To explain this behavior, it is likely that once the record gets created, Sales Territories takes over and updates the record and sets the Sales Rep field immediately. This would be considered an update, and therefore would trigger the email alert, at the same time as the date created . The timestamps between the update and the date created are so similar, that they appear to be the same in the system notes. This makes us believe that the email is sent on create, when in reality, the e...

Let's begin with a simple query. In an opportunity search, one field aggregates Estimated Gross Profit of each line item, while another aggregates the Actual Profit of each line item. Let's label each field {custcol1} and {custcol2}. We set the summary types to Sum. Simple. Now we'd want a Formula (Currency) that returns {custcol2}, but if that has 0 or null values, we return {custcol1}. So... case when nvl({custcol2},0) = 0 then nvl({custcol1},0) else nvl({custcol2},0)) end ...would work, but would likely cause duplicates. Using max on each variable could be a solution to that, but then again, there could be some opportunities with multiple line items. Using max would only get the biggest of those line items, missing out on the smaller lines. A better solution would be using sum distinct instead: case when sum(distinct nvl({custcol2},0)) = 0 then sum(distinct nvl({custcol1},0)) else sum(distinct nvl({custcol2},0)) end In most cases, this would work. But there would be extr...

Okay, so here's an interesting dilemma. A customer created an item saved search using the joined fields Inventory Number/Bin on Hand and, Inventory Number. He wanted to retrieve the Expiration date from the Lot Number Record, but that resulted to each Lot Number Record compare itself to each Bin. This in turn bloated the results from 8 rows to 40. Pulling 3 dimensions to a search, can get messy, since search results are only capable of returning 2 dimensions. Luckily, there's a solution. It seems you have to find fields from each record type that return the same value. This would be your primary key. Then, you use that key as a condition on a case when statement. case when {inventorynumber.inventorynumber} = {inventorynumberbinonhand.inventorynumber} then {inventorynumber.expirationdate} end Also set the Summary Type to Maxmimum for that formula, then Group the rest of the fields. The down side is, since setting summary types forces drill down reports; you can't easily acce...

I've just actually recently got into using the function regexp_substr. It didn't seem very relevant because most SQL functions already capture what customers are looking for. Until now. Someone was looking for a formula that would return either the smallest value, or next smallest, depending on some criteria. While I wasn't able to capture the entire request, I thought of a similar problem: How can I add the amounts of the two lowest SOs per customer? The first thing that came to mind was that I needed to aggregate and rank the SO amounts using listagg. So that's what I did.  listagg({amount}, ' ') within group (order by {amount} asc) Then, I thought of using my recent learnings on regexp_substr. It turns out, that actually did it. These were the formulas: REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 1) REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)...

 This search shows a comparison of transaction amounts in YTD vs Rolling Year. YTD case when to_char({trandate},'YYYY') = to_char({today},'YYYY') then {amount} end Rolling Year case when {trandate} >=  to_date('01-'||to_char(add_months({today},-12),'MM-YYYY'),'DD-MM-YYYY')  then {amount} end

In this example, we will be looking at a Role Search that returns roles with the Make Journal Entry and the Journal Approval permissions, with Edit/Full and View levels respectively. In the Criteria > Summary, we can simply add a Formula (Numeric) filter where the value is equal to 1. The formula contains the combinations we would like to include in the search. For instance: case when ns_concat({permission}||{level}) = 'Make Journal EntryEdit,Journal ApprovalView' or ns_concat({permission}||{level}) = 'Make Journal EntryFull,Journal ApprovalView' then 1 end Results are pretty straightforward. We may need to use summary types because of the ns_concat formula.

I started taking the Python Essential Training course from LinkedIn Learning. The challenges are getting harder and harder, but I managed to do them without any help. The codes I made were not the most efficient ones, but at least they did the jobs. Below are the codes I made for each challenge: Description Code Factorial Gets the factorial of any integer. def factorial(num): if type(num) == int: if num >= 0: i = 1 result = 1 while i Hex to decimal converter Converts hexadecimal strings to decimals. def hexToDec(hexNum): valid = True result = 0 a = 0 for item in hexNum: if not item in hexNumbers: valid = False if not valid: print(None) else: for item in hexNum: a += 1 result = result + 16**(len(hexNum)-a)*hexNumbers[item] print(result) hexToDec('ABC') ...

I've come up with two ways to create this search. I prefer the easy way, which is already seen in the images below. That's it. No need to read further. The hard way is just an experiment (and flex) on using nested case whens inside a listagg function. The search uses listagg so that for each transaction, there would only be one row with a compiled list of items, instead of having one transaction having multiple rows of items. The search also hides expense lines. Add the following code in a Formula (HTML) field in the Results tab. Set the Summary Type to Maximum. '<table width=500>' || listagg(distinct( case when {item} is not null then case when {applyingtransaction.type} = 'Item Receipt' then case when {applyingtransaction.quantity} < {quantity} then '<tr><td width=50%>' || {applyingtransaction.item} || '</td><td width=20%>' || to_number({quantity}-{applyingtransaction.quantity}) || '</td...

When using NetSuite online forms with HTML templates, you can add standard dropdown fields. Sometimes standard dropdown fields can contain options that you don't want included. An easy workaround for this, is to use the script tag in the HTML of your template to remove those options. The following example shows how to remove some states from the state field. Since selectedIndex represents the rank or order number an option is from a dropdown, every line that deletes, changes the selectedIndex of the succeeding lines appropriately. <script>   document.getElementById("state").remove(document.getElementById("state").selectedIndex = "5");   document.getElementById("state").remove(document.getElementById("state").selectedIndex = "5");   document.getElementById("state").remove(document.getElementById("state").selectedIndex = "5");   document.getElementById("state").remove(document.ge...

Imagine having two promotions, whose coupon codes are "ItemPromo1" and "ItemPromo2". If you want to find sales orders who have both promotions applied, do the following: Navigate to Lists > Search > Saved Searches > New Click Transaction Search Title : Enter a title Click Criteria Click Standard Filter : Select Main Line Main Line : Select Yes Click Set Select Type Type : Select Sales Order Click Set Click Summary Summary Type : Select Maximum Field : Select Formula (Numeric) Formula : Enter instr(NS_CONCAT(Distinct({promocode}||{promoapplicabilitystatus})),'ItemPromo1Applied') Formula (Numeric) : Select greater than Value : Enter 0 Summary Type : Select Maximum Field : Select Formula (Numeric) Formula : Enter instr(NS_CONCAT(Distinct({promocode}||{promoapplicabilitystatus})),'ItemPromo2Applied') Formula (Numeric) : Select  greater than Value : Enter 0 Click Set Click Results Click Remove All Field : Select Document Number Summary Type : Sel...

A customer asked for help on their opportunity search. The search should not contain duplicate rows of opportunities. But the search should also contain joint activity fields. The challenge comes with the fact that opportunities can have multiple activities attached to them. Therefore in this one-to-many relationship, there would naturally be duplicate rows of opportunities. That is unless of course we know how to use ns_concat . The below formula is an example of how to achieve the customer's goal: case when ns_concat({activity.title}) is not null then '<table border =1>'||REPLACE(ns_concat(distinct '<tr><td>'||{activity.title}||'</td><td>' ||{activity.date}||'</td></tr>'),',','')||'</table>' end To briefly explain, ns_concat converts an array into a string. So essentially, whatever is inside the ns_concat tags will be looped, until each of the elements in the array have been ex...

The first time I've used ChatGPT was around the last week of March. I asked it to make a hangman game that I could embed into PowerPoint. As I expected, it created a VBA code for me, and I knew that it was a template. Now that I got that done, I went ahead and created the objects on a PowerPoint presentation. I entered the code provided to me, and tweaked it a bit. I added variables, and after about an hour, I had a working hangman game, which I could modify. I imagine it would have taken me possibly six times as long to finish the game without ChatGPT's code. Overall, it was an enjoyable experience. The code was easy to read, and easy to modify. While you do need to know some basic coding knowledge to complete the code it provides, I can see how valuable it can be if you want to learn how to code. I tried having ChatGPT create SuiteScript code, which looked fairly decent. Getting a reservation to SuiteScript training is pretty difficult, even if you are a NetSuite employee. I...

The behavior could be a result of a number of things. Below are the general steps to resolve the concern. Navigate to Lists > Accounting > Items Filter : Type : Select Discount Sales Discount : Click Edit Include Children : Enter Checkmark Click Accounting Account : Note the Value (Example: 4004 Income : Sales Discounts) Click Preferences Available to Adv. Partners : Enter Checkmark Click Save Note : Do this for other Discount Items you would use in your promotions. Navigate to Lists > Accounting > Accounts Account : Click Edit Note : Edit the account that was noted earlier (Example 4004 Income : Sales Discounts). Subsidiaries : Include Children : Enter Checkmark Click Save

This is a search that I've spent some time creating. Heads up, this is going to be a complicated search. The purpose of this search is to be able to track how many business hours have passed since a case has been created. Business hours are Mondays at 4AM through Fridays at 5PM. floor(((abs(round(to_char(trunc({createddate},'DD'), 'J') - to_char(trunc({today},'DD'), 'J') - (trunc({createddate},'d')-trunc({today},'d'))/7*2 - (case when to_char({today}, 'DY') = 'SUN' then 1 else 0 end) - (case when to_char({createddate}, 'DY') = 'SAT' then 1 else 0 end), 2))-1)*(17-4) + (decode(sign(trunc({createddate},'DD')+17/24 - {createddate}), -1 , 0, trunc({createddate},'DD')+17/24 - {createddate}-(case when trunc({createddate},'DD')+4/24 - {createddate} > 0 then trunc({createddate},'DD')+4/24 - {createddate} else 0 end)))*24 + (case when {today} > trunc({today},'DD')+17...

If you want to see transaction life cycles using a saved search (meaning a search that would display opportunities, along with their related estimates, sales orders, and invoices), you could try doing the following. Navigate to Lists > Search > Saved Searches > New Click Transaction Search Title : Enter a title Click Criteria Filter : Select Main Line Select Yes Click Set Select Type Select any of Multiple Select Field : Select Opportunity Click Set Click Results Field : Select Document Number Summary Type: Select Group Select Formula (Text) Summary Type : Select Maximum Formula : Enter  case when {applyingtransaction.type} = 'Estimate' then {applyingtransaction} end Select Formula (Text) Summary Type : Select Maximum Formula : Enter  case when {applyingtransaction.type} = 'Sales Order' then {applyingtransaction} end Select Formula (Text) Summary Type : Select Maximum Formula : Enter  case when {applyingtransaction.type} = 'Invoice' then {applyingtr...

Question: When clicking on a record from saved search results, I'm directed to the drill down. Can I set up the saved search so I am taken to the record directly? Short Answer: Yes, that behavior is possible through the use of formulas. The reason that's happening is because your search likely uses Summary Types on the Results tab. As standard behavior goes, clicking on a record from the summary will take you to the details of the results. If you want a field to take you directly to the record when clicked, you can try the following: Edit the Saved Search Click Results Field : Select Formula (Text) Formula : Enter '<a href= URL '||{internalid}||'">'||{ field_id }||'</a>' Note : Replace URL with the URL of the record, excluding the internal ID. Replace field_id with the actual field ID. Summary Type : Select Group Click Save & Run Example: In a transaction search, Document Number and Name are grouped (Summary Type = Group). The formul...

When creating Email Templates, Freemarker allows us to use if statements. For instance, you want to create a template that will show the phrase "You may contact us at abc@xyz.com" to the customer, only if the customer's sales rep has an email address, where abc@xyz is the sales rep's email. If the sales rep doesn't have an email address, or if there is no sales rep, then we just show abc@xyz.com. The if statement would look like this: You may contact as via <#if salesrep.email != "">${salesrep.email}<#else>abc@xyz.com</#if>. Moreover, NetSuite does not evaluate what's inside the if tag if the condition has already failed. This is useful information when we encounter compilation errors when attempting to save email templates. If we have confirmed that there is indeed nothing wrong with a template's HTML, the issue must be on the freemarker tags. The error would be a standard one, showing which lines we must look at in source mode...