Record duplicating with each line in database

[jaguar00]

I'm using 8.5 and I'm making a report that will list every quote for a particular customer. Each quote listed should show the total estimated value. I don't have a field in the database that has that value so I wrote a formula called Net like this: {tbl_YSG_QuoteLineItems.Quantity} * {tbl_YSG_QuoteLineItems.Price} and a formula called Value like this: {@Net} - {@Net} * ({tbl_YSG_QuoteLineItems.Discount}/100) thinking it would give me the total value for the entire quote but it lists the total value of each line in each quote. Here's the output:

Quote#: LMRT5478 Quote Date: 10/2/00

Value: 2,485.89 Quote Status: Won
______________________________________________________

Quote#: LMRT5478 Quote Date: 10/2/00

Value: 4,138.98 Quote Status: Won
_______________________________________________________

Quote#: LMRT7312 Quote Date: 1/31/01

Value: 1,983.52 Quote Status: Won
_______________________________________________________

Quote#: LMRT9521 Quote Date: 3/25/01

Value: 1,438.84 Quote Status: Won

As you can see it will list the total value as long as the quote only has one line item.

I'm really new at writing formulas so any help is extremely appreciated. Thank you!!!

 

[Naith]

Hey Jaguar,

Try these instead:

({tbl_YSG_QuoteLineItems.Quantity},{QuoteID}) * ({tbl_YSG_QuoteLineItems.Price},{QuoteID})

{@Net} - {@Net} //Don't understand this bit. Why would you want to subtract something from itself and not expect to get zero?
* (({tbl_YSG_QuoteLineItems.Discount},{QuoteID})/100)

The difference is that the ",{QuoteID}" part I've added represents your grouping field. If you're grouping by QuoteID, this will total all the values by group before performing your calculation.

Naith

 

[jaguar00]

I used your formula but it's telling me the ) is missing and should be inserted after ({tbl_YSG_QuoteLineItems.Quantity} and before {QuoteID}). I put it there but I still get "The remaining text does not appear to be part of the formula."

I'm sorry......HELP!

 

[Naith]

Sorry, there should be a "sum" prefixing everything in parentheses.

e.g. Sum({tbl_YSG_QuoteLineItems.Quantity},{QuoteID}) * Sum({tbl_YSG_QuoteLineItems.Price},{QuoteID})

Give that a go.

Naith

 

[jaguar00]

OK...I grouped on QuoteID and I have the totals correct but it's still listing them twice or however many times according to the amount of items listed in each quote. I tried supressing if duplicated but that doesn't work either.

 

[Naith]

I think you should be placing your fields in the group header or footer, not in the details section.

 

[jaguar00]

OK, thanks. I'll try that this morning. Thanks again for all your help. I'm so new I know just enough to get myself into trouble with this stuff. Have a great Friday!