[MarkZSSI Contributions: 0Level 1☆]

I need to display the results of a saved search calculated field – Formula(numeric)

So far I was able to display the Internal ID, Name, Description which are grouped. Code so far looks like this:

Code:
function downstocking(request, response)
{
// Global Variables

var form;

// GET Request
if (request.getMethod() == ‘GET’)
{

var myform = createForm();
var mysublist = createSublist(myform);

var filters = new Array();

//select all and remove all
mysublist.addMarkAllButtons();

//Define search columns
var columns = new Array();
columns[0] = new nlobjSearchColumn(‘internalid’).setSort();
columns[1] = new nlobjSearchColumn(‘name’);
columns[2] = new nlobjSearchColumn(‘displayname’);
var searchresults = nlapiSearchRecord(‘item’,’20995′);

var p = 1;

for (var i = 0; searchresults != null && i < searchresults.length; i++) { var searchresult = searchresults[i]; mysublist.setLineItemValue('internalid',p,searchresult.getValue('internalid',null,'group')); mysublist.setLineItemValue('name',p,searchresult.getValue('name',null,'group')); mysublist.setLineItemValue('description',p,searchresult.getValue('description',null,'group')); p++; } function createForm() { form = nlapiCreateForm('Downstocking Report', false); form.addFieldGroup('maingroup', 'Select').setShowBorder(false); //form.addSubmitButton("Submit Checked Item(s)"); return form; } function createSublist(form) { list = form.addSubList('cc_inv_list', 'list', 'Downstocking Report', null); list.addField('internalid', 'text', 'Internal ID'); list.addField('name', 'text', 'Name'); list.addField('description', 'text','Description'); return list; } The above code works fine. It outputs this: The calculated fields in the saved search I'm referencing in the code that I need to display look like this: How do I get these calculated fields to display as shown above in the code that works to display it in NS? Thanks!
This is a cached copy. Click here to see the original post.

[david.smith Contributions: 0Level 1☆]

Use this:

https://chrome.google.com/webstore/d…epdmfaihdokglp

[MarkZSSI Contributions: 0Level 1☆]

Originally posted by david.smith

View Post

Use this:

https://chrome.google.com/webstore/d…epdmfaihdokglp

Thanks david.smith That works nicely to get the correct search. However I’m having issues displaying the formula fields. I have this as the code, and receive back this error message from NS:

You have entered an invalid form element name. It must be prefixed with “custpage”, unique, lowercase, and cannot contain any non-alphanumeric characters (except for the underscore character) in order to be added to the form or sublist.

Code:

Code:
function downstocking(request, response)
{
// Global Variables

var form;

// GET Request
if (request.getMethod() == ‘GET’)
{

var myform = createForm();
var mysublist = createSublist(myform);

//var filters = new Array();

//select all and remove all
// mysublist.addMarkAllButtons();

//START – Export As Script chrome app – 10/19/16

var inventoryitemSearch = nlapiSearchRecord(“inventoryitem”,null,
[
[“type”,”anyof”,”InvtPart”],
“AND”,
[“quantitycommitted”,”greaterthanorequalto”,”1″],
“AND”,
[“binonhandavail”,”greaterthan”,”0″],
“AND”,
[“type”,”anyof”,”InvtPart”]
],
[
new nlobjSearchColumn(“internalid”,null,”GROUP”),
new nlobjSearchColumn(“itemid”,null,”GROUP”).setSort(false),
new nlobjSearchColumn(“description”,null,”GROUP”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’A’,6,1) > 0) OR (INSTR({binnumber},’B’,6,1) > 0) OR (INSTR({binnumber},’C’,6,1) > 0) OR (INSTR({binnumber},’D’,6,1) > 0) OR (INSTR({binnumber},’E’,6,1) > 0) OR (INSTR({binnumber},’G’,6,1) > 0) OR (INSTR({binnumber},’H’,6,1) > 0) OR (INSTR({binnumber},’I’,6,1) > 0) OR (INSTR({binnumber},’J’,6,1) > 0) OR (INSTR({binnumber},’K’,6,1) > 0) OR (INSTR({binnumber},’L’,6,1) > 0) OR (INSTR({binnumber},’M’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’Q’,6,1) > 0) OR (INSTR({binnumber},’R’,6,1) > 0) OR (INSTR({binnumber},’S’,6,1) > 0) OR (INSTR({binnumber},’T’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN (INSTR({binnumber},’XDOCK’) ) 0 THEN NVL({binonhandavail},0) ELSE 0 END”)
]
);

//END – Export As Script chrome app – 10/19/16

var p = 1;

for (var i = 0; inventoryitemSearch != null && i < inventoryitemSearch.length; i++)

{
var searchresult = inventoryitemSearch[i];

mysublist.setLineItemValue('internalid',p,searchresult.getValue('internalid',null,'group'));
mysublist.setLineItemValue('itemid',p,searchresult.getValue('itemid',null,'group'));
mysublist.setLineItemValue('description',p,searchresult.getValue('description',null,'group'));
mysublist.setLineItemValue('formulanumeric',p,searchresult.getValue('formulanumeric'));
mysublist.setLineItemValue('formulanumeric',p,searchresult.getValue('formulanumeric'));
mysublist.setLineItemValue('formulanumeric',p,searchresult.getValue('formulanumeric'));
p++;

}

if(null){
response.writeLine("Nothing Found");
response.writeLine('

‘);

}else{
response.writePage(myform);
}

}

// POST response
else
{

}

}

function createForm()
{

form = nlapiCreateForm(‘Downstocking Report’, false);

form.addFieldGroup(‘maingroup’, ‘Select’).setShowBorder(false);

//form.addSubmitButton(“Submit Checked Item(s)”);

return form;
}

function createSublist(form)
{

list = form.addSubList(‘cc_inv_list’, ‘list’, ‘Downstocking Report’, null);

list.addField(‘internalid’, ‘text’, ‘Internal ID’);
list.addField(‘itemid’, ‘text’, ‘SKU’);
list.addField(‘description’, ‘text’,’Description’);
list.addField(‘formulanumeric’,’text’, ‘Bins A-M’);
list.addField(‘formulanumeric’, ‘text’,’Bins Q-T’);
list.addField(‘formulanumeric’, ‘text’,’XDOCK’);

return list;

}
If you comment out the formula fields, it works and returns the first 3 columns, no issues.

Any ideas on what I’m doing wrong?

[elie Contributions: 0Level 1☆]

Looks like it is just in the last three addField calls. You can’t add three fields that are all named the same (even though the error doesn’t quite state that(:

list.addField(‘formulanumeric’,’text’, ‘Bins A-M’); list.addField(‘formulanumeric’, ‘text’,’Bins Q-T’); list.addField(‘formulanumeric’, ‘text’,’XDOCK’);

[MarkZSSI Contributions: 0Level 1☆]

Originally posted by elie

View Post

Looks like it is just in the last three addField calls. You can’t add three fields that are all named the same (even though the error doesn’t quite state that(:

I thought that was the case also, but I made them formulanumeric1, 2, 3 and still nothing. Here is what displays now after making that change (code is the same, just added 1, 2, 3):

Any other ideas on what could be wrong? The columns as you see are empty.

[MarkZSSI Contributions: 0Level 1☆]

Originally posted by elie

View Post

Looks like it is just in the last three addField calls. You can’t add three fields that are all named the same (even though the error doesn’t quite state that(:

I thought that was the case also, but I made them formulanumeric1, 2, 3 and still nothing. Here is what displays now after making that change (code is the same, just added 1, 2, 3):

Any other ideas on what could be wrong? The columns as you see are empty.

[elie Contributions: 0Level 1☆]

Yes, I think you should change this call to not reference the name of the field:

“getValue”

mysublist.setLineItemValue(‘formulanumeric’,p,sear chresult.getValue(‘formulanumeric’));

[david.smith Contributions: 0Level 1☆]

try this

Code:
list.addField(‘custpage_formulanumeric1′,’text’, ‘Bins A-M’);

list.addField(‘custpage_formulanumeric2’, ‘text’,’Bins Q-T’);
​
list.addField(‘custpage_formulanumeric3’, ‘text’,’XDOCK’);

[MarkZSSI Contributions: 0Level 1☆]

hey elie and david.smith unfortunately, neither suggestion worked.

Here is the code:

Code:
function downstocking(request, response)
{
// Global Variables

var form;

// GET Request
if (request.getMethod() == ‘GET’)
{

var myform = createForm();
var mysublist = createSublist(myform);

//START – Export As Script chrome app – 10/19/16
var binam = [];
var inventoryitemSearch = nlapiSearchRecord(“inventoryitem”,null,
[
[“type”,”anyof”,”InvtPart”],
“AND”,
[“quantitycommitted”,”greaterthanorequalto”,”1″],
“AND”,
[“binonhandavail”,”greaterthan”,”0″],
“AND”,
[“type”,”anyof”,”InvtPart”]
],
[
new nlobjSearchColumn(“internalid”,null,”GROUP”),
new nlobjSearchColumn(“itemid”,null,”GROUP”).setSort(false),
new nlobjSearchColumn(“description”,null,”GROUP”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’A’,6,1) > 0) OR (INSTR({binnumber},’B’,6,1) > 0) OR (INSTR({binnumber},’C’,6,1) > 0) OR (INSTR({binnumber},’D’,6,1) > 0) OR (INSTR({binnumber},’E’,6,1) > 0) OR (INSTR({binnumber},’G’,6,1) > 0) OR (INSTR({binnumber},’H’,6,1) > 0) OR (INSTR({binnumber},’I’,6,1) > 0) OR (INSTR({binnumber},’J’,6,1) > 0) OR (INSTR({binnumber},’K’,6,1) > 0) OR (INSTR({binnumber},’L’,6,1) > 0) OR (INSTR({binnumber},’M’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’Q’,6,1) > 0) OR (INSTR({binnumber},’R’,6,1) > 0) OR (INSTR({binnumber},’S’,6,1) > 0) OR (INSTR({binnumber},’T’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN (INSTR({binnumber},’XDOCK’) ) 0 THEN NVL({binonhandavail},0) ELSE 0 END”)
]
);

//END – Export As Script chrome app – 10/19/16

var p = 1;

for (var i = 0; inventoryitemSearch != null && i < inventoryitemSearch.length; i++)

{
var searchresult = inventoryitemSearch[i];

mysublist.setLineItemValue('internalid',p,searchresult.getValue('internalid',null,'group'));
mysublist.setLineItemValue('itemid',p,searchresult.getValue('itemid',null,'group'));
mysublist.setLineItemValue('description',p,searchresult.getValue('description',null,'group'));
mysublist.setLineItemValue('formulanumeric',p,searchresult.getValue('formulanumeric'));
mysublist.setLineItemValue('formulanumeric',p,searchresult.getValue('formulanumeric'));
mysublist.setLineItemValue('formulanumeric',p,searchresult.getValue('formulanumeric'));
p++;

}

if(null){
response.writeLine("Nothing Found");
response.writeLine('

‘);
response.writeLine(‘Try Again Later‘);

}else{
response.writePage(myform);
}

}

// POST response
else
{

}

}

function createForm()
{

form = nlapiCreateForm(‘Downstocking Report’, false);

form.addFieldGroup(‘maingroup’, ‘Select’).setShowBorder(false);

//form.addSubmitButton(“Submit Checked Item(s)”);

return form;
}

function createSublist(form)
{

list = form.addSubList(‘downstocking_list’, ‘list’, ‘Downstocking Report’, null);

list.addField(‘internalid’, ‘text’, ‘Internal ID’);
list.addField(‘itemid’, ‘text’, ‘SKU’);
list.addField(‘description’, ‘text’,’Description’);
list.addField(‘custpage_formulanumeric1’, ‘text’,’Bins A-M’);
list.addField(‘custpage_formulanumeric2’, ‘text’,’Bins Q-T’);
list.addField(‘custpage_formulanumeric3’, ‘text’,’XDOCK’);

return list;

}

[david.smith Contributions: 0Level 1☆]

see if this works

Code:
var searchresult = inventoryitemSearch[i];
function downstocking(request, response)
{
// Global Variables

var form;

// GET Request
if (request.getMethod() == ‘GET’)
{

var myform = createForm();
var mysublist = createSublist(myform);

//START – Export As Script chrome app – 10/19/16
var binam = [];

var columns = [
new nlobjSearchColumn(“internalid”,null,”GROUP”),
new nlobjSearchColumn(“itemid”,null,”GROUP”).setSort(false),
new nlobjSearchColumn(“description”,null,”GROUP”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’A’,6,1) > 0) OR (INSTR({binnumber},’B’,6,1) > 0) OR (INSTR({binnumber},’C’,6,1) > 0) OR (INSTR({binnumber},’D’,6,1) > 0) OR (INSTR({binnumber},’E’,6,1) > 0) OR (INSTR({binnumber},’G’,6,1) > 0) OR (INSTR({binnumber},’H’,6,1) > 0) OR (INSTR({binnumber},’I’,6,1) > 0) OR (INSTR({binnumber},’J’,6,1) > 0) OR (INSTR({binnumber},’K’,6,1) > 0) OR (INSTR({binnumber},’L’,6,1) > 0) OR (INSTR({binnumber},’M’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’Q’,6,1) > 0) OR (INSTR({binnumber},’R’,6,1) > 0) OR (INSTR({binnumber},’S’,6,1) > 0) OR (INSTR({binnumber},’T’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN (INSTR({binnumber},’XDOCK’) ) 0 THEN NVL({binonhandavail},0) ELSE 0 END”)
]

var inventoryitemSearch = nlapiSearchRecord(“inventoryitem”,null,
[
[“type”,”anyof”,”InvtPart”],
“AND”,
[“quantitycommitted”,”greaterthanorequalto”,”1″],
“AND”,
[“binonhandavail”,”greaterthan”,”0″],
“AND”,
[“type”,”anyof”,”InvtPart”]
],
columns
);

//END – Export As Script chrome app – 10/19/16

var p = 1;

for (var i = 0; inventoryitemSearch != null && i < inventoryitemSearch.length; i++)

{
var searchresult = inventoryitemSearch[i];

mysublist.setLineItemValue('internalid',p,searchresult.getValue(columns[0]);
mysublist.setLineItemValue('itemid',p,searchresult.getValue(columns[1]));
mysublist.setLineItemValue('description',p,searchresult.getValue(columns[2]));
mysublist.setLineItemValue('custpage_formulanumeric1',p,searchresult.getValue(columns[3]));
mysublist.setLineItemValue('custpage_formulanumeric2',p,searchresult.getValue(columns[4]));
mysublist.setLineItemValue('custpage_formulanumeric3',p,searchresult.getValue(columns[5]));
p++;

}

if(null){
response.writeLine("Nothing Found");
response.writeLine('

‘);
response.writeLine(‘Try Again Later‘);

}else{
response.writePage(myform);
}

}

// POST response
else
{

}

}

function createForm()
{

form = nlapiCreateForm(‘Downstocking Report’, false);

form.addFieldGroup(‘maingroup’, ‘Select’).setShowBorder(false);

//form.addSubmitButton(“Submit Checked Item(s)”);

return form;
}

function createSublist(form)
{

list = form.addSubList(‘downstocking_list’, ‘list’, ‘Downstocking Report’, null);

list.addField(‘internalid’, ‘text’, ‘Internal ID’);
list.addField(‘itemid’, ‘text’, ‘SKU’);
list.addField(‘description’, ‘text’,’Description’);
list.addField(‘custpage_formulanumeric1’, ‘text’,’Bins A-M’);
list.addField(‘custpage_formulanumeric2’, ‘text’,’Bins Q-T’);
list.addField(‘custpage_formulanumeric3’, ‘text’,’XDOCK’);

return list;

}

[MarkZSSI Contributions: 0Level 1☆]

david.smith – real close – you just forgot to add setting new columns array. Once I did that, it worked great. I appreciate you taking your time to help me figure this out. The good news, this part of it is done, but this is only the first part of the code I need to finish. I need to pull in another saved search, as well as then do some calculations – vlookup to merge the two, and then some math to come up with the actual downstocking numbers.

But that’s for another day.

Here is the final code:

Code:
function downstocking(request, response)
{
// Global Variables

var form;

// GET Request
if (request.getMethod() == ‘GET’)
{

var myform = createForm();
var mysublist = createSublist(myform);

//var filters = new Array();

//select all and remove all
// mysublist.addMarkAllButtons();

//START – Export As Script chrome app – 10/19/16
var columns = [];
var inventoryitemSearch = nlapiSearchRecord(“inventoryitem”,null,
[
[“type”,”anyof”,”InvtPart”],
“AND”,
[“quantitycommitted”,”greaterthanorequalto”,”1″],
“AND”,
[“binonhandavail”,”greaterthan”,”0″],
“AND”,
[“type”,”anyof”,”InvtPart”]
],
[
columns[0] = new nlobjSearchColumn(“internalid”,null,”GROUP”),
columns[1] = new nlobjSearchColumn(“itemid”,null,”GROUP”).setSort(false),
columns[2] = new nlobjSearchColumn(“description”,null,”GROUP”),
columns[3] = new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’A’,6,1) > 0) OR (INSTR({binnumber},’B’,6,1) > 0) OR (INSTR({binnumber},’C’,6,1) > 0) OR (INSTR({binnumber},’D’,6,1) > 0) OR (INSTR({binnumber},’E’,6,1) > 0) OR (INSTR({binnumber},’G’,6,1) > 0) OR (INSTR({binnumber},’H’,6,1) > 0) OR (INSTR({binnumber},’I’,6,1) > 0) OR (INSTR({binnumber},’J’,6,1) > 0) OR (INSTR({binnumber},’K’,6,1) > 0) OR (INSTR({binnumber},’L’,6,1) > 0) OR (INSTR({binnumber},’M’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
columns[4] = new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’Q’,6,1) > 0) OR (INSTR({binnumber},’R’,6,1) > 0) OR (INSTR({binnumber},’S’,6,1) > 0) OR (INSTR({binnumber},’T’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
columns[5] = new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN (INSTR({binnumber},’XDOCK’) ) 0 THEN NVL({binonhandavail},0) ELSE 0 END”)
]
);

//END – Export As Script chrome app – 10/19/16

var p = 1;

for (var i = 0; inventoryitemSearch != null && i < inventoryitemSearch.length; i++)

{
var searchresult = inventoryitemSearch[i];

mysublist.setLineItemValue('internalid',p,searchresult.getValue(columns[0]));
mysublist.setLineItemValue('itemid',p,searchresult.getValue(columns[1]));
mysublist.setLineItemValue('description',p,searchresult.getValue(columns[2]));
mysublist.setLineItemValue('custpage_formulanumeric1',p,searchresult.getValue(columns[3]));
mysublist.setLineItemValue('custpage_formulanumeric2',p,searchresult.getValue(columns[4]));
mysublist.setLineItemValue('custpage_formulanumeric3',p,searchresult.getValue(columns[5]));
p++;
}

if(null){
response.writeLine("Nothing Found");
response.writeLine('

‘);
response.writeLine(‘Try Again Later‘);

}else{
response.writePage(myform);
}

}

// POST response
else
{

}

}

function createForm()
{

form = nlapiCreateForm(‘Downstocking Report’, false);

form.addFieldGroup(‘maingroup’, ‘Select’).setShowBorder(false);

//form.addSubmitButton(“Submit Checked Item(s)”);

return form;
}

function createSublist(form)
{

list = form.addSubList(‘downstocking_list’, ‘list’, ‘Downstocking Report’, null);

list.addField(‘internalid’, ‘text’, ‘Internal ID’);
list.addField(‘itemid’, ‘text’, ‘SKU’);
list.addField(‘description’, ‘text’,’Description’);
list.addField(‘custpage_formulanumeric1’, ‘text’,’Bins A-M’);
list.addField(‘custpage_formulanumeric2’, ‘text’,’Bins Q-T’);
list.addField(‘custpage_formulanumeric3’, ‘text’,’XDOCK’);

return list;

}
And a screenshot of the output as expected:

[david.smith Contributions: 0Level 1☆]

Obviously I didn’t test what I sent. Sorry for the miss. Glad to help and good luck with the next part.

[elie Contributions: 0Level 1☆]

Great news, good job. You should probably post your code for the sake of others who stumble upon this…

[MarkZSSI Contributions: 0Level 1☆]

Originally posted by elie

View Post

Great news, good job. You should probably post your code for the sake of others who stumble upon this…

Oh yeah, I shared the final code on this above. But yes, as I finalize it, I’ll post the whole thing. I just got the second saved search in there, and now I need to do the calculations and add printing features.

I’m a firm believer in giving back and sharing so others can use or at least take a modify.

[MarkZSSI Contributions: 0Level 1☆]

Originally posted by elie

View Post

Great news, good job. You should probably post your code for the sake of others who stumble upon this…

As promised, this is now completed. This is a reactive report for our warehouse so they can downstock items from higher up locations to pickable bins. If you use it or take parts of it for your own use, please share on this thread on if it was useful, etc..

OH — and I need help to add an Export to CSV/Excel button – can someone chime back in with an answer? Please?

The return:

Code:
function downstocking(request, response)
{
// Global Variables

var form;
var arr = [];
var arr2 = [];
var arr3 = [];

// GET Request
if (request.getMethod() == ‘GET’)
{

// comment out the below 2 lines if you want to go to raw mode
var myform = createForm();
var mysublist = createSublist(myform);

var columns = [];
var salesorderSearch = nlapiSearchRecord(“salesorder”,null,
[
[“type”,”anyof”,”SalesOrd”],
“AND”,
[“status”,”anyof”,”SalesOrd:D”,”SalesOrd:B”,”SalesOrd:E”],
“AND”,
[“quantitycommitted”,”greaterthan”,”0″],
“AND”,
[“type”,”anyof”,”SalesOrd”]
],
[
columns[0] = new nlobjSearchColumn(“internalid”,”item”,”GROUP”).setSort(false),
columns[6] = new nlobjSearchColumn(“item”,null,”GROUP”),
columns[7] = new nlobjSearchColumn(“quantitycommitted”,null,”SUM”),
columns[8] = new nlobjSearchColumn(“quantitypicked”,null,”SUM”),
columns[9] = new nlobjSearchColumn(“quantityshiprecv”,null,”SUM”)
]
);

var p = 1;

for (var i = 0; salesorderSearch != null && i 0) OR (INSTR({binnumber},’B’,6,1) > 0) OR (INSTR({binnumber},’C’,6,1) > 0) OR (INSTR({binnumber},’D’,6,1) > 0) OR (INSTR({binnumber},’E’,6,1) > 0) OR (INSTR({binnumber},’F’,6,1) > 0) OR (INSTR({binnumber},’G’,6,1) > 0) OR (INSTR({binnumber},’H’,6,1) > 0) OR (INSTR({binnumber},’I’,6,1) > 0) OR (INSTR({binnumber},’J’,6,1) > 0) OR (INSTR({binnumber},’K’,6,1) > 0) OR (INSTR({binnumber},’L’,6,1) > 0) OR (INSTR({binnumber},’M’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
columns[4] = new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN ((INSTR({binnumber},’Q’,6,1) > 0) OR (INSTR({binnumber},’R’,6,1) > 0) OR (INSTR({binnumber},’S’,6,1) > 0) OR (INSTR({binnumber},’T’,6,1) > 0)) THEN NVL ({binonhandavail},0) ELSE 0 END”),
columns[5] = new nlobjSearchColumn(“formulanumeric”,null,”SUM”).setFormula(“CASE WHEN (INSTR({binnumber},’XDOCK’) ) 0 THEN NVL({binonhandavail},0) ELSE 0 END”),
columns[6] = new nlobjSearchColumn(“quantitycommitted”,null,”GROUP”)
]
);

var p = 1;
for (var i = 0; inventoryitemSearch != null && i < inventoryitemSearch.length; i++)
{
var searchresult2 = inventoryitemSearch[i];
var binint = ('internalid',p,searchresult2.getValue(columns[0]));
var sku = ('itemid',p,searchresult2.getValue(columns[1]));
var desc = ('description',p,searchresult2.getValue(columns[2]));
var binsam = ('custpage_formulanumeric1',p,searchresult2.getValue(columns[3]));
var binsqt = ('custpage_formulanumeric2',p,searchresult2.getValue(columns[4]));
var xdock = ('custpage_formulanumeric3',p,searchresult2.getValue(columns[5]));
var commit = ('quantitycommitted',p,searchresult2.getValue(columns[6]));
arr2.push({bid: binint,am: binsam,qt: binsqt,xdock: xdock, sku: sku, desc: desc, comt: commit});
p++;
}

for(x in arr2){
var bid = arr2[x].bid;
for(j in arr){
var tid = arr[j].tid;
if(bid == tid){
arr3.push({picked: arr[j].picked, shipped: arr[j].shipped, tranint: arr[j].tid, comt: arr2[x].comt, sku: arr2[x].sku, desc: arr2[x].desc, binsam: arr2[x].am, binsqt: arr2[x].qt, xdock: arr2[x].xdock, binint: arr2[x].bid});
}
}
}

var p = 1;
var r = 1;
for (var i = 0; arr3 != null && i < arr3.length; i++)
{
var searchresult3 = arr3[i];
var picked = ('quantitypicked',p,searchresult3.picked);
var shipped = ('quantityshiprecv',p,searchresult3.shipped);
var binsam = ('custpage_formulanumeric1',p,searchresult3.binsam);
var commit = ('quantitycommitted',p,searchresult3.comt);
var downstock = parseInt(binsam)-(parseInt(commit)-(parseInt(picked)-parseInt(shipped)));
var tranid = ('tran',p,searchresult3.tranint);
var binid = ('bin',p,searchresult3.binint);
var skuid = ('itemid',p,searchresult3.sku);
var descrp = ('description',p,searchresult3.desc);
var qt = ('custpage_formulanumeric2',p,searchresult3.binsqt);
var xdock = ('custpage_formulanumeric3',p,searchresult3.xdock);
if (downstock < 0){
mysublist.setLineItemValue('tran',r,tranid);
mysublist.setLineItemValue('quantitypicked',r,picked);
mysublist.setLineItemValue('quantityshiprecv',r,shipped);
mysublist.setLineItemValue('bin',r,binid);
mysublist.setLineItemValue('itemid',r,skuid);
mysublist.setLineItemValue('description',r,descrp);
mysublist.setLineItemValue('custpage_formulanumeric1',r,binsam);
mysublist.setLineItemValue('custpage_formulanumeric2',r,qt);
mysublist.setLineItemValue('custpage_formulanumeric3',r,xdock);
mysublist.setLineItemValue('quantitycommitted',r,commit);
mysublist.setLineItemValue('downstock',r,(downstock*-1).toFixed(0));
r++;
}
p++;
}

if(null){
response.writeLine("Nothing Found");
response.writeLine('

‘);
response.writeLine(‘Try Again Later‘);

}else{

response.writePage(myform);

}

}

else {

}

function createForm()
{

form = nlapiCreateForm(‘Downstocking Report’, false);

form.addFieldGroup(‘maingroup’, ‘Select’).setShowBorder(false);

//form.addButton(‘custpagebutton1’, ‘Export CSV’, ‘exportcsvclient()’);

return form;
}

function createSublist(form)
{

list = form.addSubList(‘downstocking_list’, ‘list’, ‘Downstocking Report’, null);

list.addField(‘itemid’, ‘text’, ‘SKU’);
list.addField(‘description’, ‘text’,’Description’);
list.addField(‘custpage_formulanumeric1’, ‘text’,’Bins A-M’);
list.addField(‘custpage_formulanumeric2’, ‘text’,’Bins Q-T’);
list.addField(‘custpage_formulanumeric3’, ‘text’,’XDOCK’);
list.addField(‘quantitycommitted’, ‘text’,’Committed’);
list.addField(‘quantitypicked’, ‘text’,’Picked’);
list.addField(‘quantityshiprecv’, ‘text’,’Shipped’);
list.addField(‘downstock’, ‘integer’,’Downstock’);
return list;

}

}

[Author]

Posts