[dominicb]

I'm creating a saved search which outputs cases. On the output columns, I'm including Message Text ({message}), so am getting multiple rows when a case comprises of multiple messages.

I want to remove the multiple rows and am using a number of CASE WHEN clauses on the {status}, {messagedate},{lastmessagedate},etc criteria, but am struggling to get the right combination of attributes. I don't think I can use group on the output, as I'm also using CASE WHEN and SUM logic on other fields.

Is there a simpler option to remove the unwanted rows, ie. a criteria to only match last message, or an alternative attribute to use on the results? I've tried checking the Netsuite records browser for supportcase without success.
This is a cached copy. Click here to see the original post.

[torin@aminian.com]

Try the following in your results: Field
Summary Type
Formula

Number
Group

Message Date
Maximum

Formula (text)
Maximum
max(to_char(substr({messages.message},1,4000)))kee p(dense_rank last order by{messages.messagedate})

[dominicb]

Thanks torin. That's along the right lines, but we initially want to group by case type (with a count), only showing the message body on drilldown within the search result.

With that being the case, I think I need to somehow filter out the duplicates in the selection criteria.

[torin@aminian.com]

I think I know what you're looking for. Try using a formula (numeric) in the criteria. Use this formula: CASE WHEN {messagedate} = {lastmessagedate} THEN 1 ELSE 0 END. Set it to equal to 1.

Then in your results group the field Type and add a count of Internal ID. Add the fields like message and message date that you would like to see in the drill down and make sure they don't use a summary type.

Let me know if you have any luck with this.

[dominicb]

Thanks again torin, but I'm heading back to where I started now!

I thought exactly the same, but if I output {messagedate} and {lastmessagedate} as results columns, our system has cases with no {lastmessagedate} set. All seem to be internal messages, so I'm not sure whether this is an issue which needs addressing on our case forms, or a shortcoming with Netsuite..

[torin@aminian.com]

I'm not entirely sure I fully understand the issue but you could try using expressions in the criteria. Use an OR statement and add Last Message Date is empty.

[Author]

Posts