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 ;-)

Numbers are not always displayed as Positive.

Gridview displays positive numbers and negative numbers differently.

999.99 is positive; (999.99) is negative.

In the formula

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

it is the -{Total Dollar Value} that makes the Commission negative.

Hi Michael:

I understand that, but that's not what I see happening. Please look at the attached screenshot. You'll see 3 credit notes. Under both 'Total Dollar Value' and 'Total Order Value' you'll see positive amounts. I had to create a calculated column called 'True Amount' that would convert those amounts that were credit notes into negative amounts.

Cheers,

Andre

I meant 'Total Dollar Value' only. The amounts in 'Total Order Value' for credit notes appears to always be zero.

Andre:

Yes, "Total Dollar Value" will always be positive. You need to look at the type of Document and reverse the sign if it is a Credit Note.

That is what Michael's formula does. If the Document is "C", then switch the sign. And this is exactly what you did with your "True Amount" calculated column.

I believe that you and Michael are in agreement, just expressing yourselves differently.

The only question is why you weren't able to use your "True Amount" column to calculate your commission amount. As David said earlier, that will work. For instance: {True Amount} * .10 will give you proper figures.

Of course, since you have a calculation that works, why change it?

Hi Bruce:

Yes, now that it's working, I'm not going to change it.;-)

By the way, I recreated this report using the Sales Order Detail table (We had to exclude miscellaneous charges - and those are not in the header table). I used pretty much the same formula ('Amt' instead of 'Total Dollar Value') - and everything worked like a charm. And the user I wrote this for seems to like the result.:-)

Thanks all for your help!

Andre

Andre,

I am not familiar with the programming language used for Adagio modules, but there is one general underlying reason why the number may always be stored as positive ones: you can store larger numbers.

A small example:

In one byte, 8 bits, you can store values up to 2 to the 8th power, 256.

But, if you take away one bit to store the sign, you can then store values from -128 to +128.

The same is true, of course, for multi-byte integers as well.

Just a thought: I'm sure Adagio's programmers did not set it up that way just to annoy you and me .