Parent and child records on the same line...

[JCruz063]

Hi all!
I am using Crystal Reports 10.

I have the following database tables

Item
ItemID
Description

ItemProperty
ItemID
PropertyCodeID

PropertyCode
PropertyCodeID
PropertyNameID
Code
Description

ProprtyName
PropertyNameID
Description

The Setup
The main table is Item, which stores certain items in my database. Now, each item has a varying number of properties, and instead of creating about 20 fields right inside the item table, I decided to store item properites in a separate table, called ItemProperties. So data about items is stored in the ItemProperties table.

So far so good. Now, the value of each property is a code, which the user creates to associate a description. Thus, when entering age data, for instance, the user would enter code 1, which means 0-10 years old, code 2, which means 11-20 years old, and so on. Thus, in the PropertyCode table, I simply store all the codes the user has created. This is a typical entry in the tables:
[tt]
Item
ID: Description:
1 My dog

ItemProperty
ItemID PropertyCodeID
1 1
1 2
1 3

PropertyCode
PropertyCodeID PropertyNameID Code Description
1 1 1 Spike
2 2 1 Black
3 3 1 0-11 years old

PropertyName
PropertyNameID Description
1 DogName
2 DogColor
3 DogAge
[/tt]

Ok, so from the above, you can see that I have an item which is a dog; his name is spike; he's black, and his age is 0-11 years.

The Problem
I have a Crystal Report and I want it to display each item, with all its properties. The report should show a title banner display the property names which should serve as column heading. Then, each item should be displayed... one row per item. At the moment, however, I get three rows for every item (one row per every property of the item). How can I display one item per row?

Right now, I have formulas which return property code descriptions sitting right on the Details section of my report. But, like I said, for every item, I'm getting multiple rows (one row, for every property). Can anyone please shed some light?

Thanks!

JC

Can anyone please tell me how to get around this problem?

Thanks

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[MJRBIM]

Please provide details of the exact output you want in a line...

My dog - Spike, Black, 0-11 years old

or something else?

 

[JCruz063]

Thanks for your reply MJRBIM.

You're right on the money. That's exaclty what I want on each line, but right now I'm getting this:
[tt]
My Dog Spike [nothing] [nothing]
My Dog [nothing] Black [nothing]
My Dog [nothing] [nothing] 0-11 yearls old[/tt]

Thanks again!

JC


_________________________________________________
To get the best response to a question, read faq222-2244.

 

[MJRBIM]

GROUP on the ItemID - then put the following VARIABLE formulas in the correct sections.

(GROUP HEADER)

NEW STRING
Global stringVar Description := "" ;


(DETAIL)

ADD DESCRIPTION TO STRING
Global stringVar Description := Description + {PropertyCode_txt.Description} + ", ";

(GROUP FOOTER)

DISPLAY STRING
WhilePrintingRecords;
Global stringVar Description;

You will then want to supress your GROUP HEADER and DETAIL sections.

 

[JCruz063]

Thanks again MJRBIM!

Perhaps I misunderstood you. I didn't mean to say to print a comma-separated string. The report should print in a tabular manner, having the names of the properties as column headers. Like this:
[tt]
Item Name Color Age
My Dog Spike White 0-11 years old
My Dog 2 Brownie Black 0-11 years old
My Dog 3 Whatever Brown 0-11 yearls old[/tt]

This is what I did:

(GROUP HEADER)

NEW STRINGS
Global stringVar Name := "";
Global stringVar Color := "";
Global stringVar Age := "";

(DETAIL)

Here, I have 3 formulas that get the description from PropertyCode if the PropertyNameID is the appropriate one.
For example, this is the formula for DogName
Global stringVar Name;
if Len(Name) = 0
if (PropertyName.PropertyNameID} = 1 then
Name = {PropertyCode.Description}

The other two formulas are just like this one, except that they check a different PropertyNameID and they set a different variable.

(GROUP FOOTER)

DISPLAY STRINGS
WhilePrintingRecords;
Global stringVar Name;
Global stringVar Color;
Global stringVar Age;

This is not displaying the correct data, however. A lot of data appears to be missing. For every row, I get the name, but the color and the age are missing in some rows (many rows). What am I doing wrong?

Thanks!

JC




_________________________________________________
To get the best response to a question, read faq222-2244.

 

[adhhealth]

did you group by propertycode.code??
try putting your details in this section