I wish I’d known this much sooner. NetSuite’s email capture script is a great way to incorporate incoming email directly into NetSuite.
First, my company uses Outlook 365 in the cloud. We set a rule to forward incoming email to a particular address on to NetSuite. The mail remains in the original mailbox in Outlook 365, but also arrives as though sent by the original sender in NetSuite. When it arrives in NetSuite, a custom Email Capture script reads the message, records all the detail such as FROM, TO, SUBJECT, BODY and all ATTACHMENTS into a custom record type. That record type is included in a saved search, which is then displayed as a shared dashlet, accessed by an entire department.
Individuals in the department assign themselves to the incoming emails and set a status letting their colleagues know they have it. That begins a workflow in NetSuite. It’s really slick, and really easy to implement.
First, here is a sample script. It’s written in SuiteScript 1.0. I was unable to find documentation on writing this in any other version of SuiteScript. Feel free to add comments to this article if I’ve missed something.
function process(email) {
const IS_PRODUCTION = true;
const valid_types = [
'APPCACHE',
'AUTOCAD',
'BMPIMAGE',
'CERTIFICATE',
'CONFIG',
'CSV',
'EXCEL',
'FLASH',
'FREEMARKER',
'GIFIMAGE',
'GZIP',
'HTMLDOC',
'ICON',
'JAVASCRIPT',
'JPGIMAGE',
'JSON',
'MESSAGERFC',
'MP3',
'MPEGMOVIE',
'MSPROJECT',
'PDF',
'PJPGIMAGE',
'PLAINTEXT',
'PNGIMAGE',
'POSTSCRIPT',
'POWERPOINT',
'QUICKTIME',
'RTF',
'SCSS',
'SMS',
'STYLESHEET',
'SVG',
'TAR',
'TIFFIMAGE',
'VISIO',
'WEBAPPPAGE',
'WEBAPPSCRIPT',
'WORD',
'XMLDOC',
'XSD',
'ZIP',
]
var fromAddress = email.getFrom();
nlapiLogExecution('DEBUG', 'Email - from: ' + fromAddress.getName() + ', ' + fromAddress.getEmail());
nlapiLogExecution('DEBUG', 'subject - ' + email.getSubject());
var newRec = nlapiCreateRecord('customrecord_xxxxxxxxxxxxxxxxxxxxxxxxxxxx');
newRec.setFieldValue('custrecord_xxxxxxxxxx', fromAddress.getName());
newRec.setFieldValue('custrecord_xxxxxxxxxx', fromAddress.getEmail());
newRec.setFieldValue('custrecord_xxxxxxxxxx', email.getSubject());
newRec.setFieldValue('custrecord_xxxxxxxxxx', email.getTextBody());
var newRec_id = nlapiSubmitRecord(newRec, true);
var attachments = email.getAttachments();
// This variable is here in case you wanted to add notes about attachments.
// it is currently unused.
var processing_notes = '';
for (var indexAtt in attachments) {
var attachment = attachments[indexAtt];
nlapiLogExecution('DEBUG', 'Attachment: ' + attachment.getName() + ', ' + attachment.getType());
// If the file name does not have an extension, skip it
var fileName = attachment.getName();
if (fileName.indexOf('.') <= 0) continue;
// add a unique suffix to the file name, but leave the extension as-is
var fileArray = fileName.split('.');
var newName = '';
for (var i = 0; i < fileArray.length; i++) {
if (i == fileArray.length - 1) {
newName += ('_' + (new Date().valueOf()).toString());
}
newName += ('.' + fileArray[i]);
}
// Lookup the file type to see if it is supported, else save as PLAINTEXT
// This really won't affect being able to open and download the file.
// It only affects filtering files by type.
var file_type = attachment.getType().toUpperCase();
if (valid_types.filter(function (p) { return p == file_type }).length == 0) {
file_type = 'PLAINTEXT'; // Import nonrecognized file types as Other Binary File
}
var file = nlapiCreateFile(newName, file_type, attachment.getValue());
// Save the file under a selected folder in the file cabinet
file.setFolder(1111111111); //Internal ID of folder to hold imported attachments
var file_id = nlapiSubmitFile(file);
// Attach the file to a custom record type
nlapiAttachRecord('file', file_id, 'customrecord_xxxxxxxxxxxxxxxxxxxxxxxxxxxx', newRec_id);
}
// here is where you'd include notes about attachments, perhaps those that were erroniously
// saved under the PLAINTEXT type.
nlapiSubmitField('customrecord_xxxxxxxxxxxxxxxxxxxxxxxxxxxx', newRec_id, 'custrecord_xxxxxxxxxx', processing_notes);
}
Once you upload your script to the file cabinet, go to Customizations >> Plug-Ins >> Plug-In Implementations >> New
Select your script. Next, look for it under Manage Plug-Ins.
Your new Email Capture Script shows up here along with the associated NetSuite email address. Be sure to activate your script by checking the box next to your Plug-In.
To test, click the link. It should open your default email client (in my case it is Outlook 365) and address the mail to this address. Send your test message and in a couple of seconds, it will be picked up and processed by the new script. It’s fast!
Once you are happy with your script, set Outlook 365 (the server-side in the cloud) to forward to this address.
It is important to understand that NetSuite will only allow your script to upload known file types. That’s why I’ve supplied an array of file types in the code. However, NetSuite will accept any file of any type as long as you fib and say it’s a known file type. So, my code tries to match the type with a known NetSuite file type. If there is no match, I tell NetSuite the file is type PLAINTEXT. It allows the file to be uploaded and saved. It does not affect downloading the file. It still comes down and is opened based on the file’s extension. The only thing it affects is your ability to filter files. In my case, I’ll have a lot of files of type PLAINTEXT that are actually some other format. And… I can live with that.
The other gotcha is NetSuite thinks files of same name are the same file. So my code adds a suffix based on an integer time stamp. So two files of the same name Kevin.text and Kevin.text will be saved as Kevin_[big unique integer number].text. When these files get attached to two separate custom records, the files are not be overwritten.
And that should be all you need to know about that!
As with most things, this turned out to be a hard problem with a simple solution. I was using jsTree to display a product catalog. Originally, I did not implement multiselect. So when a node in the tree was double clicked, it expanded the node. However, when I went to multiselect, double clicking a node expanded it, but also selected it and every child node under it. I needed to decouple those two behaviors.
Double click to expand. Single click on the checkbox to select.
$(document).ready(
function() {
$('#jstree_div').jstree({
plugins: ['checkbox', 'changed'],
}).on('changed.jstree', function(e, data) {
var t1 = setTimeout(
function() {
var selected_nodes = $('#jstree_div').jstree('get_selected');
if (Array.isArray(selected_nodes) && selected_nodes.length > 0) {
// Fire whatever asynchronous process you choose here.
// Like a call to the server to fetch data.
$('#selection').text(selected_nodes.join(', '));
} else {
$('#selection').text('');
}
}, 500
)
})
})
As crazy as this sounds, NetSuite’s richtext control allows (and possibly creates) markup that won’t print. We decided to replace their control with tinyMCE.
Originally, I did this with client-side scripting. It was simple, because a client-side script would wait until the page loaded, look for all richtext controls, remove the DOM elements, replace them with a TEXTAREA and initialize tinyMCE. Doing it client-side let me continue to manage my page and scripts exactly the same as if I’d left the original (NetSuite’s) controls in place. No swapping, no renaming. Simple!
This worked great until we got a Chrome update this past week, then stuff completely unrelated to the rich textboxes stopped working. It was incredibly hard to diagnose, as the problems were intermittent. It turned out it had to do with delivering the first richtext control, and the replacing it while it was in the process of initializing. Somehow that messed up other NetSuite client-side JavaScript.
To fix this, I had to rethink how I was replacing the richtext control with tinyMCE. I had to do it server-side, to keep the original control from ever starting to render. It is impossible to remove a control server-side, but you can hide it. It is also impossible to have 2 fields with the same ID. That means you’ll need to do some renaming. Argh!
/**
* Add a textarea to the form with the name custpage_ in place of custbody_, then hide the original.
* @param {*} context
* @param {*} original_field_id
*/
function swapRichTextControls(context, original_field_id) {
var new_field_id = original_field_id.replace('custbody_', 'custpage_');
var value = context.newRecord.getValue(original_field_id);
var originalField = context.form.getField(original_field_id);
var newField = context.form.addField({
id: new_field_id,
label: originalField.label,
type: serverWidget.FieldType.TEXTAREA
});
newField.defaultValue = value
context.form.insertField({
field: newField,
nextfield: original_field_id
});
originalField.updateDisplayType({ displayType: serverWidget.FieldDisplayType.HIDDEN });
}
I call swapRichTextControls from an event script. In my case all my richtext fields are custom fields, which start with “custbody_”. Since I can’t have 2 fields with the same name, and the convention is to name all custom fields with “custpage_”, I do that in a beforeLoad() function.
In the beforeSave() function, I take value from the newly created control and save it back in field that was previously associated with the original richtext control, the one I hid in the beforeLoad() function.
/**
* On save, you need to move the value from the fake TEXTAREA back to the original field.
* @param {*} context
* @param {*} original_field_id
*/
function swapBackRichTextControls(context, original_field_id) {
var new_field_id = original_field_id.replace('custbody_', 'custpage_');
try {
context.newRecord.setValue(
original_field_id,
context.newRecord.getValue(new_field_id)
)
}
catch (err) {
}
}
This takes care of delivering a standard TEXTAREA control in place of the richtext control that we wanted to get rid of. To turn it into a tinyMCE control, we need to include the tinyMCE library and initialize the TEXTAREA.
/**
* Initialize tinyMCE on a TEXTAREA control.
* @param {*} control_id Internal ID of the control. example: custpage_note_internal.
*/
initControl: function (control_id) {
//console.log('initControl: ' + control_id);
//if (!tinymce) console.log('tinymce cdn libraries are not included');
tinymce.init({
selector: '#' + control_id,
plugins: 'link paste code media table lists hr help',
default_link_target: '_blank',
link_assume_external_targets: 'https',
paste_data_images: true,
menubar: 'edit view insert format table tools help',
toolbar: 'numlist bullist link hr',
browser_spellcheck: true,
contextmenu: false,
forced_root_block: false,
init_instance_callback: function (editor) {
editor.on('Change', function (e) {
// This is where the contents of the TinyMCE control
// are put back into the textarea where the server picks
// them up as though they were in the original <input type="hidden">
var realFieldId = e.target.id;
$('#' + realFieldId).val(e.target.getContent());
})
}
});
var control_width = jQuery(window).width() * 0.30;
jQuery('#'+control_id).css('width', control_width, 'padding-right', 20, 'padding-bottom', 20);
}
Here is the basics of how I include the tinyMCE CDN.
const tinymce_js = 'https://cdn.tiny.cloud/1/.../tinymce/5/tinymce.min.js';
function tinymce_client_includes() {
client_includes_count++;
return [
... Other library references ...
tinymce_js,
].join("\n");
}
I was working on capturing transaction summary data for use later in trending reports. A colleague of mine mentioned incorporating a date dimension table to save having to do all the complicated date calculations on the fly. This was a great idea, but not so simple to implement in NetSuite.
In short, a date dimension table is a series of rows, each representing a day. Corresponding columns represent date associations, like start of week, day in week, start of month, day in month, start and end of quarter, start and end of fiscal year, day suffix, etc. It’s extremely handy when looking back on the first of the month to say, what is the start and end dates of this week, month, year, fiscal year, etc. My end result was a series of Map/Reduce Suitescripts that wake up on start of month, quarter or year and capture summary data for the period that just ended. Once I had the dimDate table, the rest was easy.
To build my dimDate table, I started with the article my colleague sent me which builds one from a script in Microsoft SQL Server. Rather than translate this to JavaScript and run it in SuiteScript, I opted to run it and export the result. Then I created a custom record type (table) in NetSuite called dimDate with matching fields. I’ll include the definition of that table as saved via SuiteCloud Developer Framework (SDF). I’ll also include the script I used to create my dimDate data (in MS SQL Server).
This is the definition you’ll be importing. It’s a custom record type called dimDate.
Here is the Transact SQL code used to create my dimDate table. It uses October 1st as the start of the fiscal year. It starts on January 1st, 1999 and builds rows that represent all days for 40 years.
-- Run this in a sql server 2012 or higher
-- Export to CSV using the SQL Mgmt Studio Export feature (Right click on DB and Export)
-- Don't open this with Excel, or the date formats in the first 4 fields will be screwed up
DROP table DimDate;
DECLARE @StartDate date = '19990101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 40, @StartDate));
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
SELECT
Date = CONVERT(date, d),
Day = DATEPART(DAY, d),
DayName = DATENAME(WEEKDAY, d),
Week = DATEPART(WEEK, d),
FiscalWeek = DATEPART(WEEK, DATEADD(MONTH, -9, d)),
DayOfWeek = DATEPART(WEEKDAY, d),
Month = DATEPART(MONTH, d),
FiscalMonth = DATEPART(MONTH, DATEADD(MONTH, -9, d)),
MonthName = DATENAME(MONTH, d),
Quarter = DATEPART(Quarter, d),
FiscalQuarter = DATEPART(Quarter, DATEADD(MONTH, -9, d)),
Year = DATEPART(YEAR, d),
FiscalYear = DATEPART(YEAR, DATEADD(MONTH, -9, d)),
FirstOfMonth = CONVERT(varchar, DATEFROMPARTS(YEAR(d), MONTH(d), 1), 101),
LastOfYear = CONVERT(varchar, DATEFROMPARTS(YEAR(d), 12, 31), 101),
FiscalLastOfYear = CONVERT(varchar, DATEFROMPARTS(DATEPART(YEAR, DATEADD(MONTH, -9, d)), 9, 30), 101),
DayOfYear = DATEPART(DAYOFYEAR, d),
FiscalDayOfYear = DATEPART(DAYOFYEAR, DATEADD(MONTH, -9, d))
FROM d
),
dim AS
(
SELECT
DateKey = CAST(CONVERT(char(8), Date, 112) as varchar),
ExternalID = CAST(CONVERT(char(8), Date, 112) as varchar),
Date = CAST(CONVERT(varchar,Date, 101) as varchar),
LongDate = CAST(CONVERT(varchar(20), Date, 101) as varchar),
ShortDate = CAST(DATEPART(MONTH, Date) as varchar) + '/' + cast(DATEPART(DAY, Date) as varchar) + '/' + CAST(YEAR(Date) as varchar),
SortDate = CAST(CONVERT(char(10), Date, 120) as varchar),
Day,
DaySuffix = cast(Day as varchar(10)) + CONVERT(char(2), CASE WHEN Day / 10 = 1 THEN 'th' ELSE
CASE RIGHT(Day, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd' ELSE 'th' END END),
DayName,
DayOfWeek,
DayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER
(PARTITION BY FirstOfMonth, DayOfWeek ORDER BY Date)),
DayOfYear,
FiscalDayOfYear,
IsWeekend = CASE WHEN DayOfWeek IN (CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7)
THEN 1 ELSE 0 END,
Week,
FiscalWeek,
FirstOfWeek = CONVERT(varchar, DATEADD(DAY, 1 - DayOfWeek, Date), 101),
LastOfWeek = CONVERT(varchar, DATEADD(DAY, 6, DATEADD(DAY, 1 - DayOfWeek, Date)), 101),
WeekOfMonth = CONVERT(tinyint, DENSE_RANK() OVER
(PARTITION BY Year, Month ORDER BY Week)),
Month,
MonthName,
FirstOfMonth,
LastOfMonth = CONVERT(varchar, MAX(Date) OVER (PARTITION BY Year, Month), 101),
FirstOfNextMonth = CONVERT(varchar, DATEADD(MONTH, 1, FirstOfMonth), 101),
LastOfNextMonth = CONVERT(varchar, DATEADD(DAY, -1, DATEADD(MONTH, 2, FirstOfMonth)), 101),
Quarter,
FiscalQuarter,
FirstOfQuarter = CONVERT(varchar, MIN(Date) OVER (PARTITION BY Year, Quarter), 101),
LastOfQuarter = CONVERT(varchar, MAX(Date) OVER (PARTITION BY Year, Quarter), 101),
FiscalFirstOfQuarter = CONVERT(varchar, MIN(Date) OVER (PARTITION BY Year, FiscalQuarter), 101),
FiscalLastOfQuarter = CONVERT(varchar, MAX(Date) OVER (PARTITION BY Year, FiscalQuarter), 101),
Year,
FiscalYear,
FirstOfYear = CONVERT(varchar, DATEFROMPARTS([Year], 1, 1), 101),
LastOfYear,
FiscalFirstOfYear = CONVERT(varchar, DATEFROMPARTS(FiscalYear, 10, 1), 101),
FiscalLastOfYear,
IsLeapYear = CASE when CONVERT(bit, CASE WHEN (Year % 400 = 0)
OR (Year % 4 = 0 AND Year % 100 <> 0)
THEN 1 ELSE 0 END) = 1 then 'T' else 'F' end,
Has53Weeks = CASE WHEN DATEPART(WEEK, LastOfYear) = 53 THEN 'T' ELSE 'F' END,
MMYYYY = CONVERT(char(2), CONVERT(char(8), Date, 101))
+ CONVERT(char(4), Year)
FROM src
)
SELECT *
INTO dimDate
FROM dim
ORDER BY Date
OPTION (MAXRECURSION 0)
Here is the dimDate table definition saved via SuiteCloud Developer Framework. It’s huge. Sorry!
I spent 14 years running a small software company in Kansas City. During my time writing software for numerous customer, I had issues that produced outages. What I learned was it was always best to fess up and tell the truth. If you are truthful in the small things, you’re probably truthful in the big things. Customers recognize that.
This morning my phone rang at 9:20 AM Central US time. NetSuite was down! Let me run back through what I experienced, just to document. I’ll tell you my version of the truth! Let me add, I’m a NetSuite fan! I recommend NetSuite as an excellent cloud based ERP provider and will continue to recommend them to other businesses. Do they always tell the truth? Hmmm… You can judge for yourself.
9:20 – NetSuite status page shows no problems. Last update was from 1/12/2022
My users were seeing this message
I called NetSuite support and got a message that all lines were busy
I started monitoring a forum for NetSuite outages. I posted this at 10:05 CST.
By 10:15, NetSuite updated their status page as follows.
At 10:25, I got through to NetSuite support. I posted this in the forum.
At 11:05 AM I posted this in the forum.
At 11:20 AM NetSuite was back online
During the outage, I noted NetSuite customers from all over North America, Mexico, UK, Canada, Singapore who could not login to production NetSuite. In the US, users from New York, Georgia, Texas, Ohio, Pennsylvania and of course Kansas City were also unable to login. The NetSuite status page has confirmed an outage in the US Seattle data center. That’s probably true, even though it defies common logic.
And one more note about being truthful. If NetSuite would debrief us, as customers with more detail, perhaps we’d be more inclined to believe that this outage was limited to just their west coast US data center customers.
As a footnote: 30 days per month. 24 hours per day. 99.98% available or 0.02% unavailable. 30 * 24 * (1 – 0.9998) = 8 minutes 38 seconds a month.
This error was so hard to find, I thought I’d document it. The error message read: “Invalid number of parameters. Expected: 0. Provided: 3“
I was coding a SuiteQL query in SuiteScript Version 2.0. Here’s an example.
If you happen to make the mistake I made and add an extra comma at the end of one of the column definitions, BOOM!
This is relatively easy to spot is a small query. However, in a much larger query with lots of columns, it’s easy to miss. I’ll attest to that. Here is the code in case you want to try this at home.
require(['N/query'],
function (query) {
log.debug('start');
const MY_SQL_CONSTANT =
"select " +
" BUILTIN.DF(tl.item) as item_name\n" +
" , tl.linesequencenumber as line\n" +
"from transactionline tl\n" +
"where tl.transaction = ? " +
" and tl.item is not null " +
"order by tl.linesequencenumber";
var results = query.runSuiteQL({
query: MY_SQL_CONSTANT,
params: [your transaction id here]
}).asMappedResults();
log.debug(results[0].item_name);
log.debug('finished');
}
)
Visual Studio Code natively supports editing multiple lines, adding a quote at the beginning of the line and a quote followed by a comma at the end of the line. Here’s how. This example is from vsCode running on Windows 10.
ctl+home – to move to top
shift+ctl+alt+[down arrow] – to select all lines that you wish to multi-line edit
single tick – adds opening tick to all selected lines
end – to jump to the end of all lines in this multi-line edit block
single tick and comma – close and end all lines
esc – to get out of multi-line edit mode
ctl+a – to select all lines
[ – to enclose the entire list as an array (this is JavaScript)
I recently ran into an issue where a new integration wasn’t doing what it was supposed to. During debugging I learned some tips and tricks that I don’t want to forget.
In my case, I was dealing with a Hubspot integration where I was synchronizing contacts from NetSuite to Hubspot. Initially, I installed a Hubspot bundle. Let’s start there.
Customization >> SuiteBundler >> Search and Install Bundles >> List
Open the Hubspot bundle. Review the Components tab.
You can see that this bundle includes a couple of JavaScript Restlets. Restlets are stateless APIs. It is important to note that Restlets are not scheduled, and are not triggered on events. They simply sit and wait for requests, in this case from Hubspot. So let’s start by looking at the integration.
Setup >> Integration >> Manage Integrations
I clicked on Hubspot Sync. Look at the “Execution Log” tab. There are 3 subtabs.
You may be asking yourself about the REJECTEDACCOUNTCONCURRENCY status. It’s not a problem, so let’s table that for now.
If you click on the “view” links under both request and response, you can see the SOAP request and response to Hubspots API calls. Remember, the Restlet is stateless and simply responds to requests from an outside entity (Hubspot).
If you click on the REST Web Services subtab, you see more of the same. However, there is more detail if you drill into the “view” links under both request and response columns.
Let’s look at a request.
And here is the response.
This is a great place to review communication to and from your integration. And now back to the REJECTEDACCOUNTCONCURRENCY status that we saw earlier.
In a standard NetSuite installation, you are allowed 5 concurrent transactions. It is my understanding this includes UI interactions. So if 2 people hit enter at the same time, that counts for 2. If you have multiple integrations, like Hubspot, each call to an API also counts as 1. So in our case, we have enough users and integrations that we hit our concurrency limit from time to time. This is normal!
If you want more info on all of your integrations, look here.
Setup >> Integration >> SOAP Web Services Usage Log
And the final thing I’ll point out is how you tell what your concurrency limit is. Look at any of your integrations. This is where it will show you your max concurrency limit.
Oh… One more thing… Here is a great place to see how often an API request is rejected.
I just got off the phone with Jason at NetSuite support. Thank you Jason! I’d called several months ago when initially setting up NetSuite Upload. In order to save a callback when reinstalling, I wrote a blog article with required steps. After refreshing out sandbox, I followed my steps, but without success. Unfortunately, I’d missed a step. I omitted permissions.
This call turned out to be a positive experience, because Jason taught me how to troubleshoot Token Based Authentication problems. So in this post, I’ll share the missing permission, and I’ll share how to troubleshoot any token based application like this one.
So how did we find the missing permissions? Step 1: Determine if we were logging in successfully or not. To do that we did the following:
Create a saved search on “Login Audit Trail” records
Add Detail, Token-based Access Token Name. Token-based Application Name in the results columns
Sort by Date descending
What we saw was my TBA user had successfully logged in. TBA was working! This prompted us to look further into the Restlet called by NetSuite Upload, vscodeExtensionRestlet.js. To do that, we opened its script deployment and reviewed the execution log sublist. That made it abundantly clear we were missing the “Allow JS/HTML Upload” permission on the user. That’s the user I’d assigned in step 2 of the previous post (where I created the Access Token).
So the tricks are… Write an ad hoc saved search on the login audit trail, and review the detail. If you’ve successfully logged in, then keep digging into the application, in this case it was a restlet, and see what’s breaking there.
I don’t know if this is an Oracle issue or something related to NetSuite. I come from a Microsoft SQL server world where queries like this work. Perhaps there’s a way to do this in PL/SQL, but I have not found it.
It is possible this query works outside of NetSuite, but it does not work in SuiteSQL. I’ve noticed other problems rolling up data in NetSuite, even in Saved Searches. Maybe that’s related.
I’ll add that I know from attending sessions at SuiteWorld that NetSuite is in the process of modifying their provisions such that each customer will have their own database. I’m unclear where they are in that process. But at the last SuiteWorld I attended, all queries were being modified to include an additional filter adding a customer’s account number. This was explained to me as all tables included data for all customer accounts in a common database. So NetSuiteCustomer#1 and NetSuiteCustomer#2 both submit a query to list their customers, “Select * from customers“. NetSuite will modify this to be “Select * from customers where NetSuiteCustomerID = [Your NetSuite Account Number]“. Perhaps this explains what you see below.
If you have a solution to this issue, feel free to send it to me. I’ll repost it and credit you. But wait! Before you rewrite my query, thinking you’ve found my flawed reasoning…
This doesn’t work either…
AND… Removing the NVL wrapper around my raw data column… Uh no! That doesn’t work either.
Best of luck to you if you decide to take on this problem. I ended up solving it by pulling the raw data (omitting the SUM() function altogether) and rolling it up in Excel.
Happy Programming!
September 2022: This comment is from Joseph Lee. He writes:
Hello Kevin,
You should be able to do something similar to get yours to sum properly. The error I am now experiencing is when I try to do a group by rollup my forecast column only sums the amounts from opportunities and now everything else. I suspect it has to do with my case statement and I’m forced to roll up in the subquery.
Here is Joseph’s code. Thank you Joseph!
WITH
SalesSummary AS
(
SELECT
Employee AS RepID,
Type,
SUM( ForeignTotal ) AS ForeignTotal,
SUM( projectedTotal*probability ) AS ProjectedTotal
FROM
Transaction
WHERE
( ( Type = 'SalesOrd' ) AND ( BUILTIN.CF( Status ) IN ('SalesOrd:B','SalesOrd:A') AND TO_CHAR(shipdate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(shipdate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'Opprtnty' ) AND ( BUILTIN.CF( Status ) IN ('Opprtnty:A','Opprtnty:C') AND TO_CHAR(expectedclosedate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(expectedclosedate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'CustInvc' ) AND ( BUILTIN.CF( Status ) IN ('CustInvc:A','CustInvc:B') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'CashSale' ) AND ( BUILTIN.CF( Status ) IN ('CashSale:C','CashSale:B') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'CustCred' ) AND ( BUILTIN.CF( Status ) IN ('CustCred:B','CustCred:A') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
GROUP BY
Employee,
Type
)
SELECT
a.RepName,
a.Opportunity_Total,
a.Sales_Pending_Total,
a.Invoiced,
a.Cash_Sale,
a.Credit_Memo,
case when (a.Sales_Pending_Total + a.Invoiced + a.Cash_Sale + a.Credit_Memo) < Opportunity_Total
then Opportunity_Total
else (a.Sales_Pending_Total + a.Invoiced + a.Cash_Sale + a.Credit_Memo)
end Forecast
from (
SELECT
RepID,
( Employee.FirstName || ', ' || Employee.LastName ) AS RepName,
SUM ( CASE WHEN Type = 'Opprtnty' THEN ProjectedTotal ELSE 0 END ) AS Opportunity_Total,
SUM ( CASE WHEN Type = 'SalesOrd' THEN ForeignTotal ELSE 0 END ) AS Sales_Pending_Total,
SUM ( CASE WHEN Type = 'CustInvc' THEN ForeignTotal ELSE 0 END ) AS Invoiced,
SUM ( CASE WHEN Type = 'CashSale' THEN ForeignTotal ELSE 0 END ) AS Cash_Sale,
SUM ( CASE WHEN Type = 'CustCred' THEN ForeignTotal ELSE 0 END ) AS Credit_Memo,
FROM
SalesSummary
INNER JOIN Employee ON
( Employee.ID = SalesSummary.RepID )
WHERE
RepID IN ('17','24','-5','26')
GROUP BY
RepID,
( Employee.FirstName || ', ' || Employee.LastName )
ORDER BY
RepName
) a
NetSuite Experiences 