[Jochen]

Hello folks!

We want to display all our flagged customers in a shop locator system and our original idea was to automatically export all proper customers to a csv file to the file cabinet in Netsuite, which is also automatically fetched by a script and displayed in a google map. Does not sound so complex, but: the question is how to automatically save the results of a saved search (or product feed) in the Netsuite filesystem and provide it for download? Does the product feed feature help in any way?

Idea anyone?

Thanks and best regards,

Jochen

[Jochen]

or can we use a webservice instead?

[Jochen]

found this one: https://usergroup.netsuite.com/users…search-results

Unfortunately I am not able to reach bdougherty and ask for the solution.

[Jochen]

hello folks,

I think this is something that can be done with suitescript 2.0, right?

https://usergroup.netsuite.com/users…-dropbox-cloud

Is there someone out there who can help me in any way? Either coding a script or telling me the right sources for copy & paste?

Someone MUST have done this before and I don’t want to reinvent the wheel.

Thanks and best,

Jochen
This is a cached copy. Click here to see the original post.

[dominicb]

Depending on the number of results, the simplest option may be to create a saved search which outputs the customer data, then create a suitelet which publishes this to a public URL as JSON. You should then be able to use the suitelet URL as the dataLocation for the Google Maps API. The advantage to doing it this way is that the data will always be up-to-date. All this assumes it’s not too large a number of customers, otherwise script execution time may be problematic from a user experience perspective.

[JacksonP]

Something I did when I first started in SS2.0 / Javascript

Code:
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
define([‘N/file’, ‘N/record’, ‘N/search’],
/**
* @param {file} file
* @param {record} record
* @param {search} search
*/
function(file, record, search) {
// InvoiceCorrections();
/**
* Definition of the Scheduled script trigger point.
*
* @param {Object} scriptContext
* @param {string} scriptContext.type – The context in which the script is executed. It is one of the values from the scriptContext.InvocationType enum.
* @Since 2015.2
*/
function InvoiceCorrections(scriptContext) {

var closingMonth = search.create({
type: record.Type.SALES_ORDER,
columns: [‘internalid’,
‘department’,
‘entity’,
‘item’,
‘rate’,
‘effectiverate’,
‘tranid’,
search.createColumn({
name: ‘tranid’,
join: ‘fulfillingTransaction’
}),
search.createColumn({
name: ‘trandate’,
join: ‘billingTransaction’
}),
search.createColumn({
name: ‘trandate’,
join: ‘fulfillingTransaction’
}),
search.createColumn({
name: ‘postingperiod’,
join: ‘fulfillingTransaction’
}),
search.createColumn({
name: ‘postingperiod’,
join: ‘billingTransaction’
}),
search.createColumn({
name: ‘quantity’,
join: ‘fulfillingTransaction’
}),
search.createColumn({
name: ‘quantity’,
join: ‘billingTransaction’
}),
search.createColumn({
name: ‘tranid’,
join: ‘billingTransaction’
}),
search.createColumn({
name: ‘type’,
join: ‘billingTransaction’
}),
],
filters: [[‘department’, ‘anyof’, ’23’, ’29’],
‘and’,
[‘datecreated’, ‘after’, ‘monthsago8’],
‘and’,
[‘type’, ‘anyof’, ‘SalesOrd’],
‘and’,
[‘closed’, ‘is’, ‘F’]
]
});
closingMonth.filters.push(search.createFilter({
name: ‘type’,
join: ‘item’,
operator: ‘anyof’,
values: [‘InvtPart’, ‘Kit’]
}));
closingMonth.filters.push(search.createFilter({
name: ‘formulanumeric’,
operator: ‘greaterthan’,
values: ‘0’,
formula: “case when {billingtransaction.quantity} = {fulfillingtransaction.quantity} and {billingtransaction.postingperiod} {fulfillingtransaction.postingperiod} then 1 else 0 end”
}));
closingMonth.filters.push(search.createFilter({
name: ‘formulanumeric’,
operator: ‘greaterthan’,
values: ‘0’,
formula: “case when {department} = ‘Professional Sales’ and {billingtransaction.type} = ‘Cash Sale’ then 0 else 1 end”
}));

var myPagedData = closingMonth.runPaged({pageSize: 1000});
var resultSet = new Array();
resultSet.push(‘INTERNAL ID, DEPARTMENT, NAME, ITEM, ITEM RATE, EFFECTIVE RATE, DOCUMENT NUMBER, FULFILL NUMBER, FULFILL DATE, INV DATE, FULFILL PERIOD, INV PERIOD, FULFILL QTY, INV QTY, INV NUMBER, BILLING TYPE’);
myPagedData.pageRanges.forEach(function(pageRange){
var myPage = myPagedData.fetch({
index: pageRange.index
});

myPage.data.forEach(function(result){
var resultArr = new Array();

resultArr.push(‘”‘ + result.getValue(closingMonth.columns[0]) + ‘”‘); // INTERNAL ID
resultArr.push(‘”‘ + result.getText(closingMonth.columns[1]) + ‘”‘); // DEPARTMENT
resultArr.push(‘”‘ + result.getText(closingMonth.columns[2]) + ‘”‘); // NAME
resultArr.push(‘”‘ + result.getText(closingMonth.columns[3]) + ‘”‘); // ITEM
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[4]) + ‘”‘); // ITEM RATE
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[5]) + ‘”‘); // EFFECTIVE RATE
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[6]) + ‘”‘); // DOCUMENT NUMBER
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[7]) + ‘”‘); // FULFILL NUMBER
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[8]) + ‘”‘); // FULFILL DATE
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[9]) + ‘”‘); // INV DATE
resultArr.push(‘”‘ + result.getText(closingMonth.columns[10]) + ‘”‘); // FULFILL PERIOD
resultArr.push(‘”‘ + result.getText(closingMonth.columns[11]) + ‘”‘); // INV PERIOD
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[12]) + ‘”‘); // FULFILL QTY
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[13]) + ‘”‘); // INV QTY
resultArr.push(‘”‘ + result.getValue(closingMonth.columns[14]) + ‘”‘); // INV NUMBER
resultArr.push(‘”‘ + result.getText(closingMonth.columns[15]) + ‘”‘); // BILLING TYPE

resultSet.push(resultArr);
}); // MYPAGE.DATA LOOP

}); // MYPAGEDDATA LOOP

var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1;
var yyyy = today.getFullYear();
var date = mm.toString()+ ‘/’ + dd.toString() + ‘/’ + yyyy.toString();

// Creating a file name
var fname = ‘Invoice Corrections Report – Bill QTY = Fill QTY ‘ + date;

// Write to a CSV file
var fileObj = file.create({
name: fname,
fileType: file.Type.CSV,
contents: resultSet.join(‘n’)
});
// Accounting > Closing Month > Invoice Corrections
fileObj.folder = 4724267;
fileObj.save();
}

return {
execute: InvoiceCorrections
};

});

[chanarbon]

For the sample provided by JacksonP it really works well with the file cabinet. If you want to push it to external services like dropbox, you should first learn how to use their authentication model and their saver api (https://www.dropbox.com/developers/reference). If you want to save it on external SFTP, you may checkout N/sftp in Help Documentation once you already the file which on this case is the fileObj that you have rendered.

[chanarbon]

We could consolidate on this thread https://usergroup.netsuite.com/users…-dropbox-cloud

[Jochen]

Originally posted by dominicb

View Post

Depending on the number of results, the simplest option may be to create a saved search which outputs the customer data, then create a suitelet which publishes this to a public URL as JSON. You should then be able to use the suitelet URL as the dataLocation for the Google Maps API. The advantage to doing it this way is that the data will always be up-to-date. All this assumes it’s not too large a number of customers, otherwise script execution time may be problematic from a user experience perspective.

That really seems a cool idea for few datasets, but in my case every call from Google Maps would get around 500 lines back. That is too much for a solution like this.

Meanwhile I got feedback and a script from bdougherty and will try that. Thanks a lot so far! I’ll let you know about the outcome!

[madhur]

Hi Jochen Can you please share the Script, I am trying to do a similar kind of thing.

regards,

Madhur

[cblackburn]

See 17.1 release notes for a new relevant beta feature: SuiteScript 2.0 – Asynchronous Search API Beta Release

[savethepennies]

I’d also be interested in seeing the script that worked in the end (or some version that’s safe to share). I’m trying to figure out the best option at the moment for delivering the results of saved searches to an S3 bucket.

[Jochen]

This is the script I got, still not implemented, but should work! Developer: bdougherty !

Code:
Here you go. It should make sense based on the inline comments. You will need to create a set of script parameters that you can set for each deployment.

/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/

define([‘N/runtime’, ‘N/sftp’, ‘N/search’, ‘N/file’],
//require([‘N/email’, ‘N/runtime’, ‘N/sftp’, ‘N/search’, ‘N/file’],

function(runtime, ftp, search, file) {
function ftpFile(context) {

// Helper prototype function for delimiting info
String.prototype.replaceAll = function(str1, str2, ignore)
{
return this.replace(new RegExp(str1.replace(/([/,!\^${}[]().*+?|-&])/g,”\$&”),(ignore?”gi”:”g”)),(typeof(str2)==”strin g”)?str2.replace(/$/g,”$$$$”):str2);
};
/*
Location: demo.wftpserver.com
Username: demo-user
Password: demo-user
FTP Port: 21
FTPS Port: 990
SFTP Port: 2222
*/

try {
// FTP Parameters
var scriptObj = runtime.getCurrentScript();
var ftpHost = scriptObj.getParameter({name:’custscript_ftphost’} );
var ftpPort = scriptObj.getParameter({name:’custscript_ftpport’} );
var ftpSecurityKey = scriptObj.getParameter({name:’custscript_ftpseckey ‘});
var ftpUsername = scriptObj.getParameter({name:’custscript_ftpuserna me’});
var ftpPassword = scriptObj.getParameter({name:’custscript_ftppasswo rd’});

var runtimeSearchID = scriptObj.getParameter({name:’custscript_ftpsearch ‘});
var resultsFolderID = scriptObj.getParameter({name:’custscript_result_fo lder’});
var fileNamePrefix = scriptObj.getParameter({name:’custscript_filename_ prefix’});

var fieldSeperator = scriptObj.getParameter({name:’custscript_fieldSepe rator’}) == null ? ‘,’ : scriptObj.getParameter({name:’custscript_fieldSepe rator’});
var textQualifier = scriptObj.getParameter({name:’custscript_textQuali fier’}) == null ? ” : scriptObj.getParameter({name:’custscript_textQuali fier’});
}
catch(e) {
log.debug(‘Error loading FTP Parameters from Script’);
return false;
}

// var fieldSeperator = ‘,’;
// var textQualifier = ‘”‘;
var csvFile = ”;
var resultsArray = [[]];
var columnHeadingsArray = [];
var colummNamesArray = [];
var columnJoinArray = [];

var searchId = runtimeSearchID; // ID of an existing saved search 20344

try {
// Load and Execute search. Process to get header row and data rows.
var mySearch = search.load({
id: searchId
});

// Run search in Paged mode to handle large results
var pagedResults = mySearch.runPaged();

// Get Row Values and add to CSV array
pagedResults.searchDefinition.columns.forEach(func tion(col){ // log each column
columnHeadingsArray.push(addTextQualifier(col.labe l,textQualifier));
colummNamesArray.push(col.name); // Array of column names
});

resultsArray[0] = columnHeadingsArray;
csvFile += columnHeadingsArray.join(fieldSeperator) + ‘n’;

// Get the Rows Data and add to CSV array
pagedResults.pageRanges.forEach(function(pageRange ){
var myPage = pagedResults.fetch({index: pageRange.index});
myPage.data.forEach(function(result) {
var rowDataArray = [];
// need to cycle through columns and get values.
for (var i=0; i < colummNamesArray.length; i++) {
var data = (result.getText(result.columns[i]) == null) ? result.getValue(result.columns[i]) : result.getText(result.columns[i]);
data = addTextQualifier(data,textQualifier);
rowDataArray.push(data);
}
resultsArray.push(rowDataArray);
csvFile += rowDataArray.join(fieldSeperator) + 'n';
});
});

var today = new Date();
var fileName = fileNamePrefix + dateSuffix(today) + '.csv';

var myFileToUpload = file.create({
name: fileName,
fileType: file.Type.PLAINTEXT,
contents: csvFile,
encoding: file.Encoding.UTF8,
// folder: '30555772' // ID of file cabinet folder
folder: resultsFolderID
});
var fileID = myFileToUpload.save();

// var connection = sftp.createConnection({
// username: ftpUsername,
// passwordGuid: ftpPassword,
// url: ftpHost,
// directory: 'myuser/wheres/my/file',
// hostKey: ftpSecurityKey
// });
//
// connection.upload({
// directory: 'relative/path/to/remote/dir',
// filename: fileName,
// file: myFileToUpload,
// replaceExisting: true
// });

log.debug('Report Processed. FileCabinet ID: ' + fileID + '/n' + '/app/common/media/mediaitem.nl?id=' + fileID);

}
catch(e) {
log.debug('catch ' + e.toString());
}

// Helper functions
function dateSuffix(date) {
var YYYY = date.getFullYear();
var month = date.getMonth() + 1;
var MM = month < 10 ? '0' + month : '' + month;
var day = date.getDate();
var DD = day < 10 ? '0' + day : '' + day;

return YYYY+MM+DD
}

function addTextQualifier(str, qualifier) {
var escapedQualifier = qualifier + qualifier;
str = str.replaceAll(qualifier, escapedQualifier);
str = qualifier+ str + qualifier;
return str;
}
}

// ftpFile()

return {
execute: ftpFile

};
});

[savethepennies]

Thanks for sharing. I’ll review and see if it can help accomplish what we’re after.

[T.Watz]

Hi, thank you posting his script. It’s a great start.

I pieced it together and supplied correct sftp authentication info using borncorp’s sftp tool: https://ursuscode.com/netsuite-tips/…2-0-sftp-tool/

I just wanted to add for anyone else that thought you could use this with standard FTP, that you CAN NOT. I started out thinking that it would work with FTP but with more research I found out that SFTP and FTP/FTPS are completely different things.

Luckily for me the server I wanted to interface to did in fact need SFTP and not FTP, so that worked out well.

P.S

Code:
function(runtime, ftp, search, file) {
should be

Code:
function(runtime, sftp, search, file) {
and minus all the errant white spaces in the previous post.

D.S

[Author]

Posts