| Author |
Message |
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
   Topic: 2nd last commend
Posted: 30 May 2012 at 5:57am |
|
Hi all,
I am currently working on a report, given the last comment made on each case. However, every once in a while, we check if these cases are still correct. We place a comment in the 'Comment' table, saying 'Folder examined'.
However, if this is the case, I don't want to show the last comment, but the second last comment. I've tried nthlargest(2, {comment.comment}). But then he is just picking up the comment that starts with a Z or something. Is there a way to look at the comment date, determine if the comment is only 'Folder examined', and based on that show either the last comment or the second last comment?
Thanks in advance
Kind regards,
Gerben
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 30 May 2012 at 6:30am |
if the records are consecutive, you might try Prior or is Previous...but I don't know how nthlargest works. my personal favorite answer is to write a stored proc (which I always do), then you are in complete control of what value is written.
|
|
IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 30 May 2012 at 10:16pm |
|
I've never worked with stored proc before... How would I be creating this? Could you help me with that? Everything I know about Crystal is what I found out myself and what I've been told here
Thanks in advance
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 31 May 2012 at 12:59am |
A way to do it without prior/next and without a stored proc would be to merge your comment date with the comment so the nthlargest can work correctly;
Merge
totext(comment.date,"yyyyMMdd")
&
Comment
if "Folder Examined" in (nthlargest(1,merge,table.casegroup))
then mid(nthlargest(2,merge,table.casegroup),9)
else mid(nthlargest(1,merge,table.casegroup),9)
It might take abit of tinkering as I haven't tested it but that should get you what you need.
I too would create a stored proc for something like this though.
Regards,
Ryan.
Edited by rkrowland - 31 May 2012 at 4:31am
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 31 May 2012 at 3:33am |
|
Never used nthLargest, but seems like a nifty solution Ryan.
|
|
IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 31 May 2012 at 4:44am |
|
Thanks for your help Ryan. I do have 1 question though
if "Folder Examined" in (nthlargest(1,merge,table.casegroup))
What do you mean with table.casegroup? the field I am grouping on?
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 31 May 2012 at 4:56am |
That's the one, if you're not already grouped by "case" you'll need to for this to work.
Regards,
Ryan.
|
|
IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 31 May 2012 at 5:09am |
|
I'm getting the message "There must be a group matching this field", highlighting
(nthlargest(1,{@merge},{@03. C.D.#})
|
|
IP Logged |
|
|
|
