Collapsing duplicate rows - New to CR please help. Thx. 1

[baycolor]

Hi here is a former post that I got a response to. Having other troubles now...

******************** ORIGINAL PROBLEM ***************
I'm using CR V10. Here is the issue...

Output wanted on my report:
ID Solution CustContacts OurContacts
1 CRM, EDW, SCM A, B C, D, E, F, G, H
2 ZBC, XYZ, DEF, KLM I J

Source Data:
Table 1
ID
1
2

Table 2
ID Solution
1 CRM
1 EDW
1 SCM
2 ABC
2 XYZ
2 DEF
2 KLM

Table 3
ID CustomerContacts OurContacts
1 A
1 B
1 C
1 D
1 E
1 F
1 G
1 H
2 I
2 J

As you can see I can join the tables by ID (NOTE: I must use Table 1 as first table) which is going to create a bunch of rows. Table 1 ID 1 joins to 3 rows in Table 2 which then will join to 8 rows in Table 3. Ultimateley creating 24 rows with ID 1.

My output can only have one row per ID. But for the last three output columns I want all the data row values for Solution, CustomerContacts and OurContacts (for one ID) to be concatenated together into one field on the one row I display.

It is never known how many Solutions will be associated with one ID.
Neither is it known how many CustomerContacts or OurContacts will be associated with one ID.
Neither is it known how many unique IDs from Table 1 will be waiting to be processed into the report.
Neither is there a max number of Solutions per ID.
Neither is there a max number of CustomerContacts or OurContacts per ID.

Somekind of looping with concatenation needs to be done probably using WhilePrinting, Previous or Next etc... but I'm not having any luck. And I'm not sure if I'm totally going off in the wrong direction with what I'm doing.

Does anyone have any code that can handle this (please note that I'm totally new to CR and everything seems to be a struggle so please be as specific as possible with any explanations and syntax).
******************** END ORIGINAL PROBLEM ***************

******************** SOLUTION PROVIDED ***************

You will need basically 3 formulas each for Solutions, CustContact, and OurContacts

Group by ID

In the group header, place the following formula and suppress it.

CODE
WhilePrintingRecords;
global stringvar Sol := "";

In the detail section, place the following formula and suppress it.

CODE
WhilePrintingRecords;
global stringvar Sol;
sol := Sol + {table.solution};

Place the following formula in the group footer to display the results

CODE
WhilePrintingRecords;
global stringvar sol;

sol
******************** END SOLUTION PROVIDED ***************

The above worked and creates a field with all the individual data elements concatenated together on the one row per ID, however, because of duplicate rows it also concatenated the same value multiple times. I modified the second code piece that does the concatenating to:

CODE
WhilePrintingRecords;
global stringvar Sol;

// Check to see if string 2 found in string 1 if so don't concatenate string 2
If InStr(Sol, {<table>.Sol}) = 0 then
Dol:= Sol + "," + {<table>.Sol};

************* ISSUES I'M HAVING NOW **************
The group by ID is forcing the report to sort by ID first. Ultimately in my output I will have some other fields that I would like to sort by first. First by customer_name and then by solution_provided_date (or vice versa not sure yet). I can add customer_name and solution_provided_date via Record Sort Expert but the ID sort is always first and can't be overidden or pushed down in the sort order.

Is there a way around this?

NOTE: I'm very new to CR so please be detailed (especially with coding) on any explanations.

Thanks in advance for your help.

 

[lbass]

If you insert a group on customer name and then on your date field, you can then go to report->change group expert and use the direction key to make them higher order groups, with the ID group being the last. This will force the sorting that you want. You don't have to display the group headers and footers for these groups. Right click on the gray area to the left of the canvas and select suppress for those sections you don't want displayed. This of course means that the ID group will be nested within the name and date groups.

-LB