• Status: Solved
• Priority: Medium
• Security: Public
• Views: 255

# 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
0
BERITM
• 2
• 2
1 Solution

Commented:
In the formula editor there is a drop down.  The default setting is EXCEPTION FOR NULLS
Change that to DEFAULT VALUE FOR NULLS

The other way to handle this is to use formulas for the values or NULL tests in the sum formula

Formula SellPrice

If IsNull({tblSO.sellprice}) then
0
Else
{tblSO.sellprice}

Formula Cost
If IsNull({tblSO.cost}) then
0
Else
{tblSO.cost}

Formula ContractorCost
If IsNull({tblsubcontractor.cost}) then
0
Else
{tblsubcontractor.cost}

{@SellPrice}  -  ({@Cost} + {@ContractorCost})

You can also do this in a single formula

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

mlmcc
0

Commented:
mlmcc,

The last formula should be subtracting the costs, right?

James
0

Commented:
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
``````
0

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

James
0

Author Commented:
Thank you very much, the formula worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.