A Linchpin's Journey

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...

It's actually 4:14AM on a Monday, and I've been doing a lot of thinking about work. So I've spoken with a couple of senior managers and it seems like they're willing to help me get into SuiteCloud. I'm looking forward to it. The SuiteAnalytics SME team met, after I provided my inputs, and people have been more active in the channel now. I'm excited about the Knowledge Management task force I'm setting up. I just need to train them on December 1, and do one-on-one discussions with each to ensure everything is in place. Progress! While a few things are moving along nicely, there are a few things that I am quite worried about. Becoming a linchpin actually opens your eyes to the reality that there are really those who choose to be cogs, and don't want anything more. You get to identify who those people are, and become more concerned about the other potential linchpins out there, who are getting affected by the lousy work the cogs bring to the table. Being a ...

 So just an update of what I've been doing daily at work. A lot of things really. Apart from my regular tasks on taking cases, calling customers, and filing defects, I've been doing extra for no recompense. A 'gift' if you may. I've been trying to get the aging cases of my colleagues in the CRM product area down. There are a lot of cases where reps may have felt stuck, or untrained to complete the task. So I've been teaching them how to work on such cases. There are indeed some cases on niche features, like the Dunning Letters bundle, that come once in a blue moon, so it's not very often reps get familiar with how to help customers with concerns on these features.  Consultations have not been limited to CRM. People from SuiteBuilder and SuiteCloud have also approached me for consultation. A lot of people have been consulting lately about saved searches, especially in the SuiteAnalytics Subject-Matter Expert (SME) Slack channel. I'm unfortunately the only...

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') ...

So I've been really busy. Especially yesterday. I've been getting a lot of customers raising business-critical concerns. While the tasks expected of me had some substantial volume, I was at least able to squeeze in some work that's not required, which was becoming a webinar panelist. Now with productivity in mind, I would like to share a graph I was reminded of, which is also found in  Linchpin :  I'll probably always remember this graph for the rest of my life. It has become my goal to reach way beyond my peers in at least one statistic. And I have been consistent quarter by quarter. I have been getting nearly half the team's productivity, in terms of number of cases resolved/worked on. It continues to be my goal today. I digress. Going back, this graph reflects cricket player batting averages. Donald Bradman was so good; no doubt about that. He practically had a league of his own. Godin then shares: If all you can do is the task and you're not in a league of y...

I've recently read the part of Linchpin that talks about Discernment vs. Attachment. The book reminded me of how this entire journey began -- an angry response to opposition. The anger stuck for a while, but then dissipated as the plan eventually pushed through. While I was able to get things done, I guess at that time I wasn't really classified as a linchpin. Godin shows the following diagram: I was a fundamentalist zealot. I was attached to negative emotions toward a manager that did not agree with my plans. The section of the book explains how attachments can destroy relationships, resulting to make the world poorer and more isolated. I guess it's really just natural that people look for someone to blame. Like why is the cost of living so high, or why is the government so corrupt. To give some inspiration to avoid this attitude, Godin proposes that we treat situations like they are fire. We don't get mad at fire when we get burnt by it. Or we can treat outcomes that...

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...

  Boomerang Employee Boomerang employees are those who leave an employer only to come back later. Workers may return to their old workplaces for a number of reasons. They might leave their jobs, feel shift shock in their new roles elsewhere, and decide to come back, or they may boomerang to get a raise. I don't think there's anything wrong with that, nor do I think that practice is frowned upon. Whether people are after a good environment or a raise, people are just trying to get what they deserve. Not much controversy here, so this is something that doesn't need avoiding/overcoming. Career Cushioning Career cushioning is a bit like an employment insurance policy, particularly in the face of a possible recession or layoffs. The idea is to have opportunities on the back burner if you should lose your current job, whether it's by keeping your LinkedIn updated, networking, or actively applying for new jobs. Again, nothing wrong with this practice in my opinion. This looks ...

The next few posts is going to be a 5-part series where I talk about work trends. These will be reactions to the Business Insider article,  Top 10 workplace trends on TikTok this year: quiet quitting, bare minimum Mondays, and more . It's very interesting that terms were coined for what people feel about the workplace. I actually believe these are all valid concerns. I'll try to list ways on how I would avoid each. Because situations and circumstances can greatly affect the outcome of one's decisions, it's always best to think logically and weigh the pros and cons before coming up with a decision regarding these feelings. Shift shock "Also called new hire's remorse, the term refers to the feeling of regret or unhappiness new employees might feel when a job is different than they were led to believe in the hiring process. It can often lead workers to job hop after a short amount of time or boomerang back to their old employers." I guess I experienced ...

Below are my additional thoughts regarding  Seth Godin's recent post . Image from  https://wowpedia.fandom.com/ If you loved video games and were born in the 90s, you likely have come across the grunt from  Warcraft . Unless you're an orc or a warrior, you may not find this character relatable. But the term  grunt  is actually what a lot of people in the workplace are. Just look at the last definition below: Yup, low-ranking. This could define what some people can only do, or how far they could go. So some people go quiet quitting, or doing only what their paid to do -- the bare minimum. But believing that spells out their own doom. I am actually low-ranking too. I'm just a support rep, hence, I am expected to do  grunt work , or menial tasks -- calling customers, investigating cases or tickets, and so on. Neither have I ever been promoted in my entire career as well. But my low-rank doesn't prevent me from doing amazing things. In fact, if you're a gr...

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...

This post is not going to be a wisdom post or a NetSuite post. Nor is it a post about data tables or spreadsheets. I don't usually publish posts like this, but this week I just wanted to share one of my interests. I've been into smart furniture for a while now: from smart mirrors to smart coffee tables (tables where you can play games with friends who come over). I've also been interested in how people DIY their own smart tables. If a simple and clean setup is all you need, check out this video from Linus Tech Tips. This is probably the most practical of the three. A very minimalist and futuristic design comes from Basically Homeless . It might not be very ergonomic, or practical (moving the mouse seems to be a struggle), but the effort to have the cleanest setup is laudable. Hacksmith Industries has a smart desk that's very gimmicky. It has a sensor and a unity program that makes use of leap motion (hand gesture technology). I first learned about leap motion back in c...

Before reading on, please watch this video: Here are my key takeaways: The most popular path is not always the best solution; better solutions can come from breaking the norm and creating/inventing your own path. Attacking a problem from multiple angles can provide valuable insight that can speed up processes. Angles can mean direction, or discipline. Taking diagonals is quite literally a different angle. Using vacuums is more of a physics problem than a logical one. So combine multiple disciplines when solving problems. Now how can you take these takeaways into the work? How can you make your very own Fosbury Flop in your particular field?

I never really got into telling the story of what happened after the rejection from that meeting that changed my life . I experienced not just rejection, but also mockery by a manager, after putting so much effort into a plan that makes sense. I reacted by reading Linchpin , and decided to be one. I devised another plan called Operation Hero. This would differ from the original plan, Operation Siphon (where other teams would gradually take some of our tasks weekly). Here's a brief summary of that plan: One team member would be dedicated in doing 50 solutions per week (or 10 a day), while the rest did 10 per week (or 2 per day). The number was calculated to finish everything by the end of the quarter. The assigned 'hero' is given some daily offline time, so they don't get distracted with cases and calls. The assigned 'hero' can change per week as an option. The plan was inspired from what another team did, as reported by the Knowledge Management (KM) team, and I ...

Management has recently announced that employees have to return to the office. For our organization, we will be expected to come in weekly. Naturally, there were complaints. I've been listening to some feedback and there are really a few valid reasons why some employees can't return to the office. For instance, a person pre-pandemic used to rent a place to make it easier to get to the office. During the pandemic, they decided to stop renting and work remotely back in his province. Going to the office would now take him 2 more hours, and would cost twice as much as before. It's good management considered such scenarios and allowed for some people to come to the office monthly instead, or even work remotely permanently. But for those that can come once a week, it may feel like a hassle. I realized that if I was able to go to the office before, it shouldn't be a problem to go back, given I have no valid reasons that prevent me from going in the first place. If transportati...

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...

If there is no sale, look for the fear. If a marketing meeting ends in a stalemate, look for the fear. I'd like to add, "If there is no progress in the job hunt, look for the fear." It's been about 6 months and several companies, and still no luck on finding a company that's willing to take a chance on me. I've done a lot. I've posted a comment on Marty Zigman's blog . I'm getting noticed by presidents of companies that are NetSuite partners, yet why don't I still have a better offer? I have my recognitions and customer feedback out in the open. But perhaps I haven't done enough. Sure I'm lined up for doing more. I'm planning to give a saved search training no one has ever given before (distinct of the SuiteAnalytics training). I'm planning to start a book club with managers and other leaders (though I'm not in a leadership role myself). I will be taking my NetSuite Administrator Certification exam soon. So what is my fear? ...

A question I've often come across during interviews was "Do you have any implementation experience?" It was only recently that I have actually gained some. NetSuite has social impact efforts, and this year, the social impact team has decided to host a hackathon4good . And it was quite an interesting experience. The non-profit organization partnering for the event is a company that delivers blankets to children in hospitals. I couldn't use their company's logo, so I just used the closest picture I could find that's somewhat related to their cause. Hundreds of attendees were grouped into teams. I was teamed up with six others with expertise from various NetSuite modules. It was a good mix -- an opportunity to bring different perspectives to tackle the three main problems of the organization we were to help. The main goals were (1) to establish a more efficient process to cut down Invoicing and Shipping lead times, and at the same time accurately forecast expecte...

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...