https://netsuiteprofessionals.com logo
Join Slack
Powered by
Does anyone know of a consistently working way to ...
# suitescript
d
Does anyone know of a consistently working way to use summary filters in a search? I've tried using 
search.createFilter()
, filter expressions and making the search in the UI and then loading it with 
search.load()
but all of those methods result in the 
INVALID_SRCH_SUMMARY_TYP
error. My search needs to filter on customers who have X tasks with specific criteria, so I have to count the result of a formula. I have sort of done that before, but in that case I only needed to count the amount of subrecords on a custom record. That also used a formula, but copying that and adjusting it to fit my needs for this search also resulted in that error.
b
basically all 3 of those should be able to do the job
probably want to share what your attempts looked like
keep in mind that summary filters dont work without at least 1 group summary column
d
oh wow I did not know about the group summary column
Is that a difference between the UI and suitescript searches?
b
the ui simply wont use your summary filter
you probably want to be extra sure that your search does what its supposed to do
d
Also, my attemps:
Copy code
search.createFilter({
    name: 'formulanumeric',
    formula: "CASE WHEN {task.custevent_taak_type.id} = 4 AND {task.status.id} != 'COMPLETE' THEN {task.internalid} ELSE null END",
    operator: 'is',
    values: "1",
    summary: search.Summary.COUNT,
  })
Copy code
[
    "formulanumeric: CASE WHEN count(CASE WHEN {task.custevent_taak_type.id} = 4 AND {task.status.id} != 'COMPLETE' THEN {task.internalid} ELSE null END) = 3 THEN 1 ELSE" +
    " 0 END",
    "is",
    "1",
  ]
yeah I have to double check that it works then
the filter expression is one I copied (albeit the formula is different) from a different script in which it works, and the results are what I expect them to be
b
filter expression doesn't look right
there is no count in there anywhere
d
its after the first CASE WHEN
b
thats not where it goes
load the search in code, and log its filter expression
probably after making sure there is one grouping summary column
d
Yes give me a second
b
d
Yes I use that extension, but it didnt work for the summary filter. Probably because like you said, I needs a group summary column
I also loaded the search after adding a group summary column, and the filter expression is indeed very different
Copy code
[
      "count(formulanumeric: CASE WHEN {task.custevent_taak_type.id} = 4 AND {task.status.id} != 'COMPLETE' THEN 1 ELSE null END)",
      "equalto",
      "1"
   ]
Interestingly enough, the search extension gives me 
Export as Script Not Supported
b
what are you searching?
d
customers
It is the same search where I logged the filter expression from
b
sounds weird, to my knowledge customers are supported by that extension
d
oh absolutely, I have used it with customer searches in the past as well
I changed the owner, it was previously system, and now the export does work
s
in general, when I start seeing CASE WHEN statements I tend to think SuiteQL might be a better/easier fit.
d
What is SuiteQL? I have not heard of that before
s
it's the SQL dialect for querying in script. Search that term [SuiteQL] in NS help. It's part of N/query. Or wait and the magic battk help link machine may have something to say
3 Views
Open in Slack
PreviousNext
Powered by