Problem with StringVar formulas

[MJRBIM]

Crystal Reports 10

First and Last names are part of different lines of detail, and I'm trying to use three StringVar formulas to create a "FirstName LastName" field in the Group Footer where I group on CUSTOMER_ID.

Sample Data and Formulas are below....

CUSTOMER_ID,FIELD_ID,INPUT_VALUE
1,1,JOHN
1,2,SMITH
1,3,416-123-4567
1,4,TORONTO
1,5,ON
2,1,BOB
2,2,JONES
2,3,613-123-4567
2,4,OTTAWA
2,5,ON

GROUP HEADER
============
Name: {@RESET NAME VAR}
Formula: Global stringVar NAME := "";

DETAIL
======
Name: {@ADD NAME VAR}
Formula: Global stringVar NAME;
IF {CustomerInfo_txt.FIELD_ID} in [1,2] THEN stringVar NAME := NAME + {CustomerInfo_txt.INPUT_VALUE} + " " ELSE stringVar NAME := NAME;

GROUP FOOTER
=============
Name: {@DISPLAY NAME VAR}
Formula: Global stringVar NAME := NAME;

When I run the report, the StringVar is NOT RESETTING the variable in the Group Footer, so the string just keeps growing with each name.

CUSTOMER_ FIELD_ INPUT_VALUE
1
1 1 JOHN JOHN
1 2 SMITH JOHN SMITH
1 3 416-123-4567 JOHN SMITH
1 4 TORONTO JOHN SMITH
1 5 ON JOHN SMITH

2
2 1 BOB JOHN SMITH BOB
2 2 JONES JOHN SMITH BOB JONES
2 3 613-123-4567 JOHN SMITH BOB JONES
2 4 OTTAWA JOHN SMITH BOB JONES
2 5 ON JOHN SMITH BOB JONES

I'm pretty sure the issue is with the way I am declaring the StringVar. Anyone have an idea of what I am doing wrong..?

Thanks in advance for the advice.

 

[dpatrickcollins]

MJR,

Combining global variables with grouping has in my experience been a little dicey due to the way Crystal's "pass" technology works. Let me suggest a way I implemented recently with an identical data structure. The following assumes first and last name are required fields and present:

1 ) Include two more instances of the CUSTOMER table in your report. Name the first FIRSTNAME and the second LASTNAME. Join them to the existing CUSTOMER table (or, alternately, to the table CUSTOMER is presently joined to.)

2) In Select Expert, Make FIRSTNAME.FIELD = 1 and LASTNAME.FIELD = 2

3) Now Group by Customer # (as before), and concatenate FIRSTNAME.INPUT_VALUE with LASTNAME.INPUT_VALUE in group formula.

Done.

The rationale for this solution is that you are largely dealing with a data structure issue. Solving it at the data level is consistent with the issue.

 

[synapsevampire]

Global variables have nothing to do with the issue, and you don't need to state global for global variables, that's the default, although dpat has another interesting approach, I would suggest doing something similar but in a View and just use a UNION Query.

But it's simple enough your way as well, let's simplify and be more specific:

GROUP HEADER
whileprintingrecords;
stringVar First := "";
stringVar Last := "";


DETAIL
whileprintingrecords;
stringVar First;
stringVar Last;
IF {CustomerInfo_txt.FIELD_ID} = 1 THEN
First := {CustomerInfo_txt.INPUT_VALUE}
else
IF {CustomerInfo_txt.FIELD_ID} = 2 THEN
Last := {CustomerInfo_txt.INPUT_VALUE}

GROUP FOOTER
whileprintingrecords;
stringVar First;
stringVar Last;
First +" "+Last

-k

 

[lbass]

I think you just needed to preface your formulas with "whileprintingrecords;" for them to work as is.

-LB

 

[synapsevampire]

Agreed, LB, unless the order of the first/last differed.

-k

 

[MJRBIM]

whileprintingrecords;" solved it - THANKS!

Some of the other solutions look good to, I will play around with them later.

Thanks to everyone.