A Linchpin's Journey

This is a concept I fabricated. Sometimes users would ask me to create a saved search and have a filter for a checkbox. That seems simple enough. NetSuite does allow users to filter based on the checkbox status - either unticked or ticked. But users would sometimes request to be able to filter ticked, unticked, or both in the results. As a workaround, I recommended to use a formula (text) as an available filter, where users can type t , f , or all . The criteria filter would be a formula (text), returning tall or fall (true or false respectively). It worked I guess. When doing this however, I recommend not adding other formula (text) filters in the criteria. Doing so will apply AND logic, and can mess up search results.

I am deeply saddened by the news about the recent layoffs happening in Oracle worldwide. I know what it feels like to unexpectedly lose a job. I even know the heartbreak of giving your all just to get fired, and even unpaid, after all your effort. I know the shame and disrespect of an angry boss (after my Oracle experience) that extorts you for their self-interest. In fact, I myself was fired from my previous employment. I held back from sharing this because I feared sharing this wouldn't inspire people to become linchpins if they'd get fired anyway. But I have come to realize why this story may be worth sharing. I left Oracle early this year as I would be taking up a NetSuite Admin role for a NetSuite partner. The company would be setting up operations in the Philippines, and the owner and CEO of the partner company saw my application. He proceeded to interview me and said he was very impressed with my profile. He told me that it was not easy to impress him. I thought to mys...

If you've read my previous posts, you may be aware that I've been complaining about the low salary I've been getting in Oracle. It's just really enough for our family. But it definitely is not going to be enough in case of emergencies or sudden expenses, like a car battery replacement, a rise in amortization costs, or  inflation. As much as I have loved my experience in Oracle, the recognitions, achievements, and of course, the people, I have other goals in life. There's more to it than just work. Financial goals, in particular, are what I needed to work on. And ultimately, the decision to leave Oracle seemed to be for the best. I've recently finished reading The Dip , by Seth Godin. I knew it would be a source of comfort as I enter this liminal space of unemployment. The book reaffirmed that my decision to leave was the right one, and that I had been in a cul-de-sac in Oracle all along. Oracle was truly a happy place. There was a lot of space to grow, but only ...

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

"Coming up" could mean a foreshadowing of events, such as telling you right now that I have been really busy these past few months. "Coming up" could also literally mean an escalation of a phase in life, to which I'm also foreshadowing. So I haven't posted in a while. I've been busy. November through March had been really tough months for our team, especially since there were a lot of code changes that would impact specifically the product area I handled. There were also slow days, where I got to take more time to myself, not really trying to be a linchpin. I didn't help out others as much during those times, as it was not my responsibility, after all. Though I could have, and that would have made a difference. Sometimes I guess you just need some time to breathe. I guess these were also the thoughts of the technical lead, who resigned and left the company on January 1. This was troublesome for the team, as now several people would have to chip in ...

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

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