Calculated fields

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
pasbonte
Veteran Member
Posts: 162
Joined: 2013-02-06 09:49

Calculated fields

Post by pasbonte » 2021-05-05 06:40

Hello
Calculated fields
I have this code which works great in SQL.
Except that ID11 is NOT displayed (on the mysql console it works), I made the field read only, integer ... I use the calculated fields.
An idea ?

Code: Select all

SELECT (  `DNB_MAITRISE_SOCLE`.`D11_FR`+`DNB_MAITRISE_SOCLE`.`D11_HG`+`DNB_MAITRISE_SOCLE`.`D11_SVT`+`DNB_MAITRISE_SOCLE`.`D11_PHY`+`DNB_MAITRISE_SOCLE`.`D11_TECHNO`)/5
FROM DNB_MAITRISE_SOCLE WHERE `DNB_MAITRISE_SOCLE`.`id`='%ID%;
Capture d’écran 2021-05-05 083824.png
Capture d’écran 2021-05-05 083824.png (34.37 KiB) Viewed 1687 times
Capture d’écran 2021-05-05 083854.png
Capture d’écran 2021-05-05 083854.png (10.55 KiB) Viewed 1687 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields

Post by pbottcher » 2021-05-05 06:53

Hi,

the code looks good.

Can you please try to set a value in that field of the database manually for a record, the call that record via your app and check what value is written back into the database.

Also you could try to be on the safe side to put each value in a IFNULL statement like

Code: Select all

IFNULL(DNB_MAITRISE_SOCLE`.`D11_FR`,0)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

pasbonte
Veteran Member
Posts: 162
Joined: 2013-02-06 09:49

Re: Calculated fields

Post by pasbonte » 2021-05-05 08:00

Indeed: the mysql database does not update.
Capture d’écran 2021-05-05 095852.png
Capture d’écran 2021-05-05 095852.png (11.28 KiB) Viewed 1670 times
UPDATE ?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields

Post by pbottcher » 2021-05-05 08:38

Hi,

did you try to change your statement and add the IFNULL ?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Calculated fields

Post by jsetzer » 2021-05-05 09:46

+1 for @pböttcher:
Using NULL for calculations may produce unexpected results, I totally agree, see simple example in SQL:

chrome_bHPwdWloWX.png
chrome_bHPwdWloWX.png (61.53 KiB) Viewed 1656 times

But on the other hand we can see from the screenshot that you don't have NULLs in the selected fields of that specific row.

Narrow down

Did you check if your calculation gets executed at all? Maybe change SQL to

Code: Select all

SELECT id FROM %TABLENAME% WHERE id='%ID%'
and check if every record contains the record's id after loading the TV.

PS: After Re-reading the thread and pböttchers answer above, I guess this is what he wanted you to check by asking you to change the value in DB and then reloading the AppGini DV. Reloading should overwrite the DB-value, he's totally right. This here is just a different approach for testing execution.


Sidenote: Datatype integer?

I'm wondering if datatype integer is suited for the result of division by 5 of a sum of integers. I guess changing to double/float/decimal datatype will not solve your problem, because division by 5, casted to integer should just cut decimals but should not result in NULL.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Calculated fields

Post by jsetzer » 2021-05-05 09:58

Perhaps the solution is much simpler:
I just saw that a single quote is missing at the end of your SQL command
SELECT ( `DNB_MAITRISE_SOCLE`.`D11_FR`+`DNB_MAITRISE_SOCLE`.`D11_HG`+`DNB_MAITRISE_SOCLE`.`D11_SVT`+`DNB_MAITRISE_SOCLE`.`D11_PHY`+`DNB_MAITRISE_SOCLE`.`D11_TECHNO`)/5
FROM DNB_MAITRISE_SOCLE WHERE `DNB_MAITRISE_SOCLE`.`id`='%ID%;
%ID% should be surrounded by single quote ' (and you don't need the ";" here)

Code: Select all

SELECT (ifnull(D11_FR,0) + ifnull(D11_HG,0) + ifnull(D11_SVT,0) + ifnull(D11_PHY,0) + ifnull(D11_TECHNO,0))/5
FROM %TABLENAME% WHERE %PKFIELD% = '%ID%'
If your fields have required flag (NOT NULL in database), you don't need the ifnull() function calls but just...

Code: Select all

SELECT (D11_FR + D11_HG + D11_SVT + D11_PHY + D11_TECHNO) / 5
FROM %TABLENAME% WHERE %PKFIELD% = '%ID%'
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply