Unique values for Crystal output?

[smsherman]

Hello,

I am using Crystal 9 and I am simply at my wits end, any help or guidance is greatly appreciated.

I am trying to pull distinct values from a table as follows:

record, field1, field2, field3, field4, field5, field6, field7, field8, field9

I don't care what the record is- I just need all the possible distinct field values, with no duplicates. Straightforward, i know.

However, I can't figure out the solution when the value of field2 is = the value of field 1, or field3 is equal to field4 or field5.

Crystal keeps bringing in each individual field and I am unable to do a comparison.

example: (' ' = blank field value)
record1, A01, A02, A03, ' ', etc.
record2, B01, B02, A01, A03, ' ', ' ', etc.
record3, B01, A01, B02, ' ', ' ', ' ', etc.
record4, D01, A03, B01, A01, ' ', ' ', ' ' etc.

Crystal continues to pull the possible variations in, so I end up with duplicates. What am I missing?

Ideally, all I should see is ONE unique value, regardless of which field it is appearing in.

So the above should return A01, A02, A03, B01, B02, D01 as the final result.

Please let me know if you have any suggestions, or can point me towards any information source that might enlighten me. Thank you in advance.

 

[lbass]

The problem is that the values are in separate fields. Try using a command as your datasource where you use a union (NOT a union all):

select table.`field1`
from table
union
select table.`field2`
from table
union
select table.`field3`
from table
//etc.

This should give you distinct values.

I believe you are in the wrong forum. Try forum149 or forum767 in the future.

-LB

 

[synapsevampire]

This can be done by creating a formula and placing it in the report header:

whilereadingrecords;
stringvar array TheValues;
if not({SALES.ITEMNAME} in TheValues) then
(
redim preserve TheValues[ubound(TheValues)+1];
TheValues[ubound(TheValues)]:={SALES.ITEMNAME};
);
""

Now use the printing pass to display in the report header or whereever:

whileprintingrecords;
stringvar array TheValues;
join(TheValues,",")

-k