https://netsuiteprofessionals.com logo
Join Slack
Powered by
This message was deleted.
# suitescript
s
This message was deleted.
m
here's the working summary criteria formula in a saved search:
message has been deleted
writing filters without using 
search.createFilter()
works fine, example:
Copy code
var itemfulfillmentSearchObj = search.create({
   type: "itemfulfillment",
   filters:
   [
      ["mainline","is","T"], 
      "AND", 
      ["type","anyof","ItemShip"], 
      "AND", 
      ["max(formulanumeric: case when {file.name} like '%asnFileCabinet%' then 1 else 0 end)","equalto","0"]
   ],
...
here's what doesn't work:
Copy code
var filters = [];
        filters.push(search.createFilter({name: 'mainline', operator: 'is', values: true}));
        filters.push(search.createFilter({name: 'type', join: 'createdfrom', operator: 'anyof', values: 'ItemShip'}));
        filters.push(search.createFilter({name: 'formulanumeric', formula: "case when {file.name} like '%asnFileCabinet%' then 1 else 0 end", operator: "equalto", summary: 'max'}));
    var itemFulfillments = search.create({type: 'transaction', filters: filters});
this is the error encountered when using 
search.createFilter
with the 
summary
option:
"An nlobjSearchFilter contains an invalid summary type: formulanumeric: MAX."
same error with any summary type and both with + without using a formula.
but, this is what the loaded search returns:
message has been deleted
(or the less-pretty but successful in SS2.0 filterExpression):
message has been deleted
curious if this is a defect or something i'm doing blatantly wrong. i can work with either for what i'm doing, but would prefer to use search.createFilter for simplicity
fwiw, this is what i would expect to work but yields the same error:
Copy code
filters.push(search.createFilter({name: 'formulanumeric', formula: "case when {file.name} like '%asnFileCabinet%' then 1 else 0 end", operator: "equalto", values: "0", summary: 'max'}));
documentation:
message has been deleted
¯\_(ツ)_/¯
b
summary filters need summary columns
and in this case values
m
i've got both, it works fine when written out in as an array but not when using search.createFilter
example above was intended to keep it as focused on filters as possible
here's using name/entity as the example based on the documentation:
Copy code
require(['N/search'], function(search){
    var filters = [];
        filters.push(search.createFilter({name: 'mainline', operator: 'is', values: true}));
        filters.push(search.createFilter({name: 'type', join: 'createdfrom', operator: 'anyof', values: 'SalesOrd'}));
        filters.push(search.createFilter({name: 'entity', operator: 'anyof', values: 1649, summary: 'group'}));
    var columns = [];
        columns.push(search.createColumn({name: 'name', summary: 'group'}));
    var docSearch = search.create({type: 'transaction', filters: filters, columns: columns});
        console.log(docSearch.runPaged().count);
});
message has been deleted
here's that same criteria written out in the longer form & successfully returning results:
message has been deleted
Copy code
require(['N/search'], function(search){
    var filters = [['mainline','is',true],'AND',['createdfrom.type','anyof','SalesOrd'],'AND',['max(formulanumeric: {entity})','equalto',1649]];
    var columns = [];
        columns.push(search.createColumn({name: 'entity', summary: 'group'}));
    var docSearch = search.create({type: 'transaction', filters: filters, columns: columns});
        console.log(docSearch.runPaged().count);
});
this isn't the search criteria I intend to use in my solution, but an incredibly watered down example. i've created a ticket hoping netsuite identifies a defect or tells me what specifically is wrong with my input
one more with max in both places; arbitrary at this point though
message has been deleted
b
summary criteria require grouping in the columns
they cannot group themselves
Copy code
require(['N/search'], function(search){
    var filters = [];
        filters.push(search.createFilter({name: 'mainline', operator: 'is', values: true}));
        filters.push(search.createFilter({name: 'type', join: 'createdfrom', operator: 'anyof', values: 'SalesOrd'}));
        filters.push(search.createFilter({name: 'entity', operator: 'anyof', values: 1649, summary: 'group'}));
    var columns = [];
        columns.push(search.createColumn({name: 'name', summary: 'group'}));
    var docSearch = search.create({type: 'transaction', filters: filters, columns: columns});
        console.log(docSearch.runPaged().count);
});
will fail since its at attempt to do grouping in the filter criteria
suitescript is more specific in using summary criteria
the ui will ignore them if you do them incorrectly and dont group your columns
suitescript will error
m
i understand what you're saying, but in this context it's incorrect
and it works in my example as summary criteria when writing the long form array of filters vs. search.createFilter, which is why i started this thread to see if anyone had working examples
summarized columns combine & summarize the results. summary criteria filters the summarized results when summarized results exist. this works in the UI and works in script in both 1.0 and 2.0, but in 2.0 specifically works in one way of writing filters but not the other
will post defect # once NetSuite gets back to me
b
what did your attempt using summary criteria with a grouping column look like
m
here's the full working example:
Copy code
var filters = [];
			filters.push(search.createFilter({name: 'mainline', operator: 'is', values: true}));
			filters.push(search.createFilter({name: 'type', join: 'createdfrom', operator: 'anyof', values: 'SalesOrd'}));
		
		if(params.custparam_sps_includeprevious!='T'){
			filters.push(search.createFilter({name: "max(formulanumeric: case when {file.name} like '%poAckFileCabinet%' then 1 else 0 end)", operator: 'equalto', values: 0}));
		}
		
		if(params.custparam_sps_customer){
			filters.push(search.createFilter({name: 'entity', operator: 'anyof', values: params.custparam_sps_customer}));
		}
here are related columns in the same request:
Copy code
var columns = [];
			columns.push(search.createColumn({name: 'name', summary: 'group'}));
			columns.push(search.createColumn({name: 'trandate', summary: 'group'}));
			columns.push(search.createColumn({name: 'tranid', summary: 'group', sort: search.Sort.ASC}));
			columns.push(search.createColumn({name: 'otherrefnum', summary: 'group'}));
this successfully filters and outputs results for transactions which have zero files attached containing the text poAckFileCabinet in the file name
without the summary criteria, if multiple files are attached, results will output a transaction that has multiple files - even if a non-summarized filter is used to exclude 
file.name does not contain %poAckFileCabinet%
let me correct the above, sorry --- example of filters is from the rewrite in the format i'd prefer to use
getting the example with working summary filters which doesn't use search.createFilter
Copy code
var filters = 
   [
      ["mainline","is","T"], 
      "AND", 
      ["type","anyof","SalesOrd"], 
      "AND", 
      ["max(formulanumeric: case when {file.name} like '%poAckFileCabinet%' then 1 else 0 end)","equalto","0"],
	  "AND",
	  ["entity","anyof",params.custparam_sps_customer]
   ]
b
Copy code
var filters = 
   [
      ["mainline","is","T"], 
      "AND", 
      ["type","anyof","SalesOrd"], 
      "AND", 
      ["max(formulanumeric: case when {file.name} like '%asnFileCabinet%' then 1 else 0 end)","equalto","0"],
	  "AND",
	  ["entity","anyof",params.custparam_sps_customer]
   ]
is reasonable, its a filter expression
m
^ same criteria, the difference is this works: 
["max(formulanumeric: case when {file.name} like '%asnFileCabinet%' then 1 else 0 end)","equalto","0"],
right
b
Copy code
filters.push(search.createFilter({name: "max(formulanumeric: case when {file.name} like '%poAckFileCabinet%' then 1 else 0 end)", operator: 'equalto', values: 0}));
is not, its a filter expression inside of a regular filter
has no chance of working
m
right, but here's another example of what doesn't work with it despite SS2.0 documentation indicating it should
filters.push(search.createFilter({name: 'formulanumeric', formula: "case when {file.name} like '%poAckFileCabinet%' then 1 else 0 end", operator: 'equalto', values: 0, summary: max}));
b
by itself that doesnt work
m
right, it should. hence the likely defect
b
fair chance max is not a valid identifier
in combination, it can work if you had a summary column which grouped your results
m
it should work
it doesn't work
b
it cant work
m
facepalm
thanks for weighing in
b
what you are sharing is probably invalid since max is not a string
m
sorry, let me correct that
filters.push(search.createFilter({name: 'formulanumeric', formula: "case when {file.name} like '%poAckFileCabinet%' then 1 else 0 end", operator: 'equalto', values: 0, summary: 'max'}));
syntax aside
it does not work, see explanation & examples above and feel free to try it out yourself
b
i have
m
👍
b
there are 2 parts you need correct
the filters and the columns
m
sure
b
and so far you have never shared both in a correct form
2 Views
Open in Slack
PreviousNext
Powered by