Subtract a field from one table from a field in second table

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-18 15:50

Please can someone help me with a calculated field please.

I have tried a load of different examples which I found in AppGini and on other sites but I am clearly missing the point somewhere

Data fields are
"users" table / "init_kgs" field
"rec_weight" table / "rec_kgs" field

Result Field is
"rec_weight" table / "kgs_diff" field

So, I would like the MySQL code to perform following simple subtraction please, to be placed in query field of AppGini - Thank you.
("rec_weight" table / "kgs_diff" field) = ("users" table / "init_kgs" field) minus ("rec_weight" table / "rec_kgs" field)

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Subtract a field from one table from a field in second table

Post by AhmedBR » 2022-10-18 19:49

And what is the link between the two tables?
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-18 23:50

I’m not sure I follow what you mean. I will explain what I am doing. When a user signs up, part of their sign up is to add their current weight stored in field “init_kgs” in the “users’ table. Later as the user progresses through training, they record their weight again and again stored in field “rec_kgs” in “rec_weight” table. Each time they record a new weight, it is subtracted from the initial kgs to show weight loss and this is stored in "rec_weight" table / "kgs_diff" field.

At least that is what I am trying to do without success yet. I hope that answers your question. Thank you

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Subtract a field from one table from a field in second table

Post by AhmedBR » 2022-10-19 08:31

I understand, but there must be a link between the two tables in order to do what you need.
It is a relationship data, there must be a connection (a relation) between the tables.
Here is an example:
rec_weight has a field called user_id that refers to users table (lookup field) so that we can know the this weight belongs to that user.

Can you post a photo of your rec_weight and user tables, which fields do you have?
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

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

Re: Subtract a field from one table from a field in second table

Post by jsetzer » 2022-10-19 09:16

AhmedBR wrote:
2022-10-19 08:31
... but there must be a link between the two tables in order to do what you need ...
Or maybe he's just relying on record ownership. Relation can then be built using membership_userrecords, which makes it a bit more complicated.

It would be helpful if you could give some more information about your model. Do you have fields for building the relation?
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

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-19 09:55

Do I need to upload the image somewhere because it will not appear in the reply by using my HD file location address between the image tags or can I email them to you?

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

Re: Subtract a field from one table from a field in second table

Post by jsetzer » 2022-10-19 09:59

Open Full Editor. You can add and upload attachments there.

No, we cannot see images on your HDD.
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

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-19 13:56

Thank you for your help again. Please find attched two images, "project_tree" and "recipes" - the two tables you requested can be seen within project_tree image. The attached images are of the whole project. I could not fit "recipies" in the same image on my screen.
I believe there are fields to enable the relationship(s) I need to be built.

The project is an exercise to learn the associated tasks in preparation for another project I am planning for my business. The project is a chopped down version of Weight Watchers weight loss application for my personal use.

Some notes about the fields:

users table is straight forward,

food_items table - "item" field is the food item name and "points" field is the value of the food item in the application calcultions.

meal_time table - "name" field is breakfast, lunch, dinner, snack

food_eaten table - "date" field is the date of the meal, "mealtime" field is taken from "meal_time" table, "food_item" field is taken from "food_item" table and "points field is taken from "food_items" table.

rec_weight" table - "date" field is the date that weight is recorded throughout training, "rec_kgs" is the recorded weight in kgs to dec places and "kgs_diff field is "init_kgs" field from "users" table MINUS "rec_kgs" field from this table

This is a new projects and the tables are note populated except for a few test records. If you recommend using different fields e.g. username instead of user_id, they can be changed.

With reference to your comment about seeing files on my hard disk, I do realise you cannot see these files, you misunderstood me. What I meant but perhaps was not clear about, was that including the link to these files on my hard disk between image tags in the reply form did not load the images into the reply form window like it had when you sent an image in your previous reply.
Attachments
recipes_table.jpg
recipes_table.jpg (11.53 KiB) Viewed 2587 times
project_tree.jpg
project_tree.jpg (51.38 KiB) Viewed 2587 times

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

Re: Subtract a field from one table from a field in second table

Post by jsetzer » 2022-10-19 14:37

Thanks for uploading the images using the forum's attachment function. This will help us helping you.
With reference to your comment about seeing files on my hard disk, I do realise you cannot see these files, you misunderstood me. What I meant but perhaps was not clear about, was that including the link to these files on my hard disk between image tags in the reply form did not load the images into the reply form window like it had when you sent an image in your previous reply.
Sorry, we cannot see your local images even if you put references to your private local files here. Browsers on the public Internet cannot just find and show files stored on some remote computers' hard-drive unless you share them in a cloud service or publish them in a file service, fortunately, for security reasons.
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

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-19 14:54

Yes understood.
I hope the images have sent will help you arrive ata working Query for the calculated field

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

Re: Subtract a field from one table from a field in second table

Post by jsetzer » 2022-10-19 15:27

For calculating the difference you will need to find the previous rec-weight first and read that record's rec_kgs. Due to the specifics of calculated fields, this will lead to unexpected results in case you insert rec-weights having date in between existing records.

If I was you I would not use a calculated field but a readonly field and do calculations in PHP for all records in after_insert and after_update hook. Doing it for all records sorted by date ascending ensures correct step by step calculation, even if intermediate dates will be inserted at any time later:

diff(n) = kg(n-1) - kg(n)
With n>0
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

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

Re: Subtract a field from one table from a field in second table

Post by pbottcher » 2022-10-19 16:58

Hi,
maybe I did not get all the issues, but from I would expect is that if you add

Code: Select all

SELECT users.kgs_diff - food_eaten.rec_kgs from food_eaten left join users on food_eaten.user_id=users.id where food_eaten.id=%ID%
as SQL query in the calculated field for kgs_diff.
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.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-19 18:44

With respect I do not understand why you need to work from the previously recorded weight. We are not looking for a measure by measure weight loss. We are only interested in what the total weight loss is.

This gives the user a constant positive. The user does not heed to know they have not lost weight - they can work that out our themselves. The ongoing current weight loss is fine.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-19 18:49

Hello pböttcher
You have helped me before a while back. Thank you - I’ll give that a go

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

Re: Subtract a field from one table from a field in second table

Post by jsetzer » 2022-10-19 18:55

trevorm wrote:
2022-10-19 18:44
With respect I do not understand why you need to work from the previously recorded weight. We are not looking for a measure by measure weight loss. We are only interested in what the total weight loss is.

This gives the user a constant positive. The user does not heed to know they have not lost weight - they can work that out our themselves. The ongoing current weight loss is fine.

Sorry, if I did not understand your requirement right. I thought it would be best to correctly and automatically calculate the whole history, valid even for unexpected user input.
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

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-19 19:22

Please do not apologise. Your suggestion is a very good one but I am conserned that the project will move faster in development than I can follow. If we could just track change from the original first input weight. Then, if you you have time to demonstrate to me, the way to change that to monitor unexpected user input. e.g. weight gain. Thank you

I am very conscious that I am at a very basic level, which must be tedious for you you.

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-23 14:43

I am sorry but are not able to help me

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Subtract a field from one table from a field in second table

Post by AhmedBR » 2022-10-23 18:11

Two of best people around here where talking, so I just listen first.
I got lost in the discussion

Here is what I would do:

1. Change kgs_diff to normal field (decimal), make sure all the weight fields are decimal (good for calculation), you can also use integer if you do not care about grams

2. Place this in the after insert and after update hook in rec_weight table

Code: Select all

$initkgs = sqlvalue("Select `init_kgs` from `users` where `id` = '{$data['user_id']}' ",$eo)+0;
sql("UPDATE `rec_weight` set `kgs_diff` = `rec_kgs` - '$initkgs' where `id` = '{$data['id']}'",$eo);
The first line gets the ini_kgs based on the user.
second line do the subtraction and shows the diff, if you get a minus, he gained weight.
(code not tested, but if you get any error message, just tell me what it is, we can solve it).

That should it.
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-23 22:17

Thank you so much, I will try it out. Thank you for your time

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-28 13:18

Thank you for your help/ 'I have found the hook file and the lines you mention. Please would you guide me where the lines of code you gave should be pasted.

function rec_weight_before_insert(&$data, $memberInfo, &$args) {

return TRUE;
}


function rec_weight_before_update(&$data, $memberInfo, &$args) {

return TRUE;
}

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-28 13:21

Sorry copied wrong lines in previous post

function rec_weight_after_insert($data, $memberInfo, &$args) {

return TRUE;
}

function rec_weight_after_update($data, $memberInfo, &$args) {

return TRUE;

}

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Subtract a field from one table from a field in second table

Post by AhmedBR » 2022-10-28 13:46

Sure

Code: Select all


function rec_weight_after_insert($data, $memberInfo, &$args) {

$initkgs = sqlvalue("Select `init_kgs` from `users` where `id` = '{$data['user_id']}' ",$eo)+0;
sql("UPDATE `rec_weight` set `kgs_diff` = `rec_kgs` - '$initkgs' where `id` = '{$data['id']}'",$eo);

return TRUE;
}

Code: Select all

function rec_weight_after_update($data, $memberInfo, &$args) {

$initkgs = sqlvalue("Select `init_kgs` from `users` where `id` = '{$data['user_id']}' ",$eo)+0;
sql("UPDATE `rec_weight` set `kgs_diff` = `rec_kgs` - '$initkgs' where `id` = '{$data['id']}'",$eo);

return TRUE;

}
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-28 14:46

Thank you

trevorm
Veteran Member
Posts: 35
Joined: 2014-07-29 08:41

Re: Subtract a field from one table from a field in second table

Post by trevorm » 2022-10-28 15:30

Unfortunately it did not work

I attach an image of the lines I have inserted.
Attachments
rec_weight.jpg
rec_weight.jpg (73.07 KiB) Viewed 2320 times

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Subtract a field from one table from a field in second table

Post by AhmedBR » 2022-10-28 15:35

What exactly did not work? any error message?
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

Post Reply