Formula Question ??? If Then Statements 3

[LadyAmpherion]

First let me apologize for not know how to even give this a proper subject line ...

Here is the scenerio ... I'm am creating a Detailed Costs Reports that includes the Sales Price, the Estimated Budget, the Actual Money Spent, plus the remaining amount committed but not yet spent.

So here's my problem ... for Actuals ... I only get a number for the Qty and Dollars if there has been an amount received ... When I pull the data for the report, it gives me a blank field instead of a ZERO in the field ... I tried writing an IF-THEN-Else STatement such as follows:

If RCVDQTY > 0
Then RCVDQTY = RCVDQTY
Else RCVDQTY = 0

the statement "works" but not correctly ...

what it does is if it is > 0 it says TRUE, Else it remains BLANK .... not even FALSE ... but what I want it to return is the actual value if greater than Zero, or Zero if there is no value.

Any help here would be greatly appreciated... Thanks!

Louanne

 

[midearth]

You might be better using variables. Try:

numbervar x:=RCVDQTY;
if RCVDQTY > 0 then x else 0

 

[Katy44]

Try:
if isNull(RCVDQTY) then 0 else RCVDQTY

nulls aren't evaluated in the way you would normally expect!

Hope this helps, let me know if it hasn't

 

[LadyAmpherion]

Part II: I now have my Actual Qty Received or the Zero Value using Katy44 if is Null(RCVDQTY) ... et cetera

The next part is similar but a little more complicated ...
when any amount is received, the RCVDCOST Value gets updated from the Receipt against the Purchase ORder; however if there are ZERO receipts then there is ZERO RCVDCOST.

The Value that I need to pull is on the PODETAIL ... and using the following Scenerio (thanks to Midearth):

numbervar x:=ACTCOST;

if ACTCOST > 0 then x else PODETAILPRICE

I actually get the Correct Value, BUT I lose values where there have been NO Material Receipts and I get DOUBLE Entries on Items that do Have Material Receipts but not consisttently ...

I'm sorry to be so ignorant ... and welcome all of your assistance. Thanks again for any help that I may receive!

 

[Katy44]

Sorry, but I'm a bit lost! Could you explain the tables that are involved, and their relationships?