Customizing: combining records 3

[jefRPT]

I'm generating a report that lists a product number in one column and the history log of where it is in another column. My problem is, when a product has more than one entry in the history column, it generates a whole new line for it and the only new info would be the history column while all of the other columns repeat itself. I need to know how to combine all if the history records for each product number into one so there won't be a repetion of the other columns. Please help!
FOR EXAMPLE:
Right now its like:
ITEM #: HISTORY LOG:
1 abc
1 def
2 ghi

I want it to be like:
ITEM #: HISTORY LOG:
1 abc,def
2 ghi

 

[MikeTom]

layout the firlds in the details section but for the history log create a sub report which pulls the history logs data values.
i.e you will have one record in your main report for each item but the subreport will pull through all the history log details for that item.

 

[MikeTom]

To format the data in a line as above in the subreport you will have to set the details section to 'Format with Multiple colums' and create a fiels Say @history which = {History_Log} + ","

 

[dgillz]

MikeTom's answer will give you this:

ITEM #: HISTORY LOG:
1 abc
def
2 ghi

Very little change with what you have now, just one less '1' in the ITEM# column.

To get what you have asked for, you will need to declare a string variable that concatenates each value of the next history log field to itself, and resets upon change of Item#. I put an hour into this but I could not make it work, undoubtedly because I am still pretty new to variables.

Let me know what your ultimate solution is.

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[MikeTom]

The solution works it gives you the results all you need to do is format the subreport to get them to display how you want to. that is what my second post is about.

 

[jefRPT]

What dgillz said sounds exactly like what I need to do. But I'm new to this as well (only 2 wks)and have no idea how to make that happen. So if someone can help me out with this, I'd really appreciate it. Thanks in advance to everyone.

 

[dgillz]

Mike-

Sorry I was composing my post before I saw your 2nd post.

I have never tried the format with multiple columns option before as it is new to V8, as am I. However, I just tried it and my output of item histopry is still vertical:

abc
def
ghi

as opposed to horizontal as Jef wants: abc,def,ghi.

What am I doing wrong? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[MikeTom]

i am using this function in version 7.
if you preview the subreport is the results in column or row form?
if the answer is column. I would think the size of the subreport in the main report only allows for one column.

if answer is Row. i would look at the format of the details section on the subreport and confirm that you have the format with multiple columns ticked.

I cant think of any other reason for it not working unless it has changed for version 8 as i use 7.

 

[jefRPT]

can someone show me how to do this the way dgillz said.
I really don't know where to start at all, and I need to find a solution soon. X-)

>>To get what you have asked for, you will need to declare >>a string variable that concatenates each value of the >>next history log field to itself, and resets upon change >>of Item#. I put an hour into this but I could not make >>it work, undoubtedly because I am still pretty new to >>variables.

 

[Nuffsaid]

Dgillz,

The CD that came with Peck's book contains a sample report for parameters. This report has a formula in it that accumulates regions in the format required. Here's the formula;

// Shows the regions chosen in the multi-value ?Region parameter field

// Uses a For loop to cycle through all the elements of the parameter field
// and accumulate them in a variable.

NumberVar Counter;
StringVar Message := "Regions Chosen: ";

// cycle through all members of the multi-value ?Region parameter field
For Counter := 1 to Count({?Region}) Step 1 Do
(
// accumulate the values in the Message variable, along with comma/space
Message := Message & {?Region}[Counter] + ", "
);

// strip off last comma/space added by the loop
Left(Message, Length(Message) - 2)

I'll bet with a little work, modifacations could be made to obtain the desired results. I just haven't got the time to play wit it right now.

Nuffsaid.

 

[mmaz]

Here's a solution that I tested in CR v.8:

1- Group your Item field
2- Create the following formula which will build the history log string and drop it in the details section:

whileprintingrecords;

StringVar myValues;

//for the first record, assign a value
if RecordNumber = 1 then
myValues := {tableNm.History_Log}

else
//not the first record. if this record's item and the next record's item are the same, then concatenate history logs.
(if {tableNm.Item} = Previous ({tableNm.Item})then
myValues := myValues + ", " + {tableNm.History_Log}
else
myValues := {tableNm.History_Log});


myValues

You should now see:

ITEM #: HISTORY LOG:
1 abc
1 abc,def
2 ghi

3- To get rid of the extra records, do the following. From the Format menu, select Section. A window will open, select the Details section, check the "Suppress" checkbox, and click on the formula button. Then enter the following code:
{tableNm.Item} = Next ({tableNm.Item})

 

[jefRPT]

mmaz,
Thanks a lot. That really helped me out. But there is still one problem; I've been staring at the code all morning and still can't figure it out. Before I put your formula in, I was pulling back a total of 15 unique items, now I'm only pulling back 6. I have no idea why, the code seems perfect but its skipping/dropping a few of the items. Any suggestions? I tried it out on a different report first. It basically is the same, except instead of "product history", it has a list of all the sites it's been to. Here's how my formula looks:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
whileprintingrecords;
StringVar myValues;
if RecordNumber=1 then
myValues := {SITES.SITE_NAME}
else
(if {PROTOCOL.IACUC_NO} = Previous ({PROTOCOL.IACUC_NO})then
myValues := myValues + ", " + {SITES.SITE_NAME}
else
myValues := {SITES.SITE_NAME});

myValues
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks again.

 

[mmaz]

I think it's because the history log of the last item is getting suppressed, but I don't know yet how to fix it. I'll get back to you on that.

 

[mmaz]

Ok, I think I got it.

When you conditionnally suppress the Details section, instead of writing:
{tableNm.Item} = Next ({tableNm.Item})

Write:
not OnLastRecord and {tableNm.Item} = Next ({tableNm.Item})

"not OnLastRecord" must be the first condition otherwise it won't work!

Happy programming!

Marie

 

[jefRPT]

Marie,
Thanks for all your help. It still only pulls back 6 instead of 15. I probably just screwed something up on my end. I'll look at it a few more times, just don't know what else it could be. Thanks again.

Jeff s-)