AppGini Community Forums

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)

And what is the link between the two tables?

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

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?

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?

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?

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

No, we cannot see images on your HDD.

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.

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.

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

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

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.

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.

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

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.

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 sorry but are not able to help me

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.

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

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;
}

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;

}

Sure

Thank you

Unfortunately it did not work

I attach an image of the lines I have inserted.

What exactly did not work? any error message?