which way should I do this?

[GrantSymon]

Hi,

I am building a fairly simple accounting system for myself. My accountant however, has made it a little more complicated by suggesting it be a double-entry system. To this end, I have added a Table for the 'Position at Bank' (balance) for the start of my fiscal year. I have several different bank accounts to include balances for, but I thought it best to create a single record per annum, with a separate field per account.

What I want to achieve :

I would like to have my 'position at bank' (current bank balance) be calculated and show up alongside each entry (credit or debit) in my accounts.

So, I have created a Table for debits (which I didn't have before) and I shall reference each new debit record with the name of the appropriate bank account. So that will provide my relationship with 'Position at Bank'.

The question I have is :

Is it better, from a design point of view, to have a script calculate and enter my up-dated bank balance into the appropriate record in my main accounts table, or can this be done 'dynamically' with a calculation?

The purpose being :

I would like to be able to see, alongside my normal expenses (debit) entries, the up-date to my bank balances on a 'per transaction' basis. IOW, suppose I change the entry for my Position at Bank for a given financial year ... then all the records which were relevant to that year, would have their 'Position at Bank' fields updated automatically to reflect the new starting point. Does this make sense?

I don't know if this sort of dynamic calculation can even be achieved. I guess it would require some complicated manipulation of the dates information (which I already have in every record and in all tables).

Anyone understand what I'm going on about. : Any advice much much

[LiveOak]

It is always better (if you can tolerate the time it takes to calculate) to dynamically update balances. I have not tried it, but it should be possible in FM7 to use one of the new relationship types to sum only those transactions with a timestamp (or just date) that is the same or earlier than the timestamp (or just date) of the current transaction. If this is too slow, a auto entry (which now can update) could be used to change a store balance using the same relationship.

-bd

[GrantSymon]

Hi Brent!

you probably don't remember ... but you used to help me out *years* ago on the Compuserve forums. I managed to get a reasonable level with FM as an amateur, but I have barely touched it since vs 4, once my files were good enough to work with and once I realised that I was supposed to be doing something else with my time ... i.e. earning a living.

Anyway ... thanks for the tips.

The Timestamp seems like a good idea (I've already started putting one into all my files) and I was unaware that auto entry could be updated. Cool.

I shall now start my well established (and worn) trial and error process ... mostly error of course.

Best,

[LiveOak]

Grant! I kept looking at the name (I know that guy...), but couldn't place from where.Bill Swagerty from Claris was then moderator for the Compuserve forums. I actually met Bill a few years ago when he was employed by Incyte Pharmaceuticals in Palo Alto (near Stanford University) California and I was consulting with Incyte of FM databases for genome sample tracking.

Nice to hear from you again on our own forum.

-bd

[bruceR]

Hi Grant - I also used to be on the Compuserve forum and I remember talking with you back then. Say hello to the folks down the street at Satimage ( they are the people who make the Smile applescript editor).

Bruce Robertson

[GrantSymon]

Grant! I kept looking at the name (I know that guy...),

Hah! Well, it was rather a long time ago ... like ... (whisper) 10 years? We should award ourselves prizes for even remembering!

Regarding my question ... I'm rather wondering if I'm going about this the right way. Would you say it is better for me to keep the debit and credit records in separate tables? The complication is that I have 5 bank accounts to cater for, in 3 currencies. So whatever I set up, I have to be able to do it across all 5 accounts.

The main problem being, I'm not sure how I can incorporate a credit entry, into a report in my debit file ... so that it appears in the correct place in terms of a date sorted list of the credits/debits with the corresponding bank balance next to them (which of course was the point of my first question).

Can this be done in one table, from separate other tables using portals? Or maybe it's blindingly simple and I'm just so out of touch I don't even know about it!!

Grant

[GrantSymon]

Hey Bruce!!!

so this is where all you guys got to!! I wondered where everyone went.

I thought you'd decided to lay off FM years ago. I remember I used to beta software for you ... it was pretty cool as I remember, but it used to drive you nuts debugging it.

Any of the other stalwarts around here? Darth, Cptn Caracas, TequilaDave, used to be fun on that forum. I recall we had some great discussions about all sorts of things apart from FM. The net hasn't half changed since those days.

Best,

[bruceR]

I haven't heard any of Captain Caracas (Denny Schlesinger, I think) in a very long time. That was one very class dude. The file you're talking about, i believe, is ScriptBuilder, my automated script transfer utility built in the days of Filemaker 3.

[GrantSymon]

The file you're talking about, i believe, is ScriptBuilder, my automated script transfer utility built in the days of Filemaker 3.

Exactly! (Guess it's a bit redundant these days. <s> )

Have you any suggestions on which way I should go with my file ... 1 or several tables?

Grant