Highlight any record in data-set in which several fields within the record is duplicated 1

[tdrBates]

Using Crystal Reports, how can I highlight any record in a data-set in which several fields within the record is duplicated, but not all fields.

For example in the attached spreadsheet the fields listed below are duplicated, but not all fields within the record are duplicated.

If the fields below are duplicated within the entire data-set queried I want to highlight these records.

tDate, sched_time, start_time, end_time, bus, block

Please see attachment. Thanks for any assistance.

 

[lbass]

Go to the section expert->detail->color tab->x+2 and enter a formula with the following syntax:

(
If not onlastrecord and
{table.tdate}=next({table.tdate}) and
{table.sched_time}= next({table.sched_time}) and //etc.—add all other fields
Then cryellow else
crnocolor
) or
(
If not onfirstrecord and
{table.tdate}=previous({table.tdate}) and
{table.sched_time}=previous({table.tdate}) and//add other fields
Then cryellow else
Crnocolor
)

-LB

 

[tdrBates]

Thanks Ibass!

I went to the section expert->detail->color tab->x+2 and enter a formula with the following syntax:

I get the error "A Boolean Is Required Here". What am I missing here?

(
If not onlastrecord and
{command.transit_date_time}=next({command.transit_date_time}) and
{command.sched_time}= next({command.sched_time}) and
{command.start_time}= next({command.start_time}) and
{command.end_time}= next({command.end_time}) and
{command.vehicle_id}= next({command.vehicle_id}) and
{command.block_id}= next({command.block_id})
Then cryellow else
crnocolor
) or
(
If not onfirstrecord and
{command.transit_date_time}=previous({command.transit_date_time}) and
{command.sched_time}=previous({command.sched_time}) and
{command.start_time}= next({command.start_time}) and
{command.end_time}= next({command.end_time}) and
{command.vehicle_id}= next({command.vehicle_id}) and
{command.block_id}= next({command.block_id})
Then cryellow else
Crnocolor
)

 

[lbass]

Sorry-that should have been:

if
(
not onlastrecord and
{command.transit_date_time}=next({command.transit_date_time}) and
{command.sched_time}= next({command.sched_time}) and //etc.—add all other fields
) or
(
not onfirstrecord and
{command.transit_date_time}=previous({command.transit_date_time}) and
{command.sched_time}= previous({command.sched_time}) and //etc.—add all other fields
)
Then cryellow else
Crnocolor

-LB

 

[tdrBates]

Thanks Ibass!

The formula works now, but it does not highlight any records where duplicates are found.

 

[tdrBates]

The two records in the attached spreadsheet should be highlighted in Crystal Reports, but nothing is highlighted.

Can someone explain?

 

[lbass]

In order for my formula to work, the records need to be sorted by the six fields. Any groups would need to be on one of these fields--since grouping is the highest order type of sorting. If you cannot do this, then you would have to use variables to determine whether any of the values recur, but then you would not be able to highlight the first row in which they occurred.

-LB

 

[tdrBates]

Thanks Ibass!

I don't need to highlight the first row in which it occurs, just the rows afterward.

Since I cannot change the sort, how would I use variables to determine whether all the variables on any given row in the data-set recur.

Is it possible to flag, highlight, or identify records that duplicate certain fields within a given row?

 

[lbass]

What is the maximum number of records you would be evaluating? Or at least the maximum number of non-duplicated records? This would entail the use of arrays, and there is a limit of 1000 members to an array (in this case, a member would be the first instance of each record that is distinct based on the fields you identified.

-LB

 

[tdrBates]

Maximum number of records would be 5000 records

 

[tdrBates]

I could adjust the Date-Time range to query a Maximum of a 1000 records if necessary.

Thanks for your help on this!

 

[lbass]

Create a formula like this:

whileprintingrecords;
stringvar array dupe;
numbervar i;
if not (totext({table.number},0,"")+" "+{table.string} +" "+
totext({table.date},"MM/dd/yyyy") in dupe) then
(
i := i + 1;
(
if i < 1000 then (
redim preserve dupe;
dupe:= totext({table.number},0,"")+" "+{table.string} +" "+
totext({table.date},"MM/dd/yyyy")
)
)
);

Since I don't know the field types you need to adjust the fields in the formula as noted above.

Place this in both the report header AND in the detail_b section which you need to insert. Then suppress the detail_b section.

Then go to the section expert->detail_a->color tab and enter:

whileprintingrecords;
stringvar array dupe;

if totext({table.number},0,"")+" "+{table.string} +" "+
totext({table.date},"MM/dd/yyyy")in dupe then
cryellow else
crnocolor

These formulas will only work for up to 1000 unique values of the concatenated strings. Might be enough for your situation without changing the selection formula.

-LB

 

[tdrBates]

IBass this works great!!!

Thanks

tdr