I really don't understand calculation fields

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

I really don't understand calculation fields

Post by urichard » 2022-12-13 16:38

Hi

Please can someone explain to me this?

Warning: Skipping calculations for field 'invoices.subtotal': This field can't be a calculated field.
Reason: Calculculated fields must be set as read only

How can i create a calculation field if I cant have the field available to enter an amount in?

The amount field is mandatory not optional but the second field which is discount field is optional, then the last field is a total of the amount minus discount but I keep getting an error that it cannot calculate if its not read only, whats the point of that?
[email protected][/color]

Kind Regards
Richard

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-13 16:54

This doesn't make any sense at all because on the documentation it says that the field shold NOT be set as read only?

https://bigprof.com/appgini/help/calculated-fields
[email protected][/color]

Kind Regards
Richard

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-13 17:30

Please help, Im really abffled here.

i downloaded the invoicing app and saw that it uses fields from another table (price * qty) to calculate the sub total but why cant I have fields inside the same table calculate?
[email protected][/color]

Kind Regards
Richard

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

Re: I really don't understand calculation fields

Post by jsetzer » 2022-12-13 17:46

urichard wrote:
2022-12-13 16:54
This doesn't make any sense at all because on the documentation it says that the field shold NOT be set as read only?

https://bigprof.com/appgini/help/calculated-fields
Where did you read on that page that calculated fields should not be readonly?
Calculated fields are read-only fields
It says clearly those fields must be set as readonly and it will fail under certain conditions (for example if not set readonly).
How can i create a calculation field if I cant have the field available to enter an amount in?
You have to decide: do you want to enter values manually or do you want AppGini to calculate the value automatically, based on your SQL query. A single field cannot be both: editable AND 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

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

Re: I really don't understand calculation fields

Post by jsetzer » 2022-12-13 17:54

urichard wrote:
2022-12-13 17:30
why cant I have fields inside the same table calculate?
For sure you can do calculations using fields of the same table.

table_a
  • id
    (Primary Key)
  • quantity
    (integer, required, default value=1)
  • price
    (decimal 10,2, required, default value = 0.00)
  • subtotals
    (decimal 10,2, [x] readonly, [x] calculated)
    SQL command:
    SELECT `quantity` * `price` FROM `table_a` WHERE `id`='%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

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-13 18:08

I think I'm confused here..

This is where I saw it
Conditions for a field to become a calculated field
If you are trying to set a field as a calculated field, it must NOT meet any of the following conditions

Fields not set as read only
Yes I want to enter values manually in 2 fields of the same table like Nett amount and second field tax and then the 3rd field same table I want AppGini to calculate the value automatically of the nett amount plus tax minus any discounts equals the gross final amount, the gross field I can understand shouldn't be editable Ashwell as the tax field because the tax field is also a %15 calculation addon based on the net?

For example:
10$ (editable) + (not editable as 15% tax of 10$ would be the following calculation = ((10 x 1.15)-10)) - (discount amount as editable field) = Gross (not editable as calculation field)
[email protected][/color]

Kind Regards
Richard

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-13 18:25

Hi Jsetzer,

i did exactly how you explained here
table_a
id
(Primary Key)
quantity
(integer, required, default value=1)
price
(decimal 10,2, required, default value = 0.00)
subtotals
(decimal 10,2, [x] readonly, [x] calculated)
SQL command:
SELECT `quantity` * `price` FROM `table_a` WHERE `id`='%ID%'
but still it gives me the error as before
Attachments
ID.jpg
ID
ID.jpg (86.93 KiB) Viewed 2149 times
Nett.jpg
Nett
Nett.jpg (99.97 KiB) Viewed 2150 times
Discount.jpg
Discount
Discount.jpg (94.23 KiB) Viewed 2150 times
Total.jpg
Total
Total.jpg (97.33 KiB) Viewed 2150 times
Error.jpg
Error
Error.jpg (51.56 KiB) Viewed 2150 times
[email protected][/color]

Kind Regards
Richard

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

Re: I really don't understand calculation fields

Post by jsetzer » 2022-12-13 19:08

Did you read the red errors?
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

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: I really don't understand calculation fields

Post by grimblefritz » 2022-12-13 19:21

Hmm... I posted a reply, but I think I then clicked Post Reply instead of Submit. Oops!

What I see is that you have set all three fields - nett, discount, and total - as calculated fields. I think (I don't use this feature myself) that you only need to have total defined as calculated.

Check the tabs at the bottom of your screenshots. You'll see all three have Calculated field selected.

Try setting nett and discount to Media, save, and generate. Does that get things working?

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-13 19:35

Hi Jsetzer
Did you read the red errors?
Yes i did, it says the fields needs not be required field, so i changed it to not be required then the next red error was that the calculation fields needs to be read only so i changed it to be read only and there was no more error but then no one can enter any amount in the Nett field or the discount field for it to calculate in the gross field, I hope you understand what I mean?
[email protected][/color]

Kind Regards
Richard

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-13 19:55

Hi Grimblefritz,
Try setting nett and discount to Media, save, and generate. Does that get things working?
Its not on those as you see the button is not ticked that says "automatically calculate"
[email protected][/color]

Kind Regards
Richard

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

Re: I really don't understand calculation fields

Post by jsetzer » 2022-12-13 20:06

For better understanding:

There are input fields and (calculated) output fields.

Quantity, Net, TaxRate, Discount etc. should be such input fields for user input.

Inputs must not be readonly and should be required (to avoid working with NULL values when being evaluated in calculated field formulas).


----

TaxAmount and Gross should be calculated output fields.

Calculated output fields must be readonly and not required.

They need a SQL command returning exactly one value in one row.

That SQL command (per calculated field) can evaluate fields from the same table and even join records from other tables.
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: I really don't understand calculation fields

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

... but then no one can enter any amount in the Nett field or the discount field
Those have to be input fields, not readonly, required.
for it to calculate in the gross field
That one has to be readonly and calculated and needs a SQL command for multiplying values of the other fields of that record. The SQL command has to return one (!) numeric 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

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: I really don't understand calculation fields

Post by grimblefritz » 2022-12-13 20:58

I know you don't have Automatically calculate set; however, you do have a SQL expression set for nett.

Go back into net

Set automatically calculate

Clear the SQL expression

Unset automatically calculate

Generate - the problem should be solved

AG probably needs to do a better job managing error checking if the checkbox is not set but there is still a SQL expression.

Unless there's an option to manually calculate fields at runtime. Not sure. Like I said, I don't use the feature.

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-14 06:24

Hi Jsetzer,

Thank you for your explanation it did help, I have set it up like that exactly already but still got the same issue.

The solution was Grimblefritz's comment
Clear the SQL expression
which have worked now, thanks @grimblefritz

However I see that the calculation doesn't get passed to the calculation field whilst I'm creating the invoice but only after I click on save I can then see the new total, why is that and how do I change that so it shows immediately after I enter an amount into the "Nett" field and "discount" field?

I.O.W even if I don't enter anything inside the discount field it must still pull through the Nett (input) amount into the Gross (output) field even before I press on the save button?
Attachments
Nett 2000 minus 20 discount.jpg
This is before I pressed save
Nett 2000 minus 20 discount.jpg (11.37 KiB) Viewed 2093 times
Nett 2000 minus 20 discount calculated.jpg
This is after
Nett 2000 minus 20 discount calculated.jpg (6.14 KiB) Viewed 2093 times
[email protected][/color]

Kind Regards
Richard

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

Re: I really don't understand calculation fields

Post by jsetzer » 2022-12-14 07:04

That's by design.

If you need clientside calculation you can write javascript or you may consider using the function, documened here: http://www.appgini.de/docs/Javascript-L ... ields.html
which is part of a (* commercial) extension for AppGini.

(* Disclaimer: I am the author of that extension)
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
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-14 07:11

Hi Jsetzer,

Thanks for your response.

What do you mean by this:
which is part of a (* commercial) extension for AppGini.
and this?
(* Disclaimer: I am the author of that extension)
Do i have to purchase the extension?
[email protected][/color]

Kind Regards
Richard

User avatar
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-14 07:33

Or a license to use it in my app?
[email protected][/color]

Kind Regards
Richard

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

Re: I really don't understand calculation fields

Post by jsetzer » 2022-12-14 07:38

Do i have to purchase the extension?
Yes, this is a separate commercial* product which may be purchased separately and could help you on various tasks, especially if you are not familiar with javascript nor jQuery.

Noone has to use it, as you can write your own javascript code for detecting form changes, getting values of the source fields, evaluating the results and putting the results into the target fields. There are samples here on the forum for each single step. It's just that it may be easier for beginners to just use existing helper functions and getting a working solution within seconds. It is everyone's personal make-or-buy decision.

(* Disclaimer: I am the author of the commercial extension)
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: I really don't understand calculation fields

Post by jsetzer » 2022-12-14 07:43

Just an example for a custom clientside function. It automatically evaluates when changing any source field and you will see results immediately

Code: Select all

// file: hooks/contacts-dv.js
var dv = AppGiniHelper.dv;

// calculate field "full_name"
// concat values of last_name, first_name, middle_names 
// readable version for beginners
dv.getField("full_name").setFunction(function (data) {
    var result = data.last_name;
    if (result && data.first_name) result += ", " + data.first_name;
    if (result && data.middle_names) result += ", " + data.middle_names;
    return result;
});
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
urichard
Veteran Member
Posts: 87
Joined: 2018-11-01 12:11

Re: I really don't understand calculation fields

Post by urichard » 2022-12-14 09:59

Okay Thanks Jsetzer,

Where can I purchase this commercial extension?

Also Im not good at coding so not familiar with javascript of jquery at all so will it be a step by step like in appgini helper or will it be abit more complicated?

Just wish i knew how to code already
[email protected][/color]

Kind Regards
Richard

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: I really don't understand calculation fields

Post by grimblefritz » 2022-12-14 12:51

urichard,

You see now why I don't use the calculated fields -- they are server-side SQL calculations, not client-side runtime calculations. I find I almost always need the calculations on the client side in real time.

As to Jsetzer's suggestions...

I would first invest both the time and money into the AppGini course on Udemy, which is presented by the author of AppGini.

https://www.udemy.com/course/customizin ... lications/

Even if your javascript/jquery is not so good, you will learn a lot about how AppGini works and probably improve your coding skills, too. I recommend you set up a test project and actually DO the exercises. At least for me, I learn a lot more and retain it better than if I just watch.

Jsetzer's extension is good and worth the money, but I would start with Udemy first. Then, when you do add Jsetzer's extension, you'll understand better what it is doing and also how to use it. He has wrapped most of the techniques taught in the course into easier-to-use functions, as well as added additional useful tools and options. I recommend both, but start with the course, then get the extension.

Just my opinion.

Post Reply