Crystal Puzzle

[atgrovecity]

Hello Crystal Experts,

Here is my puzzle:

I need to create a hospital report of client movements, but the report must only display the first record, that is the earliest movement date, of the client's latest Unit location. The records are sorted by client ID and movement date with movement date in descending order. Movement records are generated when the client switches Unit locations or when staying in the same Unit location due to bed changes or leave events etc. I need to figure how to code a Flag field to handle this. Below is a sample display of the data with the flag field set to 1 for the records I want to display and the flag field set to 0 for the records I want to suppress.

Client Unit Movement Date Flag
111222 G 03/11/2013 0
111222 G 03/10/2013 0
111222 G 02/25/2012 0
111222 G 07/14/2010 1
111222 A 02/08/2006 0
111222 A 02/07/2006 0
111222 H 01/02/2005 0
111222 G 02/01/2004 0

333142 F 01/22/2014 0
333142 F 01/21/2014 0
333142 F 10/23/2013 1

431222 H 11/28/2013 1

521333 I 04/07/2013 0
521333 I 03/14/2013 1
521333 H 11/27/2012 0

Appreciate any help on coding the Flag field.

Sincerely,

Joe

 

[FireGeek21]

I am assuming it is the "Detail" section you are wanting to suppress. To do this, click on Report -> Selection Expert. Make sure you click on Detail and then click on the formula box to the right of Suppress (don't check the Suppress check box). Now in the formula box, type {@Flag} = 0 or flag field name = 0. Now, "Save and Close".

Hope this helps!

FireGeek21

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[pmax9999]

FireGeek21, I think what the OP was looking for was how to code the Flag, not how to do the conditional suppression.

I approached it this way.

Create the following Formula called {@Test}

WhilePrintingRecords;
Global NumberVar DISP;

If      OnFirstRecord and
        {Table.Client} <> Next({Table.Client})
Then    DISP := 1
Else
If      OnFirstRecord and
        {Table.Client} = Next({Table.Client})
Then    DISP := 0
Else
If      {Table.Client} <> Previous({Table.Client}) and
        {Table.Client} <> Next({Table.Client})
Then    DISP := 1
Else
If      {Table.Client} <> Previous({Table.Client})
Then    DISP := 0
Else
If      {Table.Client} = Previous({Table.Client})
Then    If      OnLastRecord
        Then    DISP := DISP + 1
        Else
        If      {Table.Unit} <> Next({Table.Unit})
        Then    DISP := DISP + 1

This formula returns a number, where those with a 1 are the ones you want to see, so for the conditional suppression formula, use the following code:

{@Test} <> 1

I tested it using the sample data you provided and it did work. I would suggest you check the results against a larger sample to make sure I didn't miss a possible data combination that could cause the approach to fail.

Hope it helps.

Cheers
Pete

 

[atgrovecity]

Thanks Pete.
Your code looks like it will work.

To understand your code I have a question. Is the OnFirstRecord and OnLastRecord being executed for each client group? The Crystal Help file says

OnFirstRecord
Returns TRUE when the current record being evaluated is the first record in the report.
OnLastRecord
Returns TRUE when the current record being evaluated is the last record in the report.

Joe

 

[pmax9999]

Hi Joe

I didn't realise you were using any groups but a group on {Table.Client} won't impact the report as the code is testing the next and previous {Table.Client} record, and the only time this can fail is on the first and last report record - on change of group the next and previous test will still work.

The best way to satisfy your concern is to test the code. One thing I maybe didn't make clear enough is that the result of the formula can be numbers other than 0 or 1 - higher numbers will occur where there are more than 1 previous units. However the records where the formula result is 1 are the ones you want to see (assuming of course I understood what you are trying to do).
Cheers

Pete