Extracting a Unique Record 1

[csunix]

I'm trying to extract a single date value, which is the lowest in a set. It is in relation to a person who has number of future meetings e.g. 14 Sept 2004, 15 Nov 2004, 5 March 2005. However, I'm only trying to return the next meeting date, this being 14 Sept 2004. So far I have meeting_date > currentdate to prevent it picking up old dates. I have tried to use minimum(meeting_date), but that just returns 3 records of 14 Sept.
Can anyone suggest how I can get around this problem.

Regards, CS

 

[Katy44]

Could you provide details of the tables involved?

 

[csunix]

There are 4 tables.
One is a table for the person storing names address etc, this links to another table which holds details of their start date, end date, position, id of company, which then links to the company itself, this in turn links them to a table which holds meeting dates for the compay and it's members.

Hope this is enough to go on.

 

[lisanguyen]

I would create a variable and assign this variable the value of your datefield if that date is greater than today. But I would only do this once.

Step 1: Group on the person
Step 2: Create your declaration formula that goes in the Group Header
WhilePrintingRecords;
Numbervar Counter;
Datevar DateHolder;
Counter := 1;
//reset your dateholder anyway you like, I'm just going to
//use yesterday's date
DateHolder := today - 1;

Step 3: Create the Evaluation formula that will go in the Details Section. This formula will only grab the datefield value if the date is greater than today and if the counter we created is 1 meaning we stop as soon as we find the first meeting that will occur after today.

WhilePrintingRecords;
Numbervar Counter;
Datevar DateHolder;

If datefield > Today and Counter <= 1 then
(
DateHolder := datefield;
Counter := Counter + 1;
)
;

Step 4: Displaying the next meeting - this goes in the group footer
WhilePrintingRecords;
Datevar DateHolder;
If DateHolder >= Today then
totext(DateHolder)
else
"There are no meetings for this person";

========================

Let me know if this works.

 

[csunix]

Thanks, I'll let you know how I get on.

Regards, CS

 

[csunix]

The only problem I can see with this is that the main details part of the report is a text object which contains a letter and putting the final dislay in the group footer may cause a problem.

CS

 

[lbass]

If you can use:

{meeting.date} > currentdate

in the record selection formula, then you can go to report->edit selection formula->GROUP and enter:

{meeting.date} = minimum({meeting.date},{table.customerID})

...where {table.customerID} is your group field. This should return only one date per customer.

-LB

 

[lisanguyen]

nice solution lbass
=)

 

[csunix]

Thanks for the replies.

CS