Evaluate and Suppress Duplicate Rows 2

[redtoad]

I have a report that returns a number of rows based. The report gets the data from a returned sql recordset. I need a formula that will evaluate each row and suppress any duplicate rows. The evaluation will have to take into account that some of the rows begin with an asterisk, and some do not. For example

1. *John Smith Anytown, USA
2. *Mary Jones Big City, USA
3. John Smith Anytown, USA

Should appear on the report as:

1. *Mary Jones Big City, USA
2. John Smith Anytown, USA

Any thoughts are appreciated.

 

[Naith]

Group on

If Left({Name},1) = '*'
Then Mid({Name},2)
Else {Name}

Then either:

(a) sort on the above formula, and suppress the section based on:

Previous({Name}) = {Name}

Apply a condition to the section suppression of: Not OnLastRecord

or

(b) suppress the group header and Details section, and display your fields in the footer of your new group.

Naith

 

[IanWaterman]

Since * is a wildcard this will be difficult to eliminate.

As your data is an SQL recordset, why not modify the SQL to eliminate * at that point.

Then when you have the data simply group on account and use the groups headers or footers as your detail line. This will eliminate yur duplicates.

Ian Waterman
UK Crystal Consultant

 

[Naith]

'*' isn't a Crystal wildcard.

 

[redtoad]

Naith, your suggestion works great. The only remaining issue is I need to show the * in the report. How do I add the * back into the correct rows?

 

[redtoad]

Can I use the evaluation time functions for this?

 

[Naith]

Do you mean:

Do you mean:

Group: John Smith Anytown, USA
Detail: John Smith Anytown, USA
*John Smith Anytown, USA
Group: Mary Jones Big City, USA
Detail: *Mary Jones Big City, USA

If so, replace the formula in the detail section with the actual database field.

For this to be successful, you should have followed (a), and not (b), as you need the Details section to be displayed.

If you meant something else, can you give an example of what you were looking for? Something like your first post would be helpful.

Naith

 

[redtoad]

My recordset consists of records that are returned via two different queries(through union all). The records returned with the * were returned from one query, and the records returned with no * were returned from a different query. So for all of the rows that are unique, after stripping out the *, I need them to show them as they are from the recordset, with the * if they originally had one or without the * if the row never had one.

For those rows that are the same after stripping the *, I need them to show without the *. For instance, if I have the following recordset:

1. *John Smith Anytown, USA
2. Mary Jones Big City, USA
3. *Jeff Johnson Smalltown, USA
4. John Smith Anytown, USA

I need crystal to display:

1. Mary Jones Big City, USA
2. *Jeff Johnson Smalltown, USA
3. John Smith Anytown, USA

2.

 

[Naith]

If you remove the grouping field from where it's being displayed (continue grouping on the field - just don't make it show up in the report), and replace the display with the actual database field, don't you get what you're talking about in your last post? (Note, you need to be following (b) for this to occur.)

As long as you are sorting by Name descending, the value of the database {Name} field showing up in the group footer should be that of the maximum name for that group. i.e. 'John', where there's a '*John' and 'John', and '*Mary' where there's just '*Mary'.

Naith

 

[redtoad]

Yes, it's working now as intended. I was still using option a in my previous posts. Option b with your additional suggestions got me the correct display.

Thank you!