AppGini 5.98 complex calculation

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
Moh Youba
Veteran Member
Posts: 164
Joined: 2017-03-12 09:31

AppGini 5.98 complex calculation

Post by Moh Youba » 2022-01-13 19:53

Hello
I am looking to build this complex calculation, please any help.
Thank you

DEPOSIT
• IDDEPOSIT
• AMOUNT
• NET TO PAY
In NET TO PAY I want this calculation
• if amount is from 1 to 500? Add 6
• if amount is from 501 to 1000 add 7.50
• if amount is from 1001 to 2000 add 15

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

Re: AppGini 5.98 complex calculation

Post by jsetzer » 2022-01-13 20:07

As a starting point check out...

Code: Select all

SELECT CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2 
ELSE result3 
END
https://www.w3schools.com/sql/sql_case.asp
Kind regards,
<js />

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

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

Re: AppGini 5.98 complex calculation

Post by jsetzer » 2022-01-13 20:10

Code: Select all

CASE
WHEN amount BETWEEN 1 AND 500
THEN ...
"add 6" to which value?
Kind regards,
<js />

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

Moh Youba
Veteran Member
Posts: 164
Joined: 2017-03-12 09:31

Re: AppGini 5.98 complex calculation

Post by Moh Youba » 2022-01-15 01:28

Hi
Finaly got help from AppGini WhatsApp group.

SELECT

CASE
WHEN `deposit`.`amount` BETWEEN 1 AND 500 then (`amount` + 6 )
WHEN `deposit`.`amount` BETWEEN 501 AND 1000 then (`amount` + 7.5 )
WHEN `deposit`.`amount` BETWEEN 1000 AND 2000 then (`amount` + 15 )
ELSE 'check' end

FROM `deposit`

WHERE `deposit`.`id` = '%ID%'

Thank you

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

Re: AppGini 5.98 complex calculation

Post by jsetzer » 2022-01-15 13:02

(1) It seems the target field is non-numeric because you put 'check' inside.

From my experience you should avoid mixing up numeric values with strings in the same column at any cost. A column should be numeric (for calculations) or non-numeric. Of course it is possible to do so from technical point of view.

For marking deposit records as invalid, perhaps it would be better to add a status column (which can also be calculated) for indicating invalid rows.

If the column was numeric, the following SQL statement would be shorter AND would return numeric values only. As a result you'd be able to use the results directly for follow up calculations without casting strings to numbers, first. This should be more efficient and result in more consistent data:

Code: Select all

SELECT 
(amount + 
    (case when ... then 6 
    when ... then 7.5
    when ... then 15
    end))
FROM ...
(2) I still cannot see the field name of this calculated field. I guess you don't, but just in case you try to update `deposit`.`amount` field itself by using a calulation evaluating `deposit`.`amount`, obviously the value will be increased repeatedly whenever you open a record in table view or in detail view unless you append another condition to the SQL statement like 'AND NOT is_calculated'
Kind regards,
<js />

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

Post Reply