By that I mean the total of all the items ordered, excluding taxes, shipping and other miscellaneous charges. I'm using the Sales Order Header Master table for a GridView report. I'm sure I could use the order detail table and exclude misc. charges, but that would take a lot longer. Would be nice if the header table had that information somewhere.

Along those same lines, I noticed that even for credit notes, the Total Order Amount or Total Dollar Amounts are positive - when they should actually be negative. How do I get around that?

Thanks,

Andre

Hi Andre,

The reason you don't see a total amount of items (excluding misc charges and taxes) on the Order Header is that this information is not stored there. Yes it would make this inquiry faster but it's not there. You have to use the Detail information (either in Order Entry or SalesAnalysis) to determine sales exclusive of misc charges and taxes.

And the data structure does store the Credit Note amounts as positive - only in custom reports/views does this get noticed. You have to create a formula (Calculated Column in GridView) that looks at the document type field and if it is "C" then multiply the amount by -1.

IF({Doc type} = "C",-{Total Dollar Value},{Total Dollar Value})

Thanks! I was afraid you would say that.;-)

I used the exact same formula for the credit note amounts, EXCEPT I used it inside a filters - with the result of no credit notes being listed in the report. I'll try using a calculated column.

Thanks again for the quick response!

Andre

OK, I created a calculated column called 'True amount' based on the formula shown in the above message. This now shows the credit note amounts as negative (they actually show as "(3,289.44)".
However, when I looked at the commissions to be paid in this report, even the supposedly negative amounts resulted in positive commissions being calculated. For instance, 10% commission on (a credit note amount of) "(1,000.00)" resulted in a commission amount of "10.00". Obviously, GridView doesn't view amounts in parantheses as negative amounts.
In order to get around that, I created another calculated column called '$$$' (to distinguish it from my existing '$ Comm' column and entered the following formula:

IF({Doc Type}="C", -{True Amount}*{% Comm}/100, {True Amount}*{% Comm}/100)

However, the commissions calculated in the '$$$' are still all positive. Is there something I'm missing? How do I get GridView to view amounts in parentheses as negative amounts?

thx,

A.

Try dropping the - sign in your formula IF({Doc Type}="C", -{True Amount}*{% Comm}/100, {True Amount}*{% Comm}/100).

But you should be able to use calculated column with the formula {True Amount}*{% Comm}/100).

David

Hi David:

Thanks for your post! I tried that, figuring that GridView would know that any amount in parentheses is a negative amount. Only when that didn't work (i.e. the commissions amount was positive) did I add the minus sign - thinking/hoping this would most certainly tell GridView to treat any credit note amount as a negative amount.
Still no luck. Gridview continues to think all amounts are positive - parentheses or not.

Andre

Hi Andre,

What is the value of the {% comm} field? Perhaps you are dealing with a double negative? I recommend when creating Calculated Columns or Filters to at least temporarily put all values you are using for formulas as columns in your inquiry. This way you can be sure that the calculations are using the values you expect.

For example, if {True Amount} is negative and also {% Comm} is negative, the result of multiplying will always be postive.

GridView treats Positive Numbers as "Positive" and Negative Numbers as "Negative". There is something else going on.

Try

IF({Doc Type}="C",-{Total Dollar Value}*{% Comm}/100, {Total Dollar Value}*{% Comm}/100)

Andre,

What table are you now using. I can't find a field {%Comm} in the tables I looked at. I did the formulas substituting 10 for {%Comm} in {True Amount}*{% Comm}/100) and everything worked fine.

David

Hi David & Michael:

{% Comm} is a calculated colum. I tried Michael's suggestion - and it worked. I guess, I overly complicated things by using the 'True Amount' calculated column to calculate the commissions - thinking that I couldn't use the Total Dollar Value field since it always returned positive amounts. I guess, as Michael pointed out: GridView knows which numbers are positive and which are negative. It just always displays them as positive - no matter what.
So, using the Total Dollar Value field will result in a negative commission if it's a credit note. It's just confusing that it doesn't display the Total Dollar Value (in my opinion) properly.
Thank you all for your help! Going home now...

Andre ;-)