Did you know you can export the NetSuite tax tales in magento format? NO? READ ON!
In order to accomplish this task, you need to create a server-side script in NetSuite and ‘deploy’ it to its own url. The server-side NetSuite script will have access to Netsuite’s back-end, thus enabling you to access the tax information.
But first, a note on server side Javascript: If you have always relied heavily on console.log() or (when feeling dramatic) alert(), you’ll have to re-adjust. Those arrows ain’t in your quiver when in server-side land. In this particular example , we are generating HTML, so testing and seeing errors isn’t hard. But if you are dealing with other suitescript scripts, ones that don’t generate page content, I recommend you keep an eye on nlapiLogExecution. It is extremely handy. You can also use the SuiteScript Debugger.
Netsuite Configuration
Before you start the code, which is the main part of this blog post you need to set up a new customized script and deploy it. I’m assuming that most people reading this know how to do that, but just in case you don’t, I’ll short-hand it:
Create a new server-side script:
Start by saving a .js file (call it “tax_export.js”) somewhere on your hard drive.
Log into NetSuite, then go to: customization->scripting->scripts. Click “New Script”, under “Select Type”, click ‘suitelet’.
Once there, name the script “tax_export”, click the dropdown arrow in the ‘script file’ input window and click “new” (this can also be accomplished by clicking the plus sign that appears when hovering over the “script file” input window). A pop up will appear. Click ‘Choose File” from the “Select File” input panel and navigate to the .js file you made earler (we called it “tax_export.js”).
Once done, click the “save” button on the pop up panel. The pop up should go away.
Back in the main window, type the word “suitelet” in the function input window. The value of this input window needs to match the function name on the .js you uploaded. Make sure that if you change the function name in tax_export.js (which we haven’t added yet), you also change the function name in this input text field.
Deploy the NetSuite script:
Just in case you’re somewhere else in NetSuite, follow this chain again: customization->scripting->scripts. You should see your script in the list on the page. If you don’t, expand the “filters” view by clicking the plus sign next to the word “FILTERS” and select “suitelet” from the “type” dropdown. It should be there now.
Click “view” next to your script (which, should be called “tax_export”), and then, when in the script window, click “Deploy Script”. Welcome to the “Script Deployment” page!
A few more settings to cover. Click “deployed” and “available without login”. You’re going to have to make a judgement call with some of the other options. Put your cursor over “Log Level” and check out the options to see which is right for you. Typicall “Error” is the right level but you may want to set “Debug” while developing to see your debug output. Do the same with “Execute As Role” (I used “Administrator”), and in the “Audience” window use your judgment. I clicked “Select All” next to “Roles”.
Then click “Save”. We are really close to actual coding, thanks for reading on!
Now, to find the location of the deployed script:
Once again, go: customization->scripting->scripts. Click “Deployments” next to the name of your script (“tax_export”), and then click “view” next to the deployment.
And there, under “External URL” is the product of all the work so far. That is going to be the address of your tax export. So copy/paste it into a new browser window, bookmark it, make love to it, slap a ring on it and tell it you’ll never leave it, because you have worked for it!
Coding time!
Well, almost. I just want to show you where you’re going to paste the script once done: Yet again: go: customization->scripting->scripts. Click ‘view’ by your script (“tax_export”), and then click “Edit” next to the Script File name (“tax_export.js”). This is where you’ll cut and paste your code when done.
Okay, ready for some coding??!! Excellent — open up tax_export.js on your computer. Add the following code:
function suitelet(request, response){
if (request.getMethod() == 'GET') {
//this is where we'll put our code
}
}
This is the NetSuite (suitelet) framework for the code we’re going to write. The parameters are pretty obvious: “request” is that method (get or post) of the data coming in the page, “response” is the output of the function to the page.
Now let’s get to the code. First a simplified version:
function suitelet(request, response) {
if (request.getMethod() == 'GET') {
var searchresults = nlapiSearchRecord('taxgroup', null, [], []);
if (searchresults != null && searchresults != '') {
response.write('
<table>
<tbody>');
response.write('
<tr>
<th>county</th>
<th>US</th>
<th>CA</th>
<th>zip code(s)</th>
<th>tax rates</th>
<td>default</td>
</tr>
');
//loop through search results
for (var key in searchresults) {
if (searchresults[key]) {
//pull the individual records from the search results:
var order = nlapiLoadRecord('taxgroup', searchresults[key].getId());
var zipString = order.getFieldValue(['zip']);
var rateString = order.getFieldValue(['rate']);
var countyString = order.getFieldValue(['county']);
if (countyString) countyString = countyString.replace(/\s/g, '_');
response.write('
<tr>
<td>US-CA-' + countyString + '</td>
<td>US</td>
<td>CA</td>
<td>' + newZipString + '</td>
<td> ' + rateString + '</td>
<td>default</td>
</tr>
');
}
}
response.write('</tbody>
</table>
');
} else {
response.write(' no search results exists.');
}
} else {
response.write('Error: This page can only be accessed via GET method.');
}
}
Our first new line of code invokes the ‘nlapiSearchRecord’ method of suitescript. This method, takes a record type as the first parameter. Tax tables are stored in an object of type ‘taxgroups’. At the end of this post, there are links to the suitescript reference docs for you to explore the language further, if you want. But for now, that method will return an object (which we’re cleverly calling “searchResults”) that contains the search results. In this case, a collection of all the tax group data.
We then iterate through the search results object, and pull out the desired data from each of the records. For each record, we verify its not null (perhaps redundant) and assign fields of interest to local variables. Our goal here is to replicate the format of the magento tax tables, so this should get us the data we need.
The remainder of code construct HTML for a table that will render tax data.
If you load the URL you copied earlier, you should now see some data. If the page fails to load, you may choose to break out of the loop after 50 records.
Magento Format
There’s more because we really haven’t accomplished our goal yet. The code above gives us the data that magento needs, but it isn’t in the magento format.
The magento format allows for grouping consecutive zip codes with the same tax rate into a range (e.g. 92121-92127 will represent all zip codes in that range), allowing you to store fewer unique entries for a given tax amount.
I am assuming you know JavaScript, so I won’t explain the code below in great detail.
One other thing about the code below:
There are A LOT of records, so I split them up into pages of 50 records each. Feel free to change the ‘increment’ variable in the code below in order to alter this.
function suitelet(request, response) {
if (request.getMethod() == 'GET') {
response.write('<style>table#headTable{width:600px;text-align:center; margin:10px auto;}a {text-decoration:none;}button {width:200px; margin:5px;height:50px;text-decoration:none;}table#data {border-collapse: collapse;width:1000px;margin:5px auto;} td, th {word-break: break-all;padding:5px;border:1px solid #000000;}}</style>');
var increment = 50;
//build the current url string
var currentURL = request.getURL() + '?';
var params = request.getAllParameters();
for (var key in params) {
if (key != 'startRecord') currentURL += key + '=' + params[key] + '&';
}
currentURL = currentURL.substring(0, currentURL.length - 1);
var startRecord = (request.getParameter('startRecord')) ? Number(request.getParameter('startRecord')) : 0;
var endNumber = Number(startRecord + increment);
var previousNumber = Number(startRecord - increment);
var endRecord = (request.getParameter('startRecord')) ? endNumber : increment;
var searchresults = nlapiSearchRecord('taxgroup', null, [], []);
endNumber = (endNumber > searchresults.length) ? searchresults.length : endNumber;
response.write('
<table id='headTable'>
<tbody>
<tr>
<th>showing records ' + startRecord + ' to ' + endNumber + ':</th>
</tr>
<tr>
<td>');
if (startRecord > 0) response.write('<a href='' + currentURL + '&startRecord=' + previousNumber + ''><button>previous page</button></a>');
if (endRecord < searchresults.length) response.write('<a href='' + currentURL + '&startRecord=' + endRecord + ''><button>next page</button></a>');
response.write('</td>
</tr>
</tbody>
</table>
');
response.write('
<table id='data' border='0'>
<tbody>
<tr>
<th>Code</th>
<th>Country</th>
<th>State</th>
<th>Zip/Post Code</th>
<th>Rate</th>
<th>Zip/Post is Range</th>
<th>Range From</th>
<th>Range To</th>
<th>default</th>
</tr>
');
if (searchresults != null && searchresults != '') {
//loop through search results
for (var key = startRecord; key <= endRecord; key++) {
if (searchresults[key]) {
//pull the individual records from the search results:
var order = nlapiLoadRecord('taxgroup', searchresults[key].getId());
var zipString = order.getFieldValue(['zip']);
var rateString = order.getFieldValue(['rate']);
var countyString = order.getFieldValue(['county']);
if (countyString) countyString = countyString.replace(/\s/g, '_');
if (zipString && zipString.indexOf(',') !== -1) {
//if so explode it into array
var zipArray = zipString.split(',');
var initialValue;
var rangeFrom;
var rangeTo;
var counter = 0;
var newZipString = '';
//loop through array and see if each one is the previous one plus one
for (var i = 0; i <= zipArray.length; i++) {
if (zipArray[i] != undefined) {
if (!initialValue) {
newZipString = ''
initialValue = zipArray[i];
newZipString += initialValue;
rangeFrom = initialValue;
counter = 0;
}
;
var j = i + 1;
var plusOne = Number(zipArray[i]) + 1;
if (plusOne == Number(zipArray[j])) {
var dash = (newZipString.charAt(newZipString.length - 1) != '-') ? '-' : '';
newZipString += dash;
counter++;
}
else {
var comma = (newZipString.charAt(newZipString.length - 1) != '-') ? ' , ' : '';
if (newZipString.indexOf(zipArray[i]) == -1) {
newZipString += comma + zipArray[i];
rangeTo = zipArray[i];
}
var isRange = (counter > 0) ? '1' : '';
if (isRange == '') {
rangeFrom = '';
rangeTo = ''
}
response.write('
<tr>
<td>US-CA-' + countyString + '</td>
<td>US</td>
<td>CA</td>
<td>' + newZipString + '</td>
<td> ' + rateString + '</td>
<td>' + isRange + '</td>
<td>' + rangeFrom + '</td>
<td>' + rangeTo + '</td>
<td>default</td>
</tr>
');
initialValue = null;
}
}
}
}
}
}
} else {
response.write(' no search results exists.');
}
} else {
response.write('Error: This page cannot be accessed directly.');
}
}
And there you have it! Some cool code that exports the NetSuite tax tables, puts it into Magento format and only gives you 50 records per page! With a little more work, you could build a Magento module that pulls this data periodically and updates Magento’s tables, but that’s a topic for another day.
Further study
As promised, here are some SuiteScript links:
Code Blog 