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
AppGini 5.98 complex calculation
Re: AppGini 5.98 complex calculation
As a starting point check out...
https://www.w3schools.com/sql/sql_case.asp
Code: Select all
SELECT CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: AppGini 5.98 complex calculation
Code: Select all
CASE
WHEN amount BETWEEN 1 AND 500
THEN ...
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: AppGini 5.98 complex calculation
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
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
Re: AppGini 5.98 complex calculation
(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:
(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'
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 ...
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: AppGini 5.98 complex calculation
target field is decimal and read only, I use AppGini calculation
Re: AppGini 5.98 complex calculation
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:
Problems I see:
- > 2000
- < 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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: AppGini 5.98 complex calculation
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
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: AppGini 5.98 complex calculation
Hi sir, thank you for your help and advices
Best regards,
Best regards,