Solved

how to show last record in Crystal Formula

Posted on 2014-10-15
14
2,771 Views
Last Modified: 2014-10-23
I have a problem where I'm already using an If Then to cull out a note for a detail record.

"if {annotation.notetext} startswith "*" then {annotation.notetext}"  placing the formula output on the details of the report.......

but now I wish to also skinny this down to only show the most recent entry only matching the above criteria.  Not a specific date, just most recent entry of a note that startswith "*".   I've tried various suppression formulas and just can't seem to come up with the right expression.

Ideas?

thanx,

steve
0
Comment
Question by:Gocsteve1
  • 7
  • 6
14 Comments
 

Expert Comment

by:Willarki
ID: 40383141
Suppression formula:
not OnLastRecord, if you have an Ascending sort
or
not OnFirstRecord if your sort is Descending
0
 
LVL 34

Expert Comment

by:James0628
ID: 40383948
Your options will depend on exactly what you're trying to do, starting with where you're trying to show that most recent result (report footer, group footer, etc.).

 OnLastRecord/OnFirstRecord presumably won't work, because then you'd only be looking at the "last" (most recent) record, and that record may not have the text that you're looking for.

 Here's a kind of unusual approach that seems to work (if I understand correctly what you're trying to do).  I am assuming that you have a date/datetime field to identify the "most recent" entry.

 Create a formula similar to this.  I'm just calling it formula1.  You can change the name to something more meaningful.

if {annotation.notetext} startsWith "*" then
  CStr ({your datetime field}, "yyyy/MM/dd HH:mm:ss") + "|" + {annotation.notetext}

 If your "date" field is just a date, not a datetime, you can leave out the format for the time (" HH:mm:ss").

 That formula looks for notetext values that start with an "*".  When it finds one, it combines the datetime and notetext in a string, with the datetime in front.  For example, if notetext was "*some string" and the datetime in that record was 06/12/2009 at 9 AM, you'd get

2009/06/12 09:00:00|*some string


 The formula assumes that notetext will never contain a "|" character.  If it could, you'd need to use something else to separate notetext from the datetime.

 If notetext does not start with an "*", the formula just produces an empty string.

 Create another formula like this (call it whatever you want):

if "|" in Maximum ({@formula1}) then
  Split (Maximum ({@formula1}), "|") [ 2 ]


 The Maximum for formula1 will be the notetext that started with an "*" that had the latest datetime.  If there were no records with a notetext that started with "*", the maximum would be an empty string, which would cause an error on the last line.  So, it checks to make sure that the maximum value includes the "|" character.  If it does, it splits the string at that character and takes the second part (the [ 2 ]), which will be the notetext field.

 If you're looking for the most recent "*" notetext within a group, you'd add the group field to both uses of the Maximum function:

if "|" in Maximum ({@formula1}, {group field}) then
  Split (Maximum ({@formula1}, {group field}), "|") [ 2 ]


 James
0
 

Author Comment

by:Gocsteve1
ID: 40384289
Ok, Thanks both for commenting.... I had tried OnLastRecord and that did exactly what James said it would...... if the last record didn't startwith "*" it showed a blank field instead of the last entry with an *

As for your idea James, step one does concatenate the Date Time field of the record with the notetext of the record with the "|" between.   So good there..... but when I create the second formula, I assume this now contains my matching results if placed on Details.... only returns  a single record repeated thruout the report.
10-16-2014-8-57-43-AM.pdf
0
 
LVL 34

Expert Comment

by:James0628
ID: 40384858
The second formula is supposed to produce only one result.  That's the point.  It's showing you the latest "*" notetext.

 I never actually said this, but the idea was to put the second formula where you want to see that note.  For example, in the report footer, or in a group footer if you're looking for the latest note in that group.


 However, I just realized that I may have misunderstood what you were after.

 I thought you wanted the report to still include the same records, and just wanted to show that latest note somewhere separate.

 If you're actually trying to eliminate the other details and only show the record(s) that contains the latest "*" notetext, that's different.

 If you want to remove the other details from the report, the first thing would be to go to Report > Selection Formulas > Record and enter

{annotation.notetext} startswith "*"

 If you already have a record selection formula, just add that test to the others.

 Then the report will _only_ include the records with a notetext that starts with "*".  It won't include any other notetext values.

 Then, if you're looking for the latest "*" notetext in the entire report, you could sort by the date/datetime field and use OnFirstRecord or OnLastRecord, like Willarki suggested, to suppress everything but the latest record.

 Or, if you're looking for the latest "*" notetext for a group, you could sort by the date/datetime field within the group, suppress the details, and put the fields that you want to see in the group header or footer, depending on whether the sort is descending or ascending.  For example, if you sort by the date, ascending, then the last record in the group will be the one with the latest date.  If you put the fields in the group footer, you'll get the values from the last record in the group.

 Of course with something like this, the report is still reading the records for other dates, so if you do any summaries, they'll include those other records.  And if the report is reading a _lot_ of old records (ie. you have a lot of history in your db), it may take a while to run.  If that becomes an issue, you may need to consider creating a query that will find the latest date and use that to select the record, rather than having the db send every "*" notetext record to the report, and then having the report just suppress everything but the latest record.

 James
0
 

Author Comment

by:Gocsteve1
ID: 40385406
Thanx James,  I'm looking to show all results..... but only the last note with a leading "*".  By putting startswith * in the select, I am eliminating records with no notes which I don't want to do.   I want to show all records grouped (GH2 is a policy #) with the most recent note entry starting with "*" in the details.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40385475
I want to show all records grouped (GH2 is a policy #) with the most recent note entry starting with "*" in the details.
That's what my original suggestion should do, and that's what it seems to be doing in the PDF file that you posted.  The latest datetime seems to be 10/15 20:57, and that note is "* Agent to deliver 10/15", so that's what you get from formula2.

 That seems to be what you were asking for, but, once again, I see where there could be a misunderstanding.

 When you said "most recent", I thought you meant "most recent, period".  Out of _all_ of the records in the report, or in a group, you wanted to see the _last_ note.

 Instead of "most recent, period", did you really mean "previous"?  The most recent note prior to the current record?

 If so, the next question is, how do you define "previous"?

 The records in that PDF file are not sorted by the note date, so "prior record in the report" and "prior date" are two different things.  For example, the datetime on the second record is 10/15 17:05.  The prior record in the report would be the first record, with a note dated on 10/13.  But the note with the most recent prior datetime is in the 4th record, with a time of 17:04.

 James
0
 

Author Comment

by:Gocsteve1
ID: 40386678
When you said "most recent", I thought you meant "most recent, period".  Out of _all_ of the records in the report, or in a group, you wanted to see the _last_ note.

No, I'm looking for the most recent entry, matching "startswith *"  associated with the GH2 (policy #).

Note that Formula 2 results were just a repeating of the same result in every details...."* Agent to deliver 10/15"  which must be THE MOST RECENT entry period!    It should really be the most recent entry startswith "*" associated with GH2 (Policy #)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 40387249
Note that Formula 2 results were just a repeating of the same result in every details...."* Agent to deliver 10/15"  which must be THE MOST RECENT entry period!    It should really be the most recent entry startswith "*" associated with GH2 (Policy #)
Assuming that "most recent" means latest date, that _was_ the most recent "*" note.  The last date shown in that PDF file was 10/15 @ 20:57 and that note said "* Agent to deliver 10/15", so that's what you get.

 You mentioned GH2 and a "policy #", but, of course, I have no idea how (or if) GH2 relates to the things in that PDF file, and I don't see any "policy #" in it either.

 IAC, if you're looking for the last "*" note in a group, then, as I mentioned earlier, you need to include the group field in the Maximum functions.  Formula2 would be something like this:

if "|" in Maximum ({@formula1}, {group field}) then
  Split (Maximum ({@formula1}, {group field}), "|") [ 2 ]

 Replace "group field" with your group field, of course.

 James
0
 

Author Comment

by:Gocsteve1
ID: 40387374
Bingo!  I get it now.   works like a champ!!

Thanx James.....
0
 
LVL 34

Expert Comment

by:James0628
ID: 40387615
That's great to hear.  So, was the problem that you were taking the maximum for the whole report, but you should have been taking the maximum for the policy # group instead?  Just trying to understand.

 James
0
 

Author Comment

by:Gocsteve1
ID: 40387637
Correct!
0
 
LVL 34

Expert Comment

by:James0628
ID: 40388623
Great.  I'm glad we finally got it sorted out.  If everything's working, then don't forget to close the question.

 James
0
 

Author Closing Comment

by:Gocsteve1
ID: 40399825
Thanks James!  You are THE BEST!!!
0
 
LVL 34

Expert Comment

by:James0628
ID: 40401000
You're welcome.  Glad I could help.

 James
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now