AppGini Community Forums

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

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

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.
PHP's data casting and manipulation is far superior to MySQL's.

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

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

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

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