book of account CCv5

Hi all,

I have a specific example.
I need to store an book of account to table.
it is easy, every day I have to store some values to columns (some are as income and some as expense) and one column will be a saldo.
of course I have to count with saldo of previous day (add up income to saldo and substract an expense from saldo of previous day) - is is easy too.
but, I if I notice that I have a mistake 5 days ago and I repair the record of this day, them the saldo will not correspond with reality of next days (because previous day was repaired).
How to re-count the saldo of next days?
thx..

Hi homeopat,

First I'd recommend that you don't so book-keeping in this way! Look at using a proper accounting system (I like Xero).

Second, don't keep a calculated balance in the table - you can never be sure that it is up to date. Record the individual transactions and calculate the balance when you need it.

If you *must* do it this way then add a MySQL query to your forms so that **every** time a record is changed, all the 'saldo' calculations are re-done.

Bob FCMA MCT

Hi Bob,
thx for reply.

it will not be a full accounting system, so no need to use a more sophisticated solution, but thanks for the tip.
we only want to record daily turnover (cash, credit card payments, payments to the bank, other expenses) and of course also need to record the final balance due to the previous days. Do you understand me?
example:
Day 1: I received 100 and so balance is 100
Day 2: I received 500 and went out 200 so 100 + 500 - 200 = 400 balance
and so on.
It would be complicated to calculate the balance of the previous income (eg. 3 months back) and the expenditure of everyday writing than if I found the balance of the previous day from the table and he attributed the current income and expenditure.

But I understand you how do you mean it.

thx

Hi homepat,

It would be complicated to calculate the balance of the previous income (eg. 3 months back) and the expenditure of everyday writing than if I found the balance of the previous day from the table and he attributed the current income and expenditure.

True - but you have absolutely no way of knowing if the previous days' balance is correct. Don't do it like this :-(

Bob

I figure out counting of saldo.
php functions:

model.column: if (!isset($saldo)) {global $saldo,$plus,$minus};$plus="some columns ...";$minus="some columns ...";$saldo=$saldo+$plus-$minus;return $saldo;

Thats works ... If I use ascending order. But I need descending order and then saldo isnt descending but ascending.
any idea? I need show the newest data at top, so on the top have to be actually saldo.
thx

Hi homeopat,

You might be able to get the final saldo from a DB query and then work your code backwards to calculate the earlier saldos??

Bob

yes, but how backwards? I am not sure, how to do it in table display type, because table is generated from top to down (forwards)
I think that I cannot use a display type:Table but custom code in this case?

thank you

Hi Homeopat,

If you get the final saldo from a DB query then you can work out the previous ones by deducting income and adding expenses from each following row.

Or, as you suggest you could use PHP to load the data and process is before displaying.

Bob

I understand, but Can I do this by table display mode .. put this code to php functions section?

Hi Homeopat,

I think so but I don't know much about how CCv5 works yet.

Bob

Hi,

but Can I do this by table display mode .. put this code to php functions section?

I think so yes, did you try it ? the code must be in one line.

The way CCv5 works is that, you define some fields (or aliases) to be listed under the "Columns list" box, then define the outputs of these fields in the other boxes config, then you can use your field (alias) in the custom mode to display the output, or just use table mode to have them automatically formatted for you!

Regards,
Max

Hi Max,

I have In column list:

zakladni.saldo: Saldo

in php functions

zakladni.saldo: if (!isset($saldo)) {global $saldo;global $prijmy; global $vydaje;} $prijmy=$row['zakladni']['hotovost_kc']+$row['zakladni']['hotovost_euro']*$row['zakladni']['kurz_euro']+$row['zakladni']['platebni_karty']+$row['zakladni']['poukazy_p'];$vydaje=$row['zakladni']['poukazy_v']+$row['zakladni']['drobna_vydani']+$row['zakladni']['mzdy']+$row['zakladni']['postovne']+$row['zakladni']['cestovne']+$row['zakladni']['odvod_kb'];$saldo=$saldo+$prijmy-$vydaje;return $saldo;

this works fine, when ascending order. I am not sure how php functions work, I think that zakladni.saldo row in php functions is called for every record of the table separately. So I am not sure how to get final saldo at first place and then this saldo print on page by decreasing of saldo of the day from final saldo.
maybe picture help to understand ... I need saldo order same way as data in table
[attachment=0]ScreenShot601.jpg[/attachment]

thx

Hi,

Did you manage to solve this ? I just can't understand what you are trying to do here, the php function will do some processing on the cell value, if you try to do ordering then use the order box!

Regards,
Max

Hi Max,

No, I didnt.
If u take a look at picture, data are ordered by date DESC (I am using order box) and I have problem with column saldo. this values are counted with php functions.

saldo = saldo of previous day + incomes - expenses

problem is in order of saldo values (take a look at picture. value 12295 should be for date 20.7.2014. not 15.7.2014.
So I dont know how change order in same direction like a date

And how do you calculate the previous day saldo ? the functions are processed per row, and since you have them ordered by date DESC, then you will always have the next day processed first.

code of saldo is

    global $saldo;
    $saldo = $saldo  + $incomes - $expenses;

and it works... but if I change date order from asc to desc, then order of date is changed, but order of saldo didnt
is there some way how set it up?

Hi homeopat,

Then you will need to change the logic so that you can still start at the top of the list and work down - this will presumably require an extra database query to get the saldo at the beginning of the page.

Bob

Hi Bob,
thats funny, I know, but how?🙂 I dont know, how set up it in CC🙂

Hi homeopat,

I don't know how to do it either but I think it's probably can be done with some thought.

Bob

this will presumably require an extra database query to get the saldo at the beginning of the page.

I think that you can loop and modify the data in the $rows variable in the header section, but only the $rows of the current page are accessible, so if you need rows data from other pages then you will need a new query!

Regards,
Max