Personal budget app - calculated fields (totals)

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
csultan
Posts: 2
Joined: 2020-02-09 19:40

Personal budget app - calculated fields (totals)

Post by csultan » 2020-02-10 07:59

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 301 times

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 711
Joined: 2018-04-01 10:12

Re: Personal budget app - calculated fields (totals)

Post by pböttcher » 2020-02-11 20:45

Hi,

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

User avatar
csultan
Posts: 2
Joined: 2020-02-09 19:40

Re: Personal budget app - calculated fields (totals)

Post by csultan » 2020-02-12 09:53

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 236 times

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 711
Joined: 2018-04-01 10:12

Re: Personal budget app - calculated fields (totals)

Post by pböttcher » 2020-02-12 20:05

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.

lectura
Posts: 23
Joined: 2015-01-10 13:29

Re: Personal budget app - calculated fields (totals)

Post by lectura » 2020-02-14 15:36

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

Post Reply