How to Concatenate field on the many side of a relationship

[Bennie47250]

Using Crystal 7.0

Have a classic relation DB setup. The 100 records has “header” information (PO #, order date, shipping instructions…), the 200 records has detailed order information. (qty ordered, description….)

Would like to be able to produce a report that has the tables joined but only one line per order number. Right now my report will have 3 lines of data if the order has 3 items on it. All the information is identical except for the qty ordered and description

Example:
PO# Order Date Qty Description
1 6/14/2002 3 abc
1 6/14/2002 5 efg
1 6/14/2002 6 xyz

Would like to see something like this:

PO# Order Date Qty and Description
1 6/14/2002 3 abc, 5 efg, 6 xyz

I’m designing the report to export to Excel so this needs to be taken into consideration.

Thanks in advance

Bennie

 

[Naith]

Bennie,

Can you not use a cross tab for this?

Naith

 

[Bennie47250]

Admittedly I don’t know that much about cross tabs, but I been playing with it and I can’t seem to get the layout I’m looking for. Other suggestions?

 

[Naith]

I still reckon than you might want to swot up on cross tabs, because if I was trying to do what it looks like you are, then x-tabs would be my man.

I'll show you how to do this with formulas in a standard report - but, I personally can't stand this approach, because you have to put in an unknown amount of handlers in order to get out of the 254 string limit trap.

Okay then, Bennie...here we go.

//@F1 --> Details Section
// I'm working with variables called text just to keep track
// of what I'm doing. Your QTY field is probably numerical
// but you'll have to totext it in the formula so you can
// concatenate it.

whileprintingrecords;
stringvar text;
stringvar text2;
stringvar text3;

if length(text) < 225
then
text := text + &quot;, &quot;+totext({orders.order id},0) + &quot; &quot; + totext({orders.order amount})
else
if length(text2) < 225
then
if length(text) >= 225
then text2 := text
else
text := text + &quot;, &quot;+totext({orders.order id},0) + &quot; &quot; + totext({orders.order amount})
else text3 := text2;

//@F2 --> Details Section to the right of @F1

whileprintingrecords;
stringvar text;
stringvar text2;

if length(text) >= 225
then text := '';

if length(text2) >= 225
then text2 := '';


//@F3 --> Group Header

whileprintingrecords;
stringvar text := '';
stringvar text2 := '';
stringvar text3 := '';

//@F4 --> Group Footer

whileprintingrecords;
stringvar text;
stringvar text2;
stringvar text3;

replace(text,', ','',1,2)+ &quot; &quot; + text2 + &quot; &quot; + text3;

That's your lot. Having the three string variables in this instance allows for a concatenated string of 762. If you're likely to break through that length, then just add variables to compensate - or, more simply, put in an error handler which returns &quot;String Too Long&quot; or something.

Naith

 

[Naith]

I noticed that you're doing this for an excel export. Excel will consider this concatenated field to be one field.

If you want the output of the field to be broken up, you'll have to export it as a csv, and open it in excel. You might want to make sure your numerical format doesn't have thousand separators in it.

Naith

 

[Bennie47250]

Naith, Thanks for your time and suggestion. Perhaps I should have stated earlier that in the 200 (detail) records; we don't have a way of knowing how many records there may be. A customer could order 1, 10 or 50 items. Using the formula you provided, I suppose I could program for the worst case scenario. When I attempted to do this using cross tabs, I was getting “stacked up” column headings and totals that were not needed. I moved the fields around but could not get the desired results.

Thoughts?

Thanks
Bennie