asked on

Calculate three fields

Running CR 10

Looking for profitability using 3 fields. Two fields are in one table and one is another, all display currency records. One of the fields only returns currency when it has data otherwise its blank which is causing problems trying to calculate using the other fields. Below are the fields and sample data:

tblSO.sellprice
tblSO.cost
tblsubcontractor.cost

sellprice socost subcost
SO No. 12345 $200.00 $100.00 $50.00
SO No. 23456 $100.00 $0.00
SO No. 34567 $300.00 $100.00 $100.00

I need to calculate the profit using the fields but because SO No 23456 is blank the formula I'm using is not calculating correctly. Using the formula below shows a profit of $100.00 (seems to only be using the last row instead of all three) should be a total profit of $250.00.

{tblSO.sellprice} - ({tblSO.cost} + {tblsubcontractor.cost})

Formula is used in GH1 (tblSO.sonumber) and RFa

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

mlmcc,

The last formula should be subtracting the costs, right?

James

Mike McCracken

Actually the last 2 should be subtracting

Local CurrencyVar total;
If NOT IsNull({tblSO.sellprice}) then
    total := total + {tblSO.sellprice};

If NOT IsNull({tblSO.cost}) then
    total := total - {tblSO.cost};

If NOT IsNull({tblsubcontractor.cost}) then
    total := total - {tblsubcontractor.cost};

Total

Open in new window

James0628

Yeah, that's what I meant ("costs" was referring to both cost fields).

James

BERITM

ASKER

Thank you very much, the formula worked perfectly!