Technical Questions: duplicating fields

Print Page | Close Window

duplicating fields

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14775
Printed Date: 07 May 2024 at 7:10pm

Topic: duplicating fields

Posted By: swilleyk
Subject: duplicating fields
Date Posted: 25 Oct 2011 at 8:43am

I am working off of an application for university where students apply online and then the information is sent to a datatel database on sql server 2008. The problem I am getting is when I am trying to display some of the information is that there are fields like honors that the applicant can enter multiple fields into. When I am trying to display this information cyrstal creates a new report every time there is a new honors field.

Does anyone know of a way to get the field to display on one section. The name of the field is {Datatel_honorExtensionBase.Datatel_name}, and I can not see where it is assigning a different name for each instance of the honors. It attaches the honors to an application id, then distinguishes each field by a key value.

Sounds very confusing, I know, but it has been driving my mad for the past two days trying to see how to get this field to display all at once instead of creating a new instance every time.
Thanks
Kevin

Replies:

Posted By: DBlank
Date Posted: 26 Oct 2011 at 3:46am

i think when you say a 'it displays a new report' you mean it shows duplicates rows for the student. This is likely happening becuase your 'honors' table stores 1 row per honor per student. When you join that to the student table it will create as many 'student' rows as there honors for that student (conversely unless you outer join the tables it will omit students that have no honor records).

usually people handle this by grouping on the primary key of the student record (student id) and hiding the details. If you are trying to show all of the honors in 1 row you will need to use shared variable formulas to read the rows and create one string that can be displayed in the group footer.

Posted By: swilleyk
Date Posted: 27 Oct 2011 at 3:40am

I am not sure if I understand using the shared variable.

I am a semi newbie to this so I am trying to remember stuff I haven't done in a couple of years.

Basically I need to figure out how to loop through the record and pull out the information to display per person.

So if one person only has one honor, but the next has four, I need to get it to print with the person and not produce a second or third report to display the next honor.

Thanks

Posted By: DBlank
Date Posted: 27 Oct 2011 at 4:11am

there are a ton of examples of concantenating rows of data into a single string uising formulas wiwh shared stringvar.

here is one link

http://crystalreportsbook.com/Forum/forum_posts.asp?TID=14032 - http://crystalreportsbook.com/Forum/forum_posts.asp?TID=14032

Posted By: lockwelle
Date Posted: 27 Oct 2011 at 4:17am

As DBlank mention, if you are grouping on the application id(let's say).

The honors are tied to that value, which is why I am using it, then your data would like:

id1, honor1

id2, honor1

id2, honor2

etc.

since you don't want to see the multiple rows, you can create a formula like:

shared stringvar honors := honors +", "+ {table.honor};

place this in the detail section and suppress(hide) the section. Now the formula will accumulate all the honors associated with the id.

In the group header you would want a formula like:

shared stringvar honors := ""

so that you are only gathering the honors of the id

In the group footer you would want something like:

shared stringvar honors;

honors

this will display the resulting string (if there is one)

All of this is what DBlank was meant when he said: "usually people handle this by grouping on the primary key of the student record (student id) and hiding the details. If you are trying to show all of the honors in 1 row you will need to use shared variable formulas to read the rows and create one string that can be displayed in the group footer."

As for the left join, I agree, that it is the wise thing to do for the reason mentioned.

HTH

Posted By: swilleyk
Date Posted: 28 Oct 2011 at 2:55am

Thanks for the replies - I your suggestion and it worked. The problem I am having now is that there are multiple fields that I am using these formulas for and is is now duplicating the names in each display.

Extracurricular Activities and Interests , Key Club ‑ President, Key Club ‑

President, FBLA, FBLA, Volleyball, Volleyball

Honors , Capital District Key Club Distinguished Officer, My Neighbor's Foundation Award, Capital District Key Club Distinguished Officer, My Neighbor's Foundation Award, Capital District Key Club Distinguished Officer, My Neighbor's Foundation Award

Is there a way to keep these fields from doing this?

Thanks for all the help so far.

Posted By: lockwelle
Date Posted: 28 Oct 2011 at 11:39am

create a different shared variable for each field. Increment the field as appropriate. So while you might increment all the variables in 1 formula (and reset them in 1) you would have multiple lines for the increment, and multiple formulas for display (one per variable).

HTH

Posted By: swilleyk
Date Posted: 01 Nov 2011 at 3:03am

Ok this is what I have
(Reset)
whileprintingrecords;
stringvar x;
stringvar y;
stringvar z;
if not inrepeatedgroupheader then
x := "";
y := "";
z := "";
(Accum)
whileprintingrecords;
stringvar x := x + {Datatel_honorExtensionBase.Datatel_name} + ", ";
stringvar y := y + {Datatel_workexperienceExtensionBase.Datatel_name} + ", ";
stringvar z := z + {@curricname} + ", ";

I have seperate displays for each one.

Still getting the repeated displays for the honors, work experience and extra curric.

Posted By: lockwelle
Date Posted: 01 Nov 2011 at 10:44am

sorry misunderstood, thought that there were dupes between the different headings. So, just looking at extracurricular, there are dupes, one would think that the most likely reason for this is that in a join, there is a result of 2 records...so how to get around it?

what i would try is:

in the reset, set the value to ", " instead of "". Then in the accum part add/change:

if instr(x, ", " + {table.field} + ", ") = 0 then

x := x + {table.field} + ", ";

this will only allow 1 occurrance of any selection to be in your result string.

HTH

Posted By: swilleyk
Date Posted: 02 Nov 2011 at 6:12am

I keep getting an error
"The ) is missing" and points to the first x.

Thanks
Kevin

Posted By: DBlank
Date Posted: 02 Nov 2011 at 6:35am

you have to assign x, although I think you also might need to simplify this and not worry about checking for the comma portion

stringvar x;

x := x + (if instr(x,{table.field})=0 then {table.field} + ", " else "");

Posted By: swilleyk
Date Posted: 02 Nov 2011 at 7:27am

that worked like a charm
You guys ROCK

Thank you very much
Kevin

Print Page | Close Window