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: 9
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 5664 times

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

Re: Personal budget app - calculated fields (totals)

Post by pbottcher » 2020-02-11 20:45

Hi,

can you post the SQL you use for the picture shown. Is the transaction date a date field?
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.

User avatar
csultan
Posts: 9
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 5599 times

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

Re: Personal budget app - calculated fields (totals)

Post by pbottcher » 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.
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.

lectura
Posts: 28
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