How to Avoid Duplicates When Selecting Records

[Jtreshan]

I am working on a report which outputs items which are &quot;in progress&quot;.&nbsp;&nbsp;The report works correctly, but I want to add an estimated labor time for each item.&nbsp;&nbsp;This information is only available in a separate &quot;Transaction&quot; table in a field called ACTLABHRS.&nbsp;&nbsp;When I add this field to the report, I need a way to <br>limit the reporting of a specific item to only one entry (the way it works now is that all work on each item matches, so my report shows multiple entries for the same item, even though only one is presently being worked on).&nbsp;&nbsp;I guess what I need is a formula which essentially states &quot;If field XXX already has entry YYY, do not select entry YYY again&quot;.<br><br>

 

[HoustonGuy]

Hmmmm, sounds like all you may need to do is just place the ACTLABHRS in the detail section, (or wherever you want it to show up) and suppress if duplicated. (To do this right click the field after you have placed it on the report and choose &quot;Format&quot; then check the appropriate box.)<br><br>I may be over-simplifying your issue.<br><br>If I am you can always try writing a conditional suppress formula to hide itself if YYY is true. (Right click the field after it has been placed on your report and choose format. The first object format will be suppress with a formula button to the right. Click that button and place your code in the formula editor. REMEMBER - don't check the suppress checkbox. Leave it blank and let the formula take over.)<br><br>I am walking you through every little step since I'm not sure how familiar you are with Crystal. No insult intended.<br><br>Hope this helps. <p>Cody ford<br><a href=mailto:codyford@yahoo.com>codyford@yahoo.com</a><br><a href= > </a><br>VB, VBS, Seagate Info/Crystal Reports<br>

 

[Jtreshan]

Cody,<br><br>Finally got it finished.&nbsp;&nbsp;Your suggestion with the formula cleared up most of <br>the problems, but the subtotals were still incorrect for some of the fields for which<br>I was using a running total.&nbsp;&nbsp;I got that fixed by changing the subtotal requirements<br>to evaluate on a change of the key field instead of the field I was sub-totalling.<br>Thanks again for your help.<br><br>Joel

 

[Jtreshan]

Sorry Cody, I was a bit premature.&nbsp;&nbsp;The report now selects the correct records; however, the subtotals and grand totals of number of items in each area, and hours in each area are too high (I presume because even though using your formula I can prevent the records from showing in the report, they apparently are still being selected).&nbsp;&nbsp;Can I use a similar formula to stop the running totals from counting<br>the records and labor hours for those records for the ones that are not current?

 

[LindaC]

You need to put a formula in Report¦Edit Selection Formula¦Record.&nbsp;&nbsp;Enter a formula which will exclude the records which are duplicates.&nbsp;&nbsp;Crystal will not choose these records, so they will not be included in your totals. <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[Jtreshan]

I understand that I need a formula to exclude duplicates, and I thought that's what I <br>put into the selection formula, which now reads If (field name) = field name, Then True<br>I think I need help creating a better formula than that (the field name I'm using is<br>the one name in the report which is a unique field.

 

[Jtreshan]

Last post was incorrect.&nbsp;&nbsp;The formula I'm using is<br>If Previous ({fieldname}) = {fieldname} Then True

 

[LindaC]

You want to select the record if the Previous({fieldname}) is not equal to ({fieldname}).&nbsp;&nbsp;If they are equal they will be duplicate. <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[Jtreshan]

If I modify the formula so that Previous {fieldname} is not equal to ({fieldname}), I get all the duplicates, which is the opposite of what you would expect.