This topic contains 5 replies, has 0 voices, and was last updated by torin@aminian.com 7 years, 6 months ago.

  • Author
    Posts
  • #4892

    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.

  • #4893

    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})

  • #4894

    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.

  • #4895

    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.

  • #4896

    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..

  • #4897

    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.

You must be logged in to reply to this topic.