need to create summary report based on history entries

[andie18]

Hello,

I need to write a report that will show how many (numerical) records have not had a history entry on it in 180 and over 365 days. Basically, it is a slacker tattle tale report. The database is in GoldMine MSSQL 2K in the table conthist.

How would I go about looking at these records to find the last entry and then see if it falls into my criteria. some of these records have a few dozen entries in conthist and I need to find the latest entry only. the field in conthist is conthist.ondate

tia for the help

 

[MColeman]

You could create a group (numerical record?) and then do a Top N sort on date.

 

[lbass]

First, I'm assuming you are using "records" to refer to accounts of some sort, not to refer to the more technical meaning of rows of data. Let's say you want to evaluate which accounts have not had any entries in the last x number of days. First you would group on {table.acctID}. Next, sort records using {conthist.ondate} in descending order. Then insert a running total, using the running total editor. Select {table.acctID}, distinctcount, evaluate based on a formula:

datediff("d",{conthist.ondate},currentdate) > 180 and
{table.acctID} <> previous({table.acctID})

Reset never.

This would evaluate only the first record of each group, which because of the sort, will be the maximum date, and give you a total of all accounts that meet that criterion.

Create a second running total for > 365 criterion, just changing the evaluation formula.

-LB

 

[andie18]

I understand the first part and the last part but.... I feel like such a boob for asking but I cannot for the life of me figure out where the running total editor is. I thought that you meant the insert summary but I cannot get it to recognize your formula so I know that I am very wrong.

I'm using crystal 8.5 pro and I have access to 8.5 developer.

thanks by the way for the help.

 

[lbass]

In 8.0 pro, you would find it in the field explorer. Go to insert->field object->running total fields->new. Or it might be insert->running total field->new, depending on whether you have applied a service pack. I believe 8.5 has a similar layout. I call this the "running total editor" or "expert" or "wizard", but it's really just a screen for creating running totals.

-LB

 

[andie18]

doh! thanks.

I have a question the reference that you make to {table.acctID} is that for the geenral account? The way that GoldMine works is that the general account is in contact1 and all history stuff is in conthist. Do I link to contact1.account or conthist.account? I am pulling some pretty high numbers off of each and I know that is wrong.

 

[lbass]

{table.acctID} was just my name for whatever your group field is. You initially stated that you were evaluating "records" and wanted to find the most recent entry for each "record"--whatever field identifies this "record" should be your group field. I don't know whether you need the contact1 table or not, since I don't know anything about your overall report.

-LB

 

[andie18]

thanks,

I tried a lot of different variations but I am still coming up with some strange numbers. I think that I did poorly at explaining where the data is, so if you don't mind I am going to try to re-explain this.

The database is GoldMine 5.5 (Sql2K backend) I need to count records. The main record accounts live in the table called CONTACT1. I need to look at the history table (CONTHIST) and find the newest history date entry (Conthist.ondate) and then count the records from Contact1.accountno that the last conthist.ondate is older than 180 days. So if the sales rep last entered a history note on a particular record on 12/31/2002, I need to count that record (contact1.accountno). but if the last date that a sales rep entered a history note was on 3/31/2004, I do not need to count that record.

does this make more sense? I'm sorry for being so foggy.

 

[andie18]

In a fit of hary-cary, I created a new report.

I used this formula in the select expert

{CONTHIST.ONDATE} <= (currentdate - 180)

The report doesn't barf but..... it is counting the records based on the first history note entry. Bascially I need it in reverse. I need it to look at the last history note added and then pull those records older than 180 days.

I went to report/ sort records and chose conthist.ondate - descending. I thought that would do it but alas, no!

I'm also running these counts (I am doing 180 and 365) as subreports linking to the contact1.key5 field, which is the sales rep name. On the main form, I have 1 group (contact1.key5) - to seperate by sales rep assigned. I have inserted the subreports into the footer of the group. The link seems to be working properly I just have the conthist.ondate in reverse.

I sure hope that I am making sense and if I'm not, I'm really sorry about that. Too much caffeine and too little sleep.

 

[lbass]

I don't think your approach will work, since you would be pulling history records that met your criterion even though there might be history records with a date in the last six months.

Here's a different approach. You don't need to use subreports. I'm not sure about your group structure, whether you have one group on {contact1.acctno} or two groups, with the second (inner?) group on sales rep. But for now, let's assume one group on {contact1.acctno}. Create two formulas:

//{@grthan180} to be placed in the group header or footer for the acct number group:

whileprintingrecords;
numbervar diff;
numbervar grthan180;

if datediff("d",maximum({conthist.ondate}, {contact1.accountno}), currentdate) > 180 then
diff := 1 else diff := 0;
grthan180 := grthan180 + diff;

//{@displaygrthan180} to be placed in the report footer:
whileprintingrecords;
numbervar grthan180;

This will give you the grand total count of accounts with a most recent date greater than 180 days ago.

-LB