If an Individual has changed PensionNo all his records has to Change 2

[FinnB]

In a Report I have to create a formula to handle this situation:
An Individual has changed his PensionNo to 925 therefore All earlier records PensionNo has to changed to 925.
I.e.:
This is how the records are shown in the Database:
PensionNo EmploymentNo StartDate Enddate
004 1448 2001/03/04 2001/05/31
004 1448 2001/06/01 2001/10/10
004 1448 2001/10/11 2002/11/22
925 1448 2001/11/23 2003/07/01

This is how they should be presented in the Crystal Reports:

925 1448 2001/03/04 2001/05/31
925 1448 2001/06/01 2001/10/10
925 1448 2001/10/11 2001/11/22
925 1448 2001/11/23 2003/07/01

Do You have any suggestion??
Best Regards,
FinnB

 

[synapsevampire]

The first suggestion would be to handle this on the database side:

But this can be handled in CR too.

You'll need to group by employment number and use a subreport.

Right click the group header and select insert section below.

In the GH 1a place the follwing formula:

whileprintingrecords;
shared numbervar Pension := 0;

In GH 1b place the subreport which is linked by the employment number.

The subreport contains a top 1 report which is grouped by the employment number, and sorted descending on the end date.

Within the group footer use a formula like:

whileprintingrecords;
shared numbervar Pension:= {table.pensionNo};

Now in the details section of the main report, in lieu of the {table.pensionNo}, use:

whileprintingrecords;
shared numbervar Pension;
Pension

-k

 

[FinnB]

Hi -k,
Many thanks for your reply -
I say many thanks for your suggestion.
Is their any chance to do this without a subreport??

FinnB

 

[lbass]

Are the new pension numbers part of a sequence so that they are always higher than the old pension numbers? If so, I think you could just group on {EmploymentNo} and replace the {PensionNo} in the detail field with a formula {@RevisedNo}:

maximum({PensionNo},{EmploymentNo})

-LB

 

[Ngolem]

I suppose the questions are....

1. How do you know the Pension number has changed?
2. Do you know what the person's previous number and new number are....if so you could enter as parameter's....makes life easy.
3. Is this a permanent change or just for the life of the report. (ie. sometimes for historical purposes you don't want to lose the old number...ie many many documents refer to the old number and they cannot be updated.) If this is not the problem doing maintenance on your data as SV suggested is the best bet. Jim Broadbent