previous balance - current balance

[laneo]

Hi all,

I've been trying for a week to accomplish something that i suppose its easy, but i just cant make it work.

I have a layout with 2 tables, on for incoming $ and another one for expenses. I also have a total amount money (in a global field), and a balance of the incomes and expenses for that particular field.

What im trying to do is to have a field saying how much money was there before those incomes/expenses, and another one saying how much was left.

I just cant find a way to do this, any ideas ?

Hope my english was clear enough.

Thanks for your time

[BFrost]

Note: This requires the GetNthRecord function. I can't remember when that started.

Create a global numeric field for Starting Balance.

Deposit and Withdrawal entries are calculation fields where: anything greater than zero in the Amount data entry field is a Deposit, anything else is a Withdrawal.

Create a field for numbering records in Serial order when all records are present and a script to reserialize before reporting.

Create a record balance field to distinguish a starting balance from any other deposit or withdrawal that looks like this:

If ( RecNo = 1 ; Starting Balance; 0 ) + Deposit + Withdrawal

Create a running total field summarizing this balance field at each transaction.

Define a calculation field identifying the Statement Period for each record concatenates the year and month of each transaction with the day number for the end of statement period. So Statement Periods ending on the 15th of every month would have numerical values in the form YYYYMM15: year(date) * 10000 + month(date) * 100 + 15.

Create a self-join relationship by statement period specifying related records in date order with credits appearing before debits.

Create a field that counts the number of records in the current Statement Period.

Create a field using the GetNthRecord function that gets the running balance value for the first related record in the statement period MINUS whatever the amount is for that record.

Create a field using the GetNthRecord function that gets the running balance value for the last related record (record count number)in the statement period.

Run the reserialize script to assure records are in the proper order--really just to make sure the starting balance is the first record in the table so it is automatically included in the running balance. I suppose you could manually flag the the starting balance period and avoid reserializing.

I might have overlooked something here but give it a try and we can refine as we need to.

Alternatively, keep in mind you could total all deposits and all withdrawals in a statement period and have a calculation field that identifies the previous statement period. Create a relationship from the current statement period to the last statement period and catch the running total for that way.

Keeping track of either method can give you headaches. :crazy2:

[Søren Dyhr]

I've just seen this reply, and must caution by the approach ... it scales very badly due to chained dependencies, I've just toyed with something similar in this thread:

http://fmforums.com/forum/showpost.php?post/324086/

...there is in my humble opinion no way you can make this scale without storing each balance in each transaction - The Luca di Pacioli method, although we opposed to back then operates with negative figures, so one single coloumn is all it takes.

--sd

[laneo]

Hey, thank you both for the help.

The GetNthRecord was just what i was looking for.

My db is just for daily expenses/incomings. So i solved this by just looking on the previous record balance. (I dont need to define any periods... its simpler than that).

Anyway, im still stuck with one thing. How can i get the records sorted by date and serialize them from this result ?

I thought that i could do that by sorting the records and setting a calculation field ID_by_date using the function: Get (RecordNumber).

But i cant get it to work: The content of ID_by_date doesnt change after i sort the records.

Any ideas ?

Thanks again

[Søren Dyhr]

I think you need to start somewhere else, otherwise will you chase gremlings for some unforeseeable future! When you write:

I have a layout with 2 tables, on for incoming $ and another one for expenses.

...is it structurally wrong - because the two tables contains a double set of identical fields. This should be solved via an attribute to designate which is which!

http://www.tinyurl.dk/6962

http://fmforums.com/forum/showpost.php?post/317001/

--sd

[BFrost]

Good catch, Søren Dyhr, I do have a stored balance value that comes from a lookup referencing the running balance. Self join from within the same record to get the balance value. Relookup was forced by resequencing records.

The getNthRecord function by statement period comes up with the right balances as long as the starting balance is the first record in the same table as all other deposits and withdrawals. I just verified that in my own file comprising 64 statement periods. There is no chained dependency, just a running balance retrieved by the GetNthRecord function on "Amount' with records sorted (as defined in the "Statement Period" relationship: deposits before withdrawals within date).

I agree one column is all you need for Amount as long as the first record the Amount is the starting balance. However, for display purposes and to mimic certain banking statements, I like to parse Deposits & Withdrawals with (unstored) calculation fields.

laneo, what both Søren Dyhr and I are saying is the fields and table for deposits and withdrawals should be the same, the distinction can be made by the arithmetical sign at the time of data entry in amount, positive (no sign) for deposits and negative (-) for withdrawals,

or,

an additional attribute field to indicate Deposit or Withdrawal.

Having an additional attribute field does require you to cue addition or subtraction by a calculation recognizing that attribute to proceed with the appropriate arithmetic when calculating a balance.

I suppose it's a mainly a matter of taste, but I like using the sign at data entry. It sounds like you're almost there anyway.

*********************************

Søren Dyhr, perhaps you could expand on the term, Luca di Pacioli method, I did a search on the forum and in google. I found no description by that name, any help?

[Søren Dyhr]

Sure:

http://en.wikipedia.org/wiki/Accounting

...and do not forget to follow the link to "double sided"!

--sd

[laneo]

Thank you both for the big help.

The movie on that link was also very helpful.

Anyway, I still want to know how to serialize sorted records. I thought that this could be done with the Get (RecordNumber) function.

Here is the explanation of what i was trying to accomplish:

[color:green]Anyway, im still stuck with one thing. How can i get the records sorted by date and serialize them from this result ?

I thought that i could do that by sorting the records and setting a calculation field (lets name it ID_by_date) using the function: Get (RecordNumber).

But i cant get it to work: The content of ID_by_date doesnt change after i sort the records.

Thanks again

[Søren Dyhr]

Anyway, I still want to know how to serialize sorted records

If you in browse mode park the cursor in the field you wish to re-serialise hit cmnd + "=" and a dialog pops up. This is the same appearing in scripting when choosing:

http://fmhelp.filemaker.com/fmphelp_10/en/html/scripts_ref1.36.54.html

Caution - It is in way multiuser environment friendly by it's tampering with the other users re-serialisations.

--sd

[Søren Dyhr]

I agree one column is all you need for Amount as long as the first record the Amount is the starting balance. However, for display purposes and to mimic certain banking statements, I like to parse Deposits & Withdrawals with (unstored) calculation fields.

There is an excellent technique to borrow from here:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

...where debits and credits attributes can dictate the ushering, and not just for all transactions, distinctions between cash bank transfers could be facilitated as well.

--sd

[laneo]

If you in browse mode park the cursor in the field you wish to re-serialise hit cmnd + "=" and a dialog pops up.

I thought that my question was going to be misunderstood as soon as i wrote reserialize.

I dont really want to do a manual researialization, what i want to do is to sort the records and get them serialized according to the current sort (dynamically).

I read that the function Get (RecordNumber) returns the number of the current record in the current found set.

And i thought that a Sort is a way to create a Found Set, so this way i could have a serial for each record that changes dynamically as a result of a Sort.

So i made a calculation field with the get (RecordNumber) hoping that when i sort the records i would have that field with a serial number in the current sort order.

But this isnt working. Should it work or Get (RecordNumber) is not the function im looking for ?

Thanks again for all the help.

[Søren Dyhr]

Yes all the status functions (the ones not taking external parameters) would give you this, provided you make the calc'field unstored.

But if it is supposed to be shown in a portal, is @@ on the line in the portal a much better choise... but I still fail to see what this has to do with you most urgent problem, lack of relational structure.

And i thought that a Sort is a way to create a Found Set, so this way i could have a serial for each record that changes dynamically as a result of a Sort.

Queries makes found sets, these can either be scripted finds or relational fitlering. What is it you think qualifies sortings to do any thing but deal with already established found sets?

Some sort of counting by type could however make some sense, in connection with sorts, but that is exactly what I did in my initial linking to a newly made template. The template utilizes Michail Edoshins "Fast Summaries" tecnique, well here is another one using the same approach:

http://www.kevinfrank.com/download/kf-fast-summary.zip

But basicly should large sets be dealt with via genuine summaries:

http://www.tinyurl.dk/6994

--sd

[BFrost]

Here's an easy way (attached)

Running_Balance.fp7.zip