Compiling non-duplicate data into one field

[Cardstang]

I'm sure that's an unclear Subject, and I apologize if it's confusing...not sure how else to word it.

What I'm looking to do is compile all the zip codes in a City into one field.

My raw data looks like this:

Ticket# City ZIP
123456 Atlanta 30511
234567 Atlanta 30512
456779 Atlanta 30512
457895 Atlanta 30511
345678 Atlanta 30645
456789 Atlanta 31254
987654 Portland 80545
741258 Portland 80547
876543 Portland 80457
548789 New York 01567


And what I need is a summary that does this:


Atlanta 30511, 30512, 30654, 30645
Portland 80545, 80457
New York 01567

I'm using CR12 and pulling from a Remedy data source.

Thanks!

 

[fisheromacse]

i think something like this:

\\{@compiled} <---place in details
stringvar com;
IF onfirstrecord then com := {table.zip} else
IF {table.city}=previous(table.city} then com := com & ", " & {table.zip} else com

\\{@compliedREST} <---place in group header (group on city)
stringvar com := ""

\\{@compiledDISPLAY} <---place in group footer (group on city)
stringvar com;
com


i do not have crystal in front of me so apologize for any errors in syntax or logic and the always possible mental lapses on my part.

 

[fisheromacse]

oops, i should have added some more logic to account for the potential duplicates.

changes to {@compiled}

stringvar com;
IF onfirstrecord then com := {table.zip} else
IF {table.city}=previous(table.city}
AND {table.zip} NOT INSTR(com)
then com := com & ", " & {table.zip} else com


also, i usually add 'whileprintingrecords' as the first line to each of the formulas

 

[fisheromacse]

ok, i need to think then type. sorry!
the line i added above in bold should be:

AND NOT ({table.zip} IN com)

 

[Cardstang]

Hey thanks for helping me out!

It's not liking the If/Then statement.

It's telling me I need a Then statement and proceeding to highlight:

IF {table.city}= previous [highlight #729FCF]{table.city}
[/highlight]

 

[fisheromacse]

it looks like i left out a couple characters, and be sure to replace the {table.XXX} with the actual tables/fields from your database.

try:
IF {table.city}=previous({table.city})


Time to make myself some coffee!

 

[Cardstang]

I think we're getting closer...

Now the "NOT" is getting lit up and telling me I need a Then statement.

 

[fisheromacse]

i probably have a ( or a ) missing or out of place but am not seeing it.
i added a set of () below to isolate some of the logic, but otherwise, i don't think i made any real changes.

stringvar com;

IF onfirstrecord=TRUE
then com := {table.zip}
else
(
IF
(
{table.city} = previous({table.city})
AND
NOT({table.zip} IN com)
)
then com := com & ", " & {table.zip}
else com
)

 

[pmax9999]

The complexity of the solution will depend on a couple of factors. For example, in the sample you provided, the data appears to be grouped by (but not sorted by) City but there is no sort/group on ZIP. The simplest solution would involve grouping on city and ZIP code, because it simplifies the whole appropach to dealing with duplicates. The most complex solution would arise if the report can not be grouped and there is no sort order .

Assuming there is nothing that would preclude the use of groups on city and ZIP, create the following 2 formulas:

Place in City group:

WhilePrintingRecords;
Global StringVar DATA := GroupName (Table.City})

Place in ZIP group:

WhilePrintingRecords;
Global StringVar DATA;
DATA := DATA + ' ' + GroupName({Table.ZIP}, '#') + ', '

Suppress Details, all Group Headers and Group Footer 2, and place the following formula in Group Footer 1 (City):

WhilePrintingRecords;
Global StringVar DATA;
DATA := Left(DATA, Length(DATA)-2)

If there is some reason why you can't add the suggested grouping, let us know. We could work around it but we would need to understand the report structure.

Hope this helps

Cheers
Pete