ONE RECORD DUPLICATING

[Former Member]

I designed a report and checked my totals with no problem on one location, but needed to add misc charges on another location. Because my transaction file added the misc charges to each item line of the invoice, I added a table that would include the misc charges at the invoice level. When I did that, one invoice of the location that was working is now duplicating. All the remaining invoices are fine. This record has nothing to do with the misc charges. Can anyone tell me how to figure out why the one record is duplicating?

[Former Member]

Sounds like multiple misc. charges per invoice. Do a sum of those charges.

Thanks

[Former Member]

I tried to change the joins up with no luck.

I can't do a sum of the charges because the transaction file repeats the misc charges on each line even though each invoice has only one charge. I used a running total to get the correct amount of miscellaneous charges and that's when some of the records duplicated. There are only two customers which have miscellaeous charges. I'm wondering if I can make a formula specific to those two customers.

[Former Member]

Hi

Yes, that is better Idea. If you want to display zero in place of Misc for other csutomers then follow this formula :

If in [xxxx,yyyy] Then {runningTotal Misc} else 0 or If you want to suppress the entire line in your report then go in section expart and write suppress condition like : if in [xxxx,yyyy] Then false else true

Thanks,

Sastry

[Former Member]

This is what I tried:

if {TransDetailCustomerView.CustNo} in [acct,acct] then Sum ({TransDetailCustomerView.InvoiceNet}, {TransDetailCustomerView.InvoiceNo})+{#MISC CHARGES}

else

Sum ({TransDetailCustomerView.InvoiceNet}, {TransDetailCustomerView.InvoiceNo})

I used a running total for the misc charges and it worked on the location where there are actually misc charges, but then it still duplicates some records on the others.

Since the misc charges repeat in the transaction file how would I write a formula to only select misc charges one time per invoice? That would allow me to remove the table that has invoice summary info and not item info.

I didn't have duplicate records before I added the summary table.

[Former Member]

My formula worked. I had an error in my running total and when I corrected it the formulas worked.

Edited by: GayleB on Jul 20, 2011 11:37 AM

[Former Member]

Hi

It could be because of table linking. What is the join you are using ? Try to change the joing type and check.

Thanks,

Sastry