Hi All, needing some guidance on a saved search fo...
# suiteanalyticsm
Manda
03/19/2021, 7:12 PMHi All, needing some guidance on a saved search formula please. We have a custom record joined to items for item messages which allows entry of start and end dates for the message. An item may have multiple active messages at any given time. These custom records are then set to inactive 1 day after the end date and then deleted 8 days after the end date. In the results the formula text (max summary) works with NS_CONCAT(distinct{custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem}) BUT this formula also displays the inactive messages as well. To weed out the inactive records I've been trying multiple variations to no avail. I started out getting invalid formula as a result to now just getting unexpected error when trying to save the search. Current variation is this: Formula (Text) > Group Summary (I've also tried Max) > CASE WHEN {custrecord_ifd_messageitemnumber.isinactive}='F' THEN NS_CONCAT({custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem}) ELSE NULL END
g
George McMullen
03/19/2021, 7:17 PMFirst try to see if this runs in a non-summarized search.
Copy code
CASE WHEN {custrecord_ifd_messageitemnumber.isinactive}='F'
THEN
{custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem}
ELSE
NULL
ENDGeorge McMullen
03/19/2021, 7:18 PMIf that works, try:
Copy code
NS_CONCAT(
CASE WHEN {custrecord_ifd_messageitemnumber.isinactive}='F'
THEN
{custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem}
ELSE
NULL
END
)George McMullen
03/19/2021, 7:19 PMYou could also try:
Copy code
LISTAGG(
CASE WHEN {custrecord_ifd_messageitemnumber.isinactive}='F'
THEN
{custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem}
ELSE
NULL
END
, ', '
) WITHIN GROUPGeorge McMullen
03/19/2021, 7:19 PMWith those last two formulas, use MIN or MAX summary type.
George McMullen
03/19/2021, 7:20 PM(and to format code you can either press the </> button below or use 3 back-ticks `
m
Manda
03/22/2021, 3:14 PMThank you!! The NS_Concat version you gave me works except that I can't seem to incorporate the 'distinct' logic back into it so I get the same message for each location (4 times). The Listagg gets me an Invalid Expression error. It's so close!
g
George McMullen
03/22/2021, 8:31 PMPerhaps the DISTINCT function in Oracle is not supported in NetSuite yet. It’s in Oracle 19c. Can’t remember which version of Oracle Netsuite is on, and they may still not support everything.
George McMullen
03/22/2021, 8:32 PMWere you trying this:
Copy code
LISTAGG(
DISTINCT
CASE WHEN {custrecord_ifd_messageitemnumber.isinactive}='F'
THEN
{custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem}
ELSE
NULL
END
, ', '
) WITHIN GROUPm
Manda
03/22/2021, 8:52 PMUgh! I was trying the distinct function everywhere but before CASE 🤦♀️ Thank you! This one worked...NS_CONCAT(distinct CASE WHEN {custrecord_ifd_messageitemnumber.isinactive}='F' THEN {custrecord_ifd_messageitemnumber.custrecord_ifd_messagebyitem} ELSE NULL END)
g
George McMullen
03/22/2021, 9:11 PMAwesome! Glad you got it working!
George McMullen
03/22/2021, 9:11 PMAnd good to know that it works too!
m
Manda
03/23/2021, 6:35 PMThanks so much for your help!