conditional suppress 1

[AyJayEL]

Picture this;

Group on Child

Type Date
FIN 3/6/2000
PAA 2/5/2000
FIN 2/6/1999
PAA 1/4/1999

or next child
Type Date
PAA 16/10/2000

or next child
Type Date
PAA 12/12/2000
FIN 12/11/1999
PAA 10/10/1999
FIN 19/9/1998
PAA 16/9/1998

etc

I want to show only those records that have PAA as the latest record. PAA is when they are assessed and FIN is when it is finished. If I do a suppression like {Type} = 'FIN' I still get PAAs that have a FIN after them which I don't want. Learn something new every day *:->*

 

[kmcclung]

So what do you want to display? Ie.

Type Date
FIN 3/6/2000
PAA 2/5/2000
FIN 2/6/1999
PAA 1/4/1999


Do you just want the last PAA record?

 

[AyJayEL]

I just want the last PAA record as long as it doesn't have a FIN after it. I guess I want to say something like;-

If ({Type}) = 'FIN' don't show anything for that child but if there is no 'FIN' after the PAA show it. I'm just not sure of the syntax.
Learn something new every day *:->*

 

[kenhamady]

Is your first example 3 groups of records?
What are they grouped on?
Can you SHOW what your desirec output would look like for these records? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

I think I see what you want:

The results will be displayed in the Child Group footer

inside this put a group by Type

create an initialzation formula placed in Child group header and suppressed

@initialization
whilePrintingRecords;
DateVar array theDate := [date(1900,1,1),date(1900,1,1)];

now in the footer of the TYPE group put this formula

@Calc

whilePrintingRecords;
DateVar array theDate ;

if {Table.Type} = "FIN" then
theDate[1] := maximum({table.date},{Table.Type});
if {Table.Type} = "PAA" then
theDate[2] := maximum({table.date},{Table.Type});


now in the footer for Child place this display

@display_PAA

whilePrintingRecords;
DateVar array theDate ;
{table.Child} + space(10) + totext(thedate[2],"dd/MM/yyyy&quot

NOW ... in the conditional suppress for the Child footer put this formula

whilePrintingRecords;
DateVar array theDate ;

//this will suppress the footer anytime the maxFIN date
//is greater than the maxPAA date
theDate[1] > theDate[2]

SUPPRESS unconditionally Child group header, type header & footer and details

I think this will work

Jim

 

[AyJayEL]

Jim

Well I'm at home at the moment so I can't test it out but it looks perfect to me. Thank you so much for all the work you have put into it!

Andrea Learn something new every day *:->*

 

[AyJayEL]

Jim

I have tried this and when I run it I get the message 'The result of a formual can't be an array'.

Can I have done something wrong? Learn something new every day *:->*

 

[AyJayEL]

I am still stuck. Can anyone help me with this please? Learn something new every day *:->*

 

[kenhamady]

Is your first example 3 groups of records?
What are they grouped on?
Can you SHOW what your desirec output would look like for these records? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[AyJayEL]

Hi Ken, we meet again!

The group is by Child.

The data is like this:-

Jenny Jones
Type Date
PAA 12/3/2002

Michael Peters
Type Date
FIN 14/3/2002
PAA 10/3/2002

Rachel Davids
Type Date
PAA 16/3/2002
FIN 11/9/2001
PAA 10/10/2001
FIN 01/3/2001
PAA 01/2/2001

It would look like this:

Jenny Jones (Shows because she only has a Type PAA)
Type Date
PAA 12/3/2002

Michael Peters (wouldn't show because there is a Type FIN after the PAA date.

Rachel Davids (Shows because she has several rows with Type PAAs and Type FIN but the latest record is a PAA no FIN after it.)

Type Date
PAA 01/2/2002


I hope this now makes sense.
Learn something new every day *:->*

 

[Ngolem]

@initialization
whilePrintingRecords;
DateVar array theDate := [date(1900,1,1),date(1900,1,1)];
" ";


Sorry I missed that Just add a blank space to this formula and it will work...Since this is suppressed it won't change anything and will allow the formula to work

 

[AyJayEL]

Jim/Ken

Thank you both for your help!

It's taken me a bit further now! It stops on @calc saying 'A date is required here' and stops at the first maximum in the following

whilePrintingRecords;
DateVar array theDate;

If {SEN_AMEND.AMEND_TYPE} = "FIN" then
theDate[1]:= maximum({SEN_AMEND.AMEND_DATE},{SEN_AMEND.AMEND_TYPE});
If {SEN_AMEND.AMEND_TYPE} = "PAA" then
theDate[2]:=maximum({SEN_AMEND.AMEND_DATE},{SEN_AMEND.AMEND_TYPE});

Now it looks like a date to me so once again I am stuck. I'm feeling really thick here as I'm sure I should know what is wrong with this statement.

Andrea Learn something new every day *:->*

 

[Ngolem]

So...{Table.date} is not a date at all but a string??

Go to Insert|Database and highlight {Table.date}
Right-click and browse the data....does it say the data is a date, number or string??

If it is a string then modify these formulas as follows:

*********************************************

@initialization
whilePrintingRecords;
StringVar array theDate := ["",""];
" ";

*********************************************

@Calc

whilePrintingRecords;
StingVar array theDate ;

if {Table.Type} = "FIN" then
theDate[1] := maximum({table.date},{Table.Type});
if {Table.Type} = "PAA" then
theDate[2] := maximum({table.date},{Table.Type});

*********************************************

@display_PAA

whilePrintingRecords;
StringVar array theDate ;
{table.Child} + space(10) + thedate[2]

*********************************************

whilePrintingRecords;
StringVar array theDate ;

//this will suppress the footer anytime the maxFIN date
//is greater than the maxPAA date
theDate[1] > theDate[2]

If it is a number then change the formulas to NumberVar and initialize the array to zeros

This should work then...then again it won't since the format of the date doesn't lend itself to min/max determinations .... Should be yyyyMMdd for that to happen..hmmmm

Create one more formula to convert the date string to a better format...place this formula, suppressed in the detail section

@convertDate
//there is no "WhilePrintingRecords" in this formula since
//you would not be able to do the max on it

StringVar NewDate := "";
numberVar pos1;
numberVar pos2;

//find the position of the "/"
pos1 := InStr ({table.date},"/"
pos2 := InStr (pos1 + 1,{table.date},"/"

//puts the date now in the format yyyymmdd
NewDate := Newdate + right({table.date},4) +
mid({table.date},pos1 + 1,pos2 - pos1 + 1)+
left({table.date},pos1 - 1);

NewDate;

***************************
Now modify @calc as follows

@Calc

whilePrintingRecords;
StingVar array theDate ;

if {Table.Type} = "FIN" then
theDate[1] := maximum({@convertDate},{Table.Type});
if {Table.Type} = "PAA" then
theDate[2] := maximum({@convertDate},{Table.Type});

Now it might work

Jim

 

[AyJayEL]

Dear Jim

Thank you for your patience. You gave me the clue to work it out myself. The date field is actually a datetime field. So I went through and changed all the DateVar declarations to DateTimeVar and that has fixed it!



Andrea Learn something new every day *:->*

 

[Ngolem]

Glad this worked...it had to eventually