Using Record Number Field to Suppress Records

[cgilmore]

I have a crystal report where I am pulling contacts for our sales agents in the field. We then export that data to Word and e-mail it to them. We need to be able to track how many leads we send them each quarter and make sure we don't send them duplicate leads. I put the record number field in the detail section and it is counting the leads for me. However it doesn't reset when my group of agent name changes. Can you tell me how to get it to reset? ALSO, I have an incorrect formula I have put in the detail section expert suppress area
{Contact.Lead Type} = &quot;Tradeshow&quot; and {Contact.Date Assigned:} < date (2003, 01, 01)
and {#Record Number} in 1 to 200 I was attempting to filter out the first 200 records. This formula is not working. Thanks for your help!

 

[AlexB1234]

I believe you need to put it in the group footer so it will reset with each group. It has to go in the footer so that it will count everything.

Also in Crystal .net you can create a Summary field and specify that it resets with a particular group (you indicate the group) on the property page.

 

[lbass]

Instead of using recordnumber, which as far as I know cannot be reset, since it is a direct count of the number of records in the report, create a running total using the running total editor {#countwithingroup}:

Choose a recurring field like {table.ID}, count, evaluate for each record, and reset on change of group (agent).

For the section suppression, use a formula like:

{#countwithingroup} in 1 to 200

Is there a reason you are not using the record select to eliminate the following: {Contact.Lead Type} = &quot;Tradeshow&quot; and {Contact.Date Assigned:} < date (2003, 01, 01)

If you have to suppress them instead of filtering them out through your selection formula, then you should decide whether you want the running total to count them or not. If not, then in the running total editor, use &quot;evaluate based on a formula&quot; and add:

not({Contact.Lead Type} = &quot;Tradeshow&quot; and {Contact.Date Assigned:} < date (2003, 01, 01))

For the suppression formula, try:

{#countwithingroups} in 1 to 200 or
({Contact.Lead Type} = &quot;Tradeshow&quot; and {Contact.Date Assigned:} < date (2003, 01, 01))

This will suppress the first 200 records and any records 200+ that meet both of the other criteria (if that's what you're trying to achieve).

-LB

 

[cgilmore]

I added the not ({Contact.Lead Type} = &quot;Tradeshow&quot; and {Contact.Date Assigned:} < date(2003, 01, 01))to my select expert formula and I created the countwithingroup running total so I know how many leads each agent has. Some guys have more than 200 leads and others have < 200. I put the following in as the suppress formula for the detail section and it is not suppressing anything. if {#countwithingroup} > 200 then {#countwithingroup} in 1 to 200. I also tried if {#countwithingroup} > 200 then {#countwithingroup} in 1 to 200 and {#countwithingroup} in 402 to 1500. Because if they have more than 200 leads I want to only send them leads 201 to 401 this time. If they have under 200 leads I want to send them their leads. Is it because the #countwithingroup is in the Group Footer and I'm trying to suppress the detail section? Do I need to copy everything in my detail section to my group footer section?

 

[lbass]

If you have an id field that is unique so that you can eliminate duplicates by doing a distinctcount of that field, then you could take this approach, assuming you have grouped on {table.agent}. Go to format section->details->suppress->x+2 and enter:

if distinctcount({table.ID},{table.agent}) >= 200 then
{#countwingroup} in [1 to 200, 402 to 1500]

-LB

 

[cgilmore]

Thanks! That worked. I do have one more question. How many records can you export from Crystal to Excel. I tried 22,008 and I received an error message. I tried 9000 and didn't receive an error message.