Unique value without using UNION?

[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.

I am unable to use the SQL UNION command (or any SQL command due to a secondary application that limits my options). Is there a functional equivilent within Crystal to achieve the same result as an SQL UNION command?

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]

So what is this secondary application? You should have stayed in the same thread instead of starting a new one by the way. I think you could accomplish this with a formula like this:

whileprintingrecords;
stringvar array x := [{table.field1},{table.field2},{table.field3},{table.field4},{table.field5},{table.field6},{table.field7},{table.field8},{table.field9}];
stringvar y;
numbervar i := 0;
numbervar j := ubound(x);

for i := 1 to j do(
if instr(y,x) = 0 then
y := y + x+", "
);

Place the above formula in the detail section, and then in the report footer, add this formula:

whileprintingrecords;
stringvar y;

Format the formula to "can grow" so all results show.

-LB