This topic contains 5 replies, has 0 voices, and was last updated by torin@aminian.com 7 years, 5 months ago.
-
AuthorPosts
-
dominicb- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
Try the following in your results: Field
Summary Type
FormulaNumber
GroupMessage Date
MaximumFormula (text)
Maximum
max(to_char(substr({messages.message},1,4000)))kee p(dense_rank last order by{messages.messagedate}) -
dominicb- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
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- Contributions: 0
- Level 1
- ☆
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.
-
AuthorPosts
You must be logged in to reply to this topic.