Simple math

[buck1942]

Using Appgini 5.51.

Given three fields:

budget
actual
remaining

The following syntax works:

sql("UPDATE monthly_budget set remaining = budget - actual ", $eo);

ONLY if actual is not NULL...

for example, if "budget" = $1000.00 and "actual" = null, then "remaining" will NOT calculate; however, if "budget" = $1000.00 and "actual" = .00 then "remaining" computes to $1000.00 as it should.

My problem is that "actual" needs to be a Read Only field. "Actual" only gets changed via a posted transaction, creating a detailed transaction audit trail. If I allow an operator, when setting up a new account, access to the "actual' field and and she/he fails to enter ".00" (thus the "actual" would be NULL) the "remaining" amount will be incorrect.

Zero fill works, but it makes for sloppy looking reports etc.

Looking for help...

Buck

[peebee]

Have you tried making default value for "actual" as 0 (zero)?

[grimblefritz]

Or, if you want actual to be null (and not a default of zero) until it is set via program, you could compute remaining in php and update using that result.

Code: Select all

$remaining = $data['budget'] - $data['actual'];

sql("UPDATE monthly_budget set remaining = {$remaining}", $eo);

PHP's data casting and manipulation is far superior to MySQL's.

[buck1942]

Grimble, I'll try that, thanks.

Peedee, I was looking for a way to default to "0", but I did not see how to do it... I'll take another look, that would be my "druthers"...

Thanks too,

Buck

[buck1942]

peedee... I set the default to ".00" and all is well and works like I want it to.

Sometimes, it is sooo easy to overlook the obvious... when defining fields, I just looked over the "Default" option (even tho' it is in the middle of the screen)... go figure

Again, thanks to both of you for your help...

Buck

[grimblefritz]

No worries. It took me quite some time to realize that the default window size on my system was showing me one line of the "Guidelines regarding the .... data type". Only when I eventually maximized the window, quite by accident, did I notice there was more than one line there

[Bertv]

When 'budget' can be null you have the same problem....
You can always use 'ifnull' if it is not sure the field has a value:

sql("UPDATE monthly_budget set remaining = ifnull(budget,0) - ifnull(actual,0) ", $eo);