Netsuite Tips | Ursus Code

Getting Item Earliest Available Date Programatically

December 2, 2016 Adolfo Garza Comments 39 comments

I was browsing through the Netsuite user group and I stumbled upon this interesting question:

I have a client that wants to automate the process of checking/calculating the ‘Earliest Available Date’ from the ‘Check Item Availability’ feature on the item level of a sales order. I know that this calculation is done using the ATP lead time, but I am not sure how to initiate this calculation via SuiteScript or Saved Search. I have shown this issue to NetSuite support and they told me that it is not currently possible to access the “Earliest Available Date” by either Saved Search or API. It literally requires someone to click the “Check Item Availability” which loads the popup dialog and shows the date. My client would obviously like to avoid having to drill into every sales order and check this date.

Does anyone know of a way to display the ‘Earliest available date’ via Saved Search or API? Or many another way to calculate it via some other ATP lead time calculation.

I was able to reverse engineer the process that Netsuite uses to calculate the “Earliest Available date” and I came up with a solution that works within the browser console. The solution loads the popup in the back, parses the HTML and gets the data. This may help you get started on designing a more robust solution.

Here’s a sample output:

{

"transactionnumber" : "SLS00000684",

"location" : "1",

"subsidiary" : "1",

"quantity" : "20",

"item" : "553",

"type" : "InvtPart",

"availableDate" : "12/1/2016"

}

Here’s the code ready to run in the browser console:

Note: Since this is running in the browser you might need to play around with the script to get over 100 items at a time as the governance would run out. Alternatively you could use an Ajax call instead of nlapiRequestURL.

function getItemsToProcess() {

var results = [];

var filters = [];

var columns = [];

filters.push(new nlobjSearchFilter('type', null, 'anyof', ['SalesOrd']));

filters.push(new nlobjSearchFilter('trandate', null, 'within', ['12/1/2016', '12/1/2016'])); //Change this as needed

filters.push(new nlobjSearchFilter('type', 'item', 'anyof', ['Assembly', 'InvtPart']));

columns.push(new nlobjSearchColumn('transactionnumber', null, null));

columns.push(new nlobjSearchColumn('location', null, null));

columns.push(new nlobjSearchColumn('subsidiary', null, null));

columns.push(new nlobjSearchColumn('quantity', null, null));

columns.push(new nlobjSearchColumn('item', null, null));

columns.push(new nlobjSearchColumn('type', 'item', null));

results = nlapiSearchRecord('salesorder', null, filters, columns);

var itemsToProcess = [];

for (var i in results) {

var result = results[i];

var row = {};

row.transactionnumber = result.getValue(columns[0]);

row.location = result.getValue(columns[1]);

row.subsidiary = result.getValue(columns[2]);

row.quantity = result.getValue(columns[3]);

row.item = result.getValue(columns[4]);

row.type = result.getValue(columns[5]);

itemsToProcess.push(row);

}

return itemsToProcess;

}

function getItemAvailability(itemId, itemType, itemLocation, itemQty, subsidiary){

if (itemType != 'InvtPart' && itemType != 'Assembly'){

return "UNSUPPORTED";

}

var myURL = '/app/accounting/inventory/atp/checkitemavailability.nl?item=' + itemId + '&location=' + itemLocation + '&quantity=' + itemQty + '&subsidiary=' + subsidiary;

//myURL+= '&unit=' + unitOfMeasure; //Needs more work to pass this

var myResponse = nlapiRequestURL(myURL);

var myHTML = myResponse.getBody();

var parser = new DOMParser();

var doc = parser.parseFromString(myHTML, "text/html");

var availableDate = getEarliestAvailableDate(doc, itemId, itemQty, itemLocation);

return availableDate;

//**** HELPER FUNCTIONS ****//

function getEarliestAvailableDate(myDoc, itemId, itemQuantity, itemLocation) {

var quantity = parseFloat(itemQuantity) || 0;

var earliestavailabledate = '';

if (quantity && itemId && itemLocation) {

var numberOfLines = getOrderMachineSize(myDoc);

for (var i = 1; i <= numberOfLines; i++) {

if (quantity <= parseFloat(getOrderMachineValue(myDoc, 'hiddenavailability', i))) {

earliestavailabledate = getOrderMachineValue(myDoc, 'hiddendorderdate', i);

return earliestavailabledate;

}

var atpleadtime = myDoc.forms['main_form'].elements['atpleadtime'].value;

if (!atpleadtime || !earliestavailabledate) {

earliestavailabledate = nlapiDateToString(nlapiAddDays(new Date(), atpleadtime));

return earliestavailabledate;

}

return earliestavailabledate;

//******* HELPER FUNCTIONS ******//

function getOrderMachineValue(myDoc, fieldName, line) {

return myDoc.forms['order_form'].elements[fieldName + line].value;

}

function getOrderMachineSize(myDoc) {

var numberOfLines = 0;

while (myDoc.forms['order_form'].elements['hiddenavailability' + ++numberOfLines] != null);

return numberOfLines - 1;

}

function run(){

var itemsToProcess = getItemsToProcess();

var itemsProcessed = [];

for(var i in itemsToProcess){

var itemToProcess = itemsToProcess[i];

itemToProcess.availableDate = getItemAvailability(itemToProcess.item, itemToProcess.type, itemToProcess.location, itemToProcess.quantity, itemToProcess.subsidiary);

itemsProcessed.push(itemToProcess);

}

return itemsProcessed;

}

Convert Salesforce Ids from 15 to 18 characters using Javascript

December 1, 2016 Adolfo Garza Comments 798 comments

I figured this will be useful for anybody working with Salesforce. I used this within Netsuite and worked beautifully.

Here’s the code:

function convertSFDC15To18(sfdcID15){

if (sfdcID15.length == 15) {

var s = "";

for (var i = 0; i < 3; i++) {

var f = 0;

for (var j = 0; j < 5; j++) {

var c = sfdcID15.charAt(i * 5 + j);

if (c >= "A" && c <= "Z")

f += 1 << j;

}

s += "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".charAt(f);

}

return sfdcID15 + s;

} else {

throw "Error : " + sfdcID15 + " has not a length of 15 characters. Current length detected: " + sfdcID15.length + " characters.";

}

//Example:

var sfdc18 = convertSFDC15To18('5003000000D8cuI'); // Returns 5003000000D8cuIAAR

Free Automatic Translation Within Netsuite using Yandex Translate API

December 1, 2016 Adolfo Garza Comments 701 comments

In this challenge I decided to integrate Netsuite with Yandex Translate REST API for the following reasons:

Free version includes:
- Up to 10 million characters a month (or 322,580 characters per day spread evenly in 31 days)
- Up to 1 million characters in 24 hours (would last 10 days only as max is 10 million per month)
- Translation quality is decent.
- Great for small translation projects.

Let’s say we want to translate a FreeText field, that has a max of 300 characters so that would allow me for a max workload of around 1,075 translations per day, and that’s maxing out all 300 characters per request. Not bad.

Getting started is really easy:

Go to https://tech.yandex.com/translate/
Get a free API Key (You might need to register)
Call the Yandex API within Netuiste (Follow the code example below)

I tested this on an after submit script that translates the memo field and saves the result in another field called “Memo in Spanish”

Don’t forget to check Yandex Translate API documentation to learn more about the languages and more features such as auto detecting languages.

Here’s the code example:

/* = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =\

|| This user event script translates Memo text into ||

|| "Memo in Spanish" field after submit ||

|| ||

|| Version Date Author Remarks ||

|| 1.0 Dec 01 2016 Adolfo Garza Initial commit ||

\= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = */

var APIKEY = 'trnsl.1.1.20161201T045'; //<< Replace with your key

var URL = 'https://translate.yandex.net/api/v1.5/tr.json/translate?key=%API-KEY%'.replace('%API-KEY%', APIKEY);

function afterSubmit() {

var memo = nlapiGetFieldValue('memo');

var memoInSpanish = translateText(memo, 'en-es'); //<< You can set language here

nlapiSubmitField(nlapiGetRecordType(), nlapiGetRecordId(), 'custbody_memo_in_spanish', memoInSpanish);

}

function translateText(text, lang){

var reqBody = {text: text, lang: lang};

var reqUrl = URL;

var myResponse = nlapiRequestURL(reqUrl, reqBody);

var responseBody = JSON.parse(myResponse.getBody());

var responseTextArray = responseBody.text || [];

var responseText = responseTextArray.join(' ');

return responseText;

}