supressing duplicate records 1

[Mack2]

My report has about 10 tables. It is duplicating records from the purchasing tables. Any idea how I can suppress the duplicate records so the sub totals do not reflect the duplicate records. Please help. Thanks

 

[synapsevampire]

This is dependent upon numerous things, but the quick answer is to try Database->Select Distinct Records.

Otehrwise post specifics and your environment.

-k

 

[lbass]

Suppressing records will not eliminate them from subtotals. Your best bet is to use running totals. Select the amount field, sum, evaluate on change of {Purch.PurchID} (where this field is an ID unique to a transaction), reset on change of group (if you have a group on something like {table.AccountNo} or reset never (for a grand total). Place the field in the details section as a test to see what it is doing, and then copy it into the group or report footer (whichever level you are evaluating for). If you need summaries at both group and report levels, create a separate running total with a different or no reset.

-LB

 

[Mack2]

Thank you. I will try it this morning.

 

[Mack2]

I have the running totals. But when I add the withdrawl lines table(withdrawls from the warehouse). All of my records are duplicated or tripled depending on how many purchases there are. I have tried sub reports, but no luck. I will be calculating in the group footers section(total purchased - total withdrawal) Any Ideas. THANK YOU FOR YOUR HELP!

 

[Mack2]

It is almost working. Except my variable is not resetting to zero. This is my code
in header section
Numbervar RunningTotal;
RunningTotal:= 0
Detail section
Numbervar RunningTotal;
if {PurchaseLines.QtyReceived} = next({PurchaseLines.QtyReceived})
then RunningTotal
else RunningTotal:= RunningTotal + {PurchaseLines.QtyReceived}
Footer Section
Numbervar RunningTotal;
RunningTotal
When I get to the next group the variable has the values from the previous group

Help

 

[lbass]

What version are you using? The running total editor is much easier than creating manual running totals when you are trying to add many of them.

If you must use the manual approach, you need to put:

Whileprintingrecords;

at the beginning of each of the three formulas.

-LB

 

[Mack2]

Thank you, that did make a difference. But the totals are not correct for every group that has the same number of withdrawls. If within the same group the number of withdrawls are the same it does not add the second number. I am trying to sort those numbers by using the group sort expert, but it is not sorting them.
Then code I have tryed
WhilePrintingRecords;
Numbervar RunningTotal;
if {PurchaseLines.QtyReceived} = next({PurchaseLines.QtyReceived})
//if {PurchaseLines.itemnid} = next({PurchaseLines.itemnid})
//and {PurchaseLines.ItemNid} = next({PurchaseLines.ItemNid})
then RunningTotal
else RunningTotal:= RunningTotal + {PurchaseLines.QtyReceived}
I hope this makes sense, it is driving me crazy. Thanks for your help

 

[lbass]

You should use the item number, not the quantity received field to test for duplicates. It would help to see an example of what your data looks like with groups and at the detail level--that's really necessary in order to help with running totals.

Please say why you are not using the running total editor--it would be so much easier for you.

-LB

 

[Anoli]

I am new at working with variables and I have a question.
I have inserted a subreport in a report, in the subreport I declared a shared variable, that picks up the value of a filed (report to sub links are one on one).
I bring the value of the variable in the main report in a formula.
The variable is a string, and I want to be able to group by that field (and in this group summarize other fields).
Is that possible?
Thnak you.

 

[Mack2]

I was going by an example from crystal, on how to get around duplicat records. Will the editor do everything that the code will do. I will try switching fields.
Thanks for your help

 

[Mack2]

Anoli
That is a good question. You will want to start a new thread and ask it. Just copy your original then paste it into a new thread.

 

[Mack2]

Would you like me to mail it to you. Because it does have 3 groupings. You are right, there is a lot going on that is hard to explain. Thanks for your help!!!!

 

[Mack2]

I am using the item number. The only thing is I can not sort by item number. Because it is in a group and item number is not subtotaled(otherwise I could use the group sort expert)Because it is not sorted, it is summing the same record everytime is shows up. If I could get it to sort by product id, my formula would not sum on duplicate records. This is getting pretty deep! Thanks

 

[lbass]

Please provide the layout as it appears on your report, showing groupnames and sample details so that we can see where the duplicates are occurring. We need to stay within the forum--side conversations do not benefit other users and also imply an amount of time that I cannot donate. Thanks!

-LB

 

[Mack2]

My formula(from above)checks the next record to see if it is the same ID#. If I can not get the id sorted, then it includes it in the running total. Here is an example.
dj9-0962573-7 (group 1, like an order id)
PRIMO (group 2, a brand of a product)
Kings(group 3, a package which is part of the brand)

ID# qty rec running total
399 300 300.00
400 300 600.00
402 300 900.00
399 300 1,200.00
400 300 1,500.00
402 300 1,800.00
399 300 2,100.00
ID# 399 is an example.
It is duplicating because the report includes about 10 tables. THANKS FOR YOUR HELP!

 

[lbass]

Why can't you add a group #4 on Item #? Or even just add the item number field to the sort order (Report->sort records)? Either way would order your report so you could deal with duplicates.

If these don't work, then we need to know what field (not shown in your example) is causing the duplication. It might not be obvious to you without looking at your table links, but I'm sure there is one, lets' call it "duper". Once you identify it, you can reset your formula based on that:

duper ID# qty rec running total
1 399 300 300.00
400 300 600.00
402 300 900.00
2 399 300 300.00
400 300 600.00
402 300 900.00
3 399 300 300.00
400 300 600.00
402 300 900.00

-LB

 

[Mack2]

That worked! I did not know you could control group sorting within the record sort expert. Now my running totals are looking great. I now have to do the same for a couple of other fields. Then perform some calculations in the group footers. Thanks Again for your help!!!!!! thank you thank you!!

 

[Mack2]

I started the running total on the next field. Because how I have the the report sorted(by item #) I can not get the qty withdrawal # after each other. For my results I want to subtract withdrawl from received in the last group. Is this going to be possible?? Thanks
DJ9-0961749-4
PRIMO
100'S
item# qty rec run withdrawal# qty wtdl run total
393 804 0 4 6 6
393 804 0 4 44 50
393 804 0 11 25 75
393 804 0 17494 1 76
393 804 0 17494 2 78
393 804 0 17494 3 81
393 804 0 17494 4 85
393 804 804 17494 6 91
396 295 804 4 6 97
396 295 804 4 44 141
396 295 804 11 25 166
396 295 804 17494 1 167
396 295 804 17494 2 169
396 295 804 17494 3 172
396 295 804 17494 4 176
396 295 1,099 17494 6 182

 

[lbass]

Sorry, I can't follow your example. Please indicate which fields are running totals by using labels like {#runtotname}, which are formulas {@formula}, and which are fields {table.field}. Also, if the running total results look correct to you, and if you are interested in calculating at the group level, you can place the running totals in the group footer, and just show those results. Then we can go from there. Is the issue whether you can subtract running totals? If so, the answer is yes. Go to the formula editor and enter:

{#runningtotalA} - {#runningtotalB} //or you could subtract formula results

Of course the results will differ depending on where you place the formula, details or group, but usually you want to look at the results for a group, so it would be placed in the group footer.

If you want to create another example, please also indicate specifically what fields you want to subtract and show the result you want to get. Thanks.

-LB