How to select unique records with highest assigned number

[jgl99]

Selecting all records via paramater fields for date and campaign_id. Records contain phone #'s, status, and a number field. There can be several records with the same phone #, a diferent status and a different number field. I need to select only those records with the latest status. The record with the latest status would be the record with the highest value in the number field.

Any ideas?

Thanks in advance.

 

[TexasGal2]

You're going to have to bring all the records back. Then in Crystal put your data in the details section in ascending order by the field you want the latest record for, then create a group for each set of records, put the fields in details in your group footer and suppress the details. You'll get the last record only which will be the latest record.

I'm doing a quick test on one other method that might work better - if it does, I'll post it.

 

[jgl99]

Thanks, you are correct. However, I need to group and summerize the data by final status. To do that, I believe I would have to select only the data I want to summerize. Is there a way to do that?

Thanks

 

[lbass]

After creating a group on {table.phoneno}, you can go to report->edit selection formula->group and enter:

{table.number} = maximum({table.number}, {table.phoneno})

Only one record will be displayed per phone number, but for summaries, you will need to use running totals, since the non-group selected records would still contribute to the more usual summaries.

I don't think you can group on status as a higher order group, since it would force the return of multiple records per phone number, despite the group select. However, you could achieve a display by status using running totals in the report footer. Using the running total editor, you would select distinctcount of {table.phoneno}, evaluate based on a formula: {table.status} = "Status A"

Reset never. Do this for each status, and place in the report footer next to an identifying text field:

Status A: 145
Status B: 263
Status C: 100
Total: 508

-LB

 

[jgl99]

Thanks.

You were right on!