[bdougherty]

I’m trying to create a SS2 script that executes a saved search and then parses the results into a CSV file to FTP to another system.

My search has several FormulaText and FormulaCurrency fields in it. When I try to parse the Search.results object all the formulatext fields have the same names so I can’t access each unique column.

Two questions:

1. How do you differentiate different formula fields (result.getValue(‘formulatext’) in the search result?

2. Does anyone have any suggestions on how to export search results? Ideally, there would be an existing function in the Search module to do this (like in the UI).

(I’ve attached a prototype I’m doing that works in the debugger. Note: Search ID and FileCabinet ID would need to be changed).

Any ideas/suggestion always appreciated.

Thanks,

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

[michoel]

Originally posted by bdougherty

View Post

I’m trying to create a SS2 script that executes a saved search and then parses the results into a CSV file to FTP to another system.

My search has several FormulaText and FormulaCurrency fields in it. When I try to parse the Search.results object all the formulatext fields have the same names so I can’t access each unique column.

Two questions:

1. How do you differentiate different formula fields (result.getValue(‘formulatext’) in the search result?

The result.getText() / result.getValue() functions have two versions. If you have multiple columns with the same type of formula field, you need to use result.getText(column) rather than result.getText(options).

Try replacing this line:

Code:
var data = (result.getText({name:colummNamesArray[i],join:columnJoinArray[i]}) == null) ? result.getValue({name:colummNamesArray[i],join:columnJoinArray[i]}) : result.getText({name:colummNamesArray[i],join:columnJoinArray[i]});
with –

Code:
var data = result.getText({column: result.columns[i]}) ? result.getText({column: result.columns[i]}) : result.getValue({column: result.columns[i]});

2. Does anyone have any suggestions on how to export search results? Ideally, there would be an existing function in the Search module to do this (like in the UI).

I don’t believe there is any existing API function to generate a CSV file. I’ve shared some code I use here which may be helpful, though it uses SS v1.0

https://usergroup.netsuite.com/users…v-excel-button

(I’ve attached a prototype I’m doing that works in the debugger. Note: Search ID and FileCabinet ID would need to be changed).

Any ideas/suggestion always appreciated.

A suggestion to clean up your code a little – there is no need to run the search the first time to get the columns. After you have loaded the search, you can read the columns using mySearch.columns.

[david.smith]

For Q#1: The easiest way in your case would probably be to create your columns as variables before you add them to your search object. The below is not working but can show you the idea.

As for Q#2: I have a JSON to CSV that I wrote. There are a few out there if you search for them. Or take a look at michoel link above.

Code:
var itemtype ={
name: “custitem1”,
join: “item”
};
var promoCode = {
name: “custrecord_offer_code”,
join: “CUSTCOL_CSEG_PROMOTION”
};
var issueddate = {
label: “issued”,
name: “formulatext”,
summary: “GROUP”,
formula: “case when {custrecord_sub_processed_date} <= bla bla else 'false' end"
}
var transactionSearchObj = search.create({
type: 'transaction',
filters: filters,
columns: [
"entity",
search.createColumn(issueddate),
search.createColumn(itemtype),
search.createColumn(promoCode),
"currency"
]
});

— get results loop…..
var issuedDate = result.getValue(issueddate);
… more code…

[bdougherty]

Thanks for the helpful suggestions. I make some updates and see how it goes.

[bdougherty]

I made the updates and things work just like I need now. Thanks again for the help.

[Jochen]

Hello bdougherty, this is exactly, what I need too. Is it possible to get the script from you and customize for my purpose?

[savethepennies]

I’m also interested in the solution, if it’s something that can be shared. One of my current tasks involves saving csv files from saved searches to an Amazon S3 bucket. This sounds similar enough to be a great starting point.

[darrenhillconsulting]

I don’t normally plug pre-built solutions, but you guys should really check out https://www.tacticalconnect.com/

[savethepennies]

Appreciate the feedback. I’m checking it out now.

[MChammaTX]

The new async search API beta release may help too in 2017.1

“NetSuite 2017.1 exposes to SuiteScript 2.0 the ability to trigger asynchronous search operations, and to automatically export these results into a CSV file stored in the file cabinet.”

[Author]

Posts