[MarkZSSI]

I originally posted a request here – but thought I’d make this a distinct thread on it’s own.

How would I add an Export button that works to export the returned results to Excel or CSV? Thanks in advance!
This is a cached copy. Click here to see the original post.

[erictgrubaugh]

Where do you want this button to exist? The link you provided just goes to the user group homepage.

[chanarbon]

Hi @MarkZSSI,

For this concern, I browsed on the details on the link that you provided and here’s my insights:

1. Based on the code, its get function will just return a page with a form. For this concern, you should add a parameter checker e.g. if the export=T on the URL which in code would be request.getParameter(“export”) == T.

2. Once you have the condition checker from number 1, you should have a function to render the CSV file and return it as the response file.

3. Once you have fulfilled the details in 1 & 2, you should have a button on the form created for the non-export=T and deploy a client script which only does a window.location = the current suitelet URL + export=T

[michoel]

We actually have a Suitelet that does something quite similar – it adds a button to the Customer form to download a CSV of their 12 month purchase history.

The actual CSV logic is quite primitive, but seems to be working fine so far.

Code:
**
* Add ‘Export History as CSV’ button to Customers
*
* Script ID: customscript_mos_historycsv_ue
* Script Type: User Event
* Deployed to: Customer
*
*/

function beforeload(type, form) {
if (type == ‘view’) {
var script = “window.open(nlapiResolveURL(‘SUITELET’, ‘customscript_mos_historycsv_sl’, ‘customdeploy_mos_historycsv_sl’) + ‘&custom_id=’ + nlapiGetRecordId());”;
form.addButton(‘custpage_historycsv’,’Export History as CSV’, script);
}
}

/**
* Suitelet to Export the CSV
*
* Script ID: customscript_mos_historycsv_sl
* Script Type: Suitelet
*
*/

function suitelet(request, response) {

try {
var id = request.getParameter(‘custom_id’);
if(! id) {
response.write(‘custom_id parameter missing’);
}

var filename = nlapiLookupField(‘customer’, id, ‘companyname’) + ‘ 12 Month History.csv’;
response.setContentType(‘CSV’, filename);
response.write(“SKU,Description,Last Purchase Price,YTD Quantityn”);

var filters = [
[‘type’, ‘anyOf’, [‘CashRfnd’, ‘CustCred’, ‘CustInvc’, ‘CashSale’] ],
‘AND’, [‘item.type’, ‘anyof’, [‘InvtPart’, ‘Assembly’, ‘Kit’, ‘NonInvtPart’, ‘OthCharge’, ‘Service’] ],
‘AND’, [‘trandate’, ‘onorafter’, ‘monthsago12’],
‘AND’, [‘entity’, ‘anyOf’, id]
];

var columns = [
new nlobjSearchColumn(‘itemid’, ‘item’, ‘GROUP’),
new nlobjSearchColumn(‘salesdescription’, ‘item’, ‘MIN’),
new nlobjSearchColumn(‘quantity’, null, ‘SUM’).setSort(true),
new nlobjSearchColumn(‘formulacurrency’, null, ‘MAX’)
.setFormula(‘{netamount} / NULLIF({quantity},0)’)
.setWhenOrderedBy(‘trandate’)
];

nlapiCreateSearch(‘transaction’, filters, columns)
.runSearch()
.forEachResult(function(result) {
var row = [
result.getValue(‘itemid’, ‘item’, ‘GROUP’),
result.getValue(‘salesdescription’, ‘item’, ‘MIN’),
result.getValue(‘formulacurrency’, null, ‘MAX’),
result.getValue(‘quantity’, null, ‘SUM’)
];

row = row.map(function(field) {
return ‘”‘ + field.replace(/”/g, ‘””‘) + ‘”‘;
});

response.write(row.join() + “n”);
return true;
});

} catch(err) {
response.write(err + ‘ (line number: ‘ + err.lineno + ‘)’);
return;
}
}

[chanarbon]

@MarkZSSI,

You could also check out michoel’s suggestion.

michoel, I think Mark want to have the button his suitelet in the link the he provided so more or less the button for generating the CSV export should be a part of the Suitelet.

---

michoel replied on 10/30/2016, 08:44 PM: I understood that, I was just sharing some of my existing code in the hopes he or others might find it useful

[MarkZSSI]

Thanks for sharing michoel and thanks for the follow up chanarbon

[MarkZSSI]

Hey chanarbon and michoel – I’m back on this project again. Anyways, having issues grasping the total concept here.

So on the main script it’s basically a form with a button. The button does a redirect to this script:

Code:
response.sendRedirect(“SUITELET”, ‘customscript289’, ‘customdeploy1’, false);
Which opens up the results. The results is combination of an inventory and transaction search combined via arrays to have this final loop which sets the sublist and renders the page:

I just loop over the final array after combining and doing some data manipulation after the two searches are done.

Code:
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++;
}
The above loop and results that show on the page is what I need to put in the CSV.

How do I do that? Please advise soon. Thanks!

[michoel]

MarkZSSI, Looks like you are almost there 🙂

You want your Suitelet response to be the contents of the CSV.

So the first line of the output would be the column headers:

Code:
response.write(“Column 1,Column 2,Column 3,Column 4n”);
Then, you would loop through your array and output each row, delimiting each field with commas:

Code:
for(var i = 0; i < data.length; i++) {
response.write(data[i].column1 + "," + data[i].column2 + "," + data[i].column3 + "," + data[i].column4 + "n");
}
(If your data contains any commas, line breaks or quotation marks, you need to make sure to enclose each cell in quotations marks and convert quotation marks to two quotation marks.)

Finally, set the Content Type to CSV so that the browser knows to download the file instead of displaying it.

Code:
​response.setContentType('CSV', "output.csv");

[MarkZSSI]

michoel – that worked great!

Code:
response.write(“SKU,DESCRIPTION,BINS A-M,BINS Q-T,XDOCK,COMMITTED,PICKED,SHIPPED,DOWNSTOCKn”);
for(var i = 0; i < data.length; i++) {
response.write(data[i].skuid + "," + data[i].description + "," + data[i].binsam + "," + data[i].qt + "," + data[i].xdock + "," + data[i].commit + "," + data[i].picked + "," + data[i].shipped + "," + data[i].downstock + "n");
}

response.setContentType('CSV', "Downstocking-CSV-Import-as-Text-into-Excel.csv");
Question – how do you convert the CSV to an Excel or how do enclose one column with quotes? I tried

Code:
response.write(data[i]."skuid"
but that didn't seem to work. That's why I named it really long to let the warehouse people know to import it into excel as a CSV, and not just open it as unfortunately we have SKU's with leading zeros.

Any idea how to do this or how to convert it to an Excel format where the leading zero's are not stripped?

Thanks again for the above!

[david.smith]

Code:
// look to see if the column data has quotes in it and replace them with double quotes for proper formatting in csv
if(coldata && coldata.indexOf(‘”‘)>=0){
coldata = coldata.replace(/”/g,'””‘);
}

[mhson1978]

Originally posted by MarkZSSI

View Post

michoel – that worked great!

Question – how do you convert the CSV to an Excel or how do enclose one column with quotes? I tried

Code:
response.write(data[i].”skuid”
but that didn’t seem to work. That’s why I named it really long to let the warehouse people know to import it into excel as a CSV, and not just open it as unfortunately we have SKU’s with leading zeros.

Any idea how to do this or how to convert it to an Excel format where the leading zero’s are not stripped?

Thanks again for the above!

instead of using double quote to concatenate your strings, use single quote. Then, use double quote as string wrapper.

Like this ‘”‘+skuid[i]+'”, “‘+SOMEOTHERVALUE+'”n’;

in terms of having CSV NOT remove the leading zero, you could trying adding ‘in front of your value.

so ‘””+SOMEVALUE+'”,’

When this is opened in excel, excel will treat it as string value, not number.

[david.smith]

I provided the code to double quote above. Here is why. This is from MS’s site for Excel.

https://support.office.com/en-us/art…f603d0e585#bm4

CSV (Comma delimited)

This file format (.csv) saves only the text and values as they are displayed in cells of the active worksheet. All rows and all characters in each cell are saved. Columns of data are separated by commas, and each row of data ends in a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks.

If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark.

[Author]

Posts