Join multiple records into one feild 3

[bpeirson]

I am modifying an existing report using crystal. The report is used as a packing list for products we sell. In the course of manufacture we "attach" documents to the product in our data base. Some of the document numbers must be printed on the packing list below the part number.
Currently for every document associated with a part number a new entry is printed on the packing list like this:

[tt]
item qty Part
1 3 Thing one
document 1
2 3 Thing two
document 2
2 3 Thing two
document 3
[/tt]

What we want is:

[tt]
item qty Part
1 3 Thing one
document 1
2 3 Thing two
document 2, document 3
[/tt]

I have tried the following code.

dim MTRs as string
IF {OrderDet.ItemNo} = {OrderFiles.ItemNo} and {OrderFiles.DocType} = "MTR" then
    MTRs = MTRs  + {OrderFiles.DocNumber} + ", " 
End if
formula = MTRs

This will filter out documents which are not of the type "MTR" which is good. I do not know how to get the code to put all the records into one string. I know the code runs because it adds the comma to the end of each document number.

All help is appreciated.

 

[lbass]

What are we seeing in your example? Is the document in a detail_b section? Where is the doctype "MTR"? You should be able to group on {table.item} and then create three formulas (using Crystal syntax):

//{@reset} to be placed in the item group header:
whileprintingrecords;
stringvar doc;
if not inrepeatedgroupheader then
doc := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar doc;
if {table.doctype} = "MTR" then
doc := doc + {table.doc}+", ";

//{@display} to be placed in a group footer_b section:
whileprintingrecords;
stringvar doc;
left(doc,len(doc)-2)

Place the part number in GF_a. Drag the item and quantity into GF_a and suppress the group header and details section.

-LB

 

[bpeirson]

lbass, I suspected I was out of my league.

Answers to your questions:

The example is from the body of the printed packing list.

The item number, quantity and description are in detail a, the documents are in detail b.

The descriptions "Thing one" and "Thing two" are in a table called PLDet. I can only link PLDet to OrderDet, not directly to OrderFiles. The tables OrderDet and OrderFiles are linked by two fields, {ItemNo} and {JobNo}. OrderFiles contains the field {DocNumber} which I need to display and the field {DocType} which I use to filter the required documents (type "MTR" only).

What I think you are telling me to do:

1. Get rid of the code I wrote, it will filter but never concatenate the records.
2. Move all of my data fields into the Group Footer a (I will use Group Footer #1, I don't have a Group Footer a).
3. Suppress the unused sections.
4. Put in the code as supplied but modify to suit my table names. {@reset} clears previous documents, {@accum} adds new documents and {@display} removes the trailing comma.

What I don't know:

1. How do I

...group on {table.item}...

2. Do I place your formulae directly on the page in the noted sections?

Thanks for your help. I tried the Crystal help files but I didn't know what to search for in the first place.

Brad

 

[bpeirson]

lbass, I think I figured out the group sort using the group expert. I chose to group by the document type.

In the @reset formula I had to initialize doc with 2 spaces so when the left(doc,len(doc)-2) is invoked it doesn't return an error. Some items will have no documents so the string length becomes less than zero during display in those cases.

Next problem:

The first item "Thing one" on the packing list is suppressed but its document is displayed. I will try to figure this out but any help is appreciated. First I will try to regroup by the item number rather than the document type.



Brad

 

[bpeirson]

lbass, next update.

I had to put an IF LEN(doc)>2 then ... to trap strings which were too short.

The problems I reported earlier have been resolved. All were due to poor implementation on my part.

Final Problem:

Items which should appear on the packing list do not always appear. If an item has no documents attached to it the it does not appear.

An item which has a document of type "DWG" but none of type "MTR" will print as expected with no document numbers included.

An item with multiple document types will print with only the type "MTR" shown.

An item with no documents of any type will not print at all.

I will try to solve this one but I doubt I understand Crystal well enough to figure it out. All help is appreciated.

Brad

 

[lbass]

You need a left join FROM the table containing all items to the table containing the documents, with no selection criteria on the documents table. I meant for you to insert a group on your item field, not on document type. I'm not sure why you would do that. I think you would need a group #1 on a packing slip ID field, and then a group on Item#. The formulas should go in the items group header (reset), the details (accum), and the item group footer (display).

-LB

 

[bpeirson]

Thanks again.

I think it bears repeating that I'm out of my league.

Our front end allows us to automatically attach documents when parts are added to the database. I have enabled this function to attach a dummy document. I have also added this dummy document to all existing parts, the net result is that all items get printed. It's a "brute force" solution, as Isaac Asimov once wrote

"Violence is the last resort of the incompetent."

As I become more adept at using Crystal I will come back to this and try to implement the rest of your advice.