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: 228
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: 1807
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

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: 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

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

Moh Youba
Veteran Member
Posts: 228
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: 1807
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

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

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

Re: AppGini 5.98 complex calculation

Post by Moh Youba » 2022-01-28 22:33

target field is decimal and read only, I use AppGini calculation
Sans titre 1.jpg
Sans titre 1.jpg (98.22 KiB) Viewed 1468 times

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

Re: AppGini 5.98 complex calculation

Post by jsetzer » 2022-01-29 14:34

Just a note for saving you extra time and trouble: combination of your model and the SQL code you are using will return unexpected results for the following scenarios:
  • > 2000
Depending on the type of montant_envoye the following scenarios will also bring unexpected results:
  • < 1
    for example 0, 0.5, 0.99, -10, ...
  • Between 500 and 501
    for example for 500.25 (500.0000001 to 500.9999999)

Problems I see:
  • Your case statement does not cover every possible value. There may be gaps when using signed type or decimal/float/double types, because then there may be negative values (for example by invalid user input) or decimal values like 500.5
  • your case ">2000" returns string although this is a decimal field
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: AppGini 5.98 complex calculation

Post by jsetzer » 2022-01-29 14:43

I suggest you should check your formula by testing against the following values:
  • -1
  • 0
  • 499.99
  • 500
  • 500.5
  • 501
  • 999.99
  • 1000
  • 1999.99
  • 2000
  • 2000.01
  • 2001
Testing against values near the upper and lower borders and nearby the thresholds is recommended. You can be sure your business logic works, as soon as your formula returns results matching your expectations.
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

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

Re: AppGini 5.98 complex calculation

Post by Moh Youba » 2022-01-30 11:41

Hi sir, thank you for your help and advices

Best regards,

Post Reply