Subtract a field from one table from a field in second table
Subtract a field from one table from a field in second table
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)
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)
Re: Subtract a field from one table from a field in second table
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
Re: Subtract a field from one table from a field in second table
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
At least that is what I am trying to do without success yet. I hope that answers your question. Thank you
Re: Subtract a field from one table from a field in second table
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?
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
Re: Subtract a field from one table from a field in second table
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
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: Subtract a field from one table from a field in second table
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?
Re: Subtract a field from one table from a field in second table
Open Full Editor. You can add and upload attachments there.
No, we cannot see images on your HDD.
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
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: Subtract a field from one table from a field in second table
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.
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 (11.53 KiB) Viewed 2587 times
-
- project_tree.jpg (51.38 KiB) Viewed 2587 times
Re: Subtract a field from one table from a field in second table
Thanks for uploading the images using the forum's attachment function. This will help us helping you.
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.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.
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: Subtract a field from one table from a field in second table
Yes understood.
I hope the images have sent will help you arrive ata working Query for the calculated field
I hope the images have sent will help you arrive ata working Query for the calculated field
Re: Subtract a field from one table from a field in second table
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
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
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: Subtract a field from one table from a field in second table
Hi,
maybe I did not get all the issues, but from I would expect is that if you add
as SQL query in the calculated field for kgs_diff.
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%
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.
Re: Subtract a field from one table from a field in second table
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.
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.
Re: Subtract a field from one table from a field in second table
Hello pböttcher
You have helped me before a while back. Thank you - I’ll give that a go
You have helped me before a while back. Thank you - I’ll give that a go
Re: Subtract a field from one table from a field in second table
trevorm wrote: ↑2022-10-19 18:44With 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
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: Subtract a field from one table from a field in second table
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.
I am very conscious that I am at a very basic level, which must be tedious for you you.
Re: Subtract a field from one table from a field in second table
I am sorry but are not able to help me
Re: Subtract a field from one table from a field in second table
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
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.
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);
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
Re: Subtract a field from one table from a field in second table
Thank you so much, I will try it out. Thank you for your time
Re: Subtract a field from one table from a field in second table
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;
}
function rec_weight_before_insert(&$data, $memberInfo, &$args) {
return TRUE;
}
function rec_weight_before_update(&$data, $memberInfo, &$args) {
return TRUE;
}
Re: Subtract a field from one table from a field in second table
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;
}
function rec_weight_after_insert($data, $memberInfo, &$args) {
return TRUE;
}
function rec_weight_after_update($data, $memberInfo, &$args) {
return TRUE;
}
Re: Subtract a field from one table from a field in second table
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
Re: Subtract a field from one table from a field in second table
Unfortunately it did not work
I attach an image of the lines I have inserted.
I attach an image of the lines I have inserted.
- Attachments
-
- rec_weight.jpg (73.07 KiB) Viewed 2320 times
Re: Subtract a field from one table from a field in second table
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