Display info from a different record

[hcasaol]

I am very new to CR (also to this forum) so please bear with me.

I am working with a contact database. I need to create an "Expiration Date report" that will give me contact information of someone with the criteria of

{Contact.ID/Status} = "Licensing Agent"

, but show the expiration date that is listed under contacts with

{Contact.ID/Status} = "Licensee Contact".

The contacts are tied together by a value called Ticker Symbol (they should have the same ticker symbol).

The thing is I want to be able to prompt for a date range and also sort the list by Contact.Contact name.

I would appreciate any help

HC

 

[lbass]

It's a little hard to tell what you mean, but here's a try:

Add the contact table twice, and link them by {contact.tickersymbol}. In your record select statement, use something like:

{contact.status} = "Licensing Agent" and
{contact_1.status} = "Licensee Contact" and
{?date} = {contact_1.expdate} //where {?date} is a range

Group on {contact.status} and put contact information in the group header and then use {contact_1.status} as your detail field. You can add the latter field to your sort list or group on it to get the alphabetical order.

-LB

 

[hcasaol]

You are awesome LB!! It worked, I didn't do the grouping part cause I didn't need to. Thanks for your help.

HC