How to get the second lowest value

[c8ltgkue]

Using CR XI

I work for a law firm and I would like to find out the second allocated solicitor for the case. for example.

CaseID Allocation date Solicitor ID
Case 1 01/11/2013 1111
case 1 13/11/2013 1112
case 1 24/11/2013 1113
case 1 30/11/2013 1114

so if I create a group on CaseID I would like to see 1112 in solicitor ID. Sorting by ascending or descending gives me 1111 or 1114 respectively. I would like to see the second allocated solicitor i.e ID 1112.

Help..!

 

[pmax9999]

One way would be to create a Running Total ({#Count}) to count the CaseID, Evaluate on every record and Reset on change of group.

Then do a conditional suppression on the Details Section where:

{#Count} <> 2

One issue to consider though, is how you want it handled if there is only 1 Solicitor for the case. With my approach. it would show nothing.

Hope this helps

Cheers
Pete

 

[BettyJ]

You can try creating a formula which generates the count of records. Place it in details
@countrec
whileprintingrecords;
numbervar countrec:=countrec+1;
countrec;

Place the following formula to reset the countrec at every group in groupheader.
@resetcountrec
numbervar countrec:=0;

Suppress condition for details
{@countrec}<>2

Hope this helps

 

[pmax9999]

Forgot to mention that this approach requires that records be sorted on Allocation Date, in ascending order.

Pete

 

[BettyJ]

Sorry, pmax. I must have been typing when you posted the solution. Didn't see your reply.

 

[pmax9999]

No need to apologise BettyJ, I'm sure all suggestions and assistance will be welcomed by c8ltgkue. I didn't see your response either before clarifying my post with the comment about sort order.

Cheers
Pete

 

[BettyJ]

I have used Running Total some time back, but sort of forgot about it and was using formulas. It is much easier. From now on, I will be using that for generating counts.

Thank you Pete.