Page 1 of 1

Personal budget app - calculated fields (totals)

Posted: 2020-02-10 07:59
by csultan
Hi guys!

I'm a newbie in programming and i'm trying to work with AppGini for a personal budgetting app, maybe you can help me with this issue: I have a transactions table (id, transaction date (year, month), category_id, ammount, details) but also i have a summarize table (id, year, category_id, and jan, feb, march...dec as calculated fields) where i need to make some totals, ex: total ammount of tranzactions for (year+category). I tried some SQL statements in "calculated fields" but it gives me the same total on every row. Can you help me hoe to make this calculated fields to work for a row (year, month, category id, jan, feb,...,dec) ?

Thank you!
monthly_totals_calculated_fields.jpg
monthly_totals_calculated_fields.jpg (29.36 KiB) Viewed 5663 times

Re: Personal budget app - calculated fields (totals)

Posted: 2020-02-11 20:45
by pbottcher
Hi,

can you post the SQL you use for the picture shown. Is the transaction date a date field?

Re: Personal budget app - calculated fields (totals)

Posted: 2020-02-12 09:53
by csultan
Hi, this is the statement for month=1 (it seems that calculated sum(...) is for all records in sumarize table ("centralizator" in my case)

[ trazactii = transactions table
centralizator = sumarize table
an = year
categ = category ]


SELECT
sum(tranzactii.tr_suma)
FROM
tranzactii,
centralizator
WHERE
tranzactii.an = centralizator.an
AND tranzactii.categ = centralizator.categ
AND tranzactii.luna = 1

I'm a beginer, so please be gentle :)
Maybe my table structure is not so good

-and Yes, date in tranzactions table is a date field - in tranzactions I added fields year (translated "an") and month ("luna") extracted from tranzaction date --> an = year(tr_data), also luna = month(tr_data)
tables_structure.jpg
tables_structure.jpg (21.19 KiB) Viewed 5598 times

Re: Personal budget app - calculated fields (totals)

Posted: 2020-02-12 20:05
by pbottcher
Hi,

hmm, I would not expect it to do so. Can you post some sample values where you can show that it does not work correctly.

Re: Personal budget app - calculated fields (totals)

Posted: 2020-02-14 15:36
by lectura
If i got you right you want item 'id' of materiale si dotari to have all totals for all entries entered for months. if so, you need to have table for 'items' with field id, item, total
then have another table 'transaction' for entries as a child of table of items, so that it pick the 'items' from it, it will have 'item' as a lookup, 'month', ' amount'

then you do calculation of items table 'total' as
sum of amount leftjoin with with items table
this will do addition of all entries cumulatively