Returning Most Current Date - minor problem 1

[tobypsl]

I am using Crystal reports 8.5 to generate reports on a client contact database. There are 4 tables one each for Companies, Contacts and Notes and a note links table linking the notes ID to the contact ID, fields as follows;

RCLIENTS CONTACTS NOTLINK rNotes
ObjectID ObjectID LinkID NoteIDN
Company CoID NotePos NoteDate
CoType Job Title NoteIDN NoteMemo
County

CONTACTS.CoID = RCLIENTS.ObjectID, NOTLINK.LinkID = CONTACTS.ObjectID and rNotes.NoteIDN = NOTLINK.NoteIDN

I have the Clients linked to the contacts linked to the Notlink linked to the rNotes in a straight line.

I would like to list each company with each contact at that company and a date at which they were last contacted. I would like to select these by CLIENTS.County and grouped by CoType. The report I have selects and groups OK but lists every date they were contacted.

I have tried various methods suggested in the forum but they don't work. The one I like the look of is to edit Report, Edit Selection Criteria, Group and enter an expression - but the expression doesn't work. I can get only the most current contacts (ie. today) to display but any clients not contacted today will not show up.

Perhaps I have my links set up wrong ?

any help appreciated ...

 

[GMcNamara]

Insert a group on the contact date in descending order. Show the details in the group header and you should display only the last date.

 

[lbass]

To use the group selection method, be sure you have a group on the contact ID (can't tell which field this is above}. Then your group selection formula should look like:

{table.contactdate} = maximum({table.contactdate},{table.contactID})

Substitute the correct fields--can't tell what your contact date field is either--maybe {rNotes.NoteDate}?

-LB

 

[tobypsl]

GMcNamara - will try the group header method, thanks for tip.

LB - did try this earlier with

{rNotes.NoteDate}=maximum({rNotes.NoteDate},{RCONTS.ObjectID})

but it returns with an error The running total / summary field could not be calculated.

i have grouped on CONTACTS.ObjectID

 

[lbass]

I think then that you placed the formula in the wrong place. Go to report->edit selection formula->GROUP and enter it there.

-LB

 

[synapsevampire]

LB's suggestion sounds right to me, perhaps as LB forewarned you do not have it grouped accordingly.

-k

 

[tobypsl]

that works - I thought it was sufficient to put the group in using the report expert and it didn't work - but inserting it manually seems to have done the trick. 2 further points however - if I have contacted the client several times in the same day each separate contact will show up (presumably I can suppress duplicates by editing the field in the usual way?)

also any companies whose contacts have not yet been called no longer show up - can I set null date fields to contain an arbirtrary date so they do appear or can the formula be ammended in some way.

thanks for help so far !!

 

[tobypsl]

actually I can't suppress duplicates in the way I normally - which is to highlight th fields in teh report and edit each to stop duplication - as obviously some contacts sharte the same company name. can the group selection formula be set up to identify only DISTINCT dates ?

 

[lbass]

Try something like {@date}:

if isnull({rNotes.NoteDate}) then date(9999/09/09) else
{rNotes.NoteDate}

Then use a group selection formula of:

{@date} = maximum({@date},{RCONTS.ObjectID})

For the duplication, go to the section expert->details->suppress->x+2 and enter:

{RCONTS.ObjectID} = previous({RCONTS.ObjectID})

Make sure your date field is used as a sort field in descending order.

-LB

 

[MrBillSC]

It seems like you could report the contact date in the ContactDate group footer. Then the date would only appear one time. Am I missing something?
MrBill