Design of a financial tracking database

[laudaudio]

Anyone have suggestions on how to define the relationships between multiple tables (which represent individual account ledgers using table view) and how to auto create info (transfers) between them without having to use portals? I am currently thinking through the star join approach.

Additionally I realize this has been done before and there are probably templates and posts concerning it but I'm having trouble using the right key words for a search. If anyone has suggestions about good key search words (or existing templates) that would equally be helpful.

Thank you.

[Dr. Evil]

I may be able to help if you are still interested.

Tell me the process of your tasks, and what records you are tracking.

Tell me a little more about what you are using this database for.

Take care!

[LisaE]

laudaudio, you're treading in an area that I'll soon be asking about as well, so I hope you don't mind if I piggyback a little.

Dr.Evil, what sort of example might you have? Would it suit my use?

Pull up a chair while I expand...

I'm frustrated with the personal home finance software. I'm on a Mac, and in the past, Quicken has been very good to me, but I'm now in the UK...no Mac version British-style personal-use.

I've also got a newfangled all-in-one phone and I want to synch between this and my desktop.

Heck, I love FileMaker, so the solution was obvious: I've got to roll my own. Export/Import the csv's and momma's happy.

Well, I'm getting there. And it looks mighty fine so far. But I'm taking it one step at a time. One account at a time.

So yes, once I have this one account sussed (minor glitches), I'll be ready for the step laudaudio's wanting when I start relating the other accounts.

But for my question (the minor glitch), maybe it's an easy solution. Heck, I can figure a work-around, but there's got to be something under my nose that's obvious.

It's to do with running balances. That bit's easy, no probs. If I open the account filling in the field I lovingly call "payee" with the content: "Opening Balance" and enter a number in the "amount" field, it begins with...hey! the running balance matches my statement perfectly! And, it ends with (he he, so great) the same ending balance!

But that fun is short lived.

My next statement won't carry over the opening balance, and the ending balance certainly doesn't match my ending balance. The only way that I can balance my account right now is to sum the debits and sum the credits and see if they match what the statement says.

I call up an individual statement by doing a 'find', BTW. Then it just lists those transactions that are on that current statement...hence why it's not recalling any of the past information. Ending balance would be okay if I didn't enter anything until I got my statement, and left all the other receipts to sit until the next statement. Not an option. Opening blance is just plain to be ignored.

So.

How do I get an acurate opening balance and ending balance? I can base it on a date because I enter two dates...the first is the transaction date, and the second is the reconciled date. (See, that's how I know I've matched it to the statement, and the statement dates are all pretty predictable in the pattern.)

[Dr. Evil]

Humm.... sounds like you got your tables and relations set ok...

You just need to have an easy way to track your totals and such... Correct?

This may or may not help... But here is what I have done for my accounting database.

I have tables:

- accounts

- transactions

- and others... that don't matter for this point

Accounts Table;

holds my accounts information for say my business and my personal savings, checking, ect.. All transactions are tied to one of these accounts. I have built relationships to the Transaction table to label the transaction a deposit, withdraw, or reconcile. Reconcile had it's own table relation due to the fact that a reconcile can be a positive or a negative.

Transactions Table;

This is a line item table that tracks ALL transaction activity.

Reconciles, deposits, withdraws. Each transaction is tied to an Account as I mentioned, so this makes it easy to call up a single Account.

So... now that ALL activity is in one place "transactions" this is where I go to do all my reporting and registry management.

Sorting through allllllll these transaction records is a major pain if you don't have a system as I feel you know.

My solution to this hassle was to make a quick global set find script. With this I choose the account I want to see as well as the date range. I have attached a .jpg snapshot of my script.

This script requires the following additional global fields...

- glb_account_id

- glb_date_from

- glb_date_to

Ok... now. I placed these global fields in the header of a list view in my transactions table.

Attached to the glb_account_id i have a value list of all available accounts, User may choose which ever interested in viewing. Same goes for the to and from date globals, the user just enters the desired date range.

The script uses these global fields to set the find.

The key field in this script is your mark date, which is your creation or transaction date in your transaction table.

The script goes to find mode, inserts a calculated result in the mark date, sets account id and performs a find.

The clc result looks like this:

TRS_transaction::glb_date_from & "…" & TRS_transaction::glb_date_to

The "…" is the symbol for range, if you did not know.

So after the script has ran the user is looking at a specific account and a specific date range.

The date range and be anything you want. By monty, quarter, or to match your banks statement period. I go by my banking statement period, just easier for me. But... when I create a reconcile transaction a positive or a negative i only create ONE reconcile per month regardless of the statement period i work from. I do this just to keep it from duplicating and confusing matters. Now that User has desired found set, the use of Summary fields work great. Once my account balenced for a complete statement period I print and archive.

So in my database you will find a reconcile record for EVERY month, even if it is ZERO or NULL.

I just rattled off a bunch of jibber, so if I do not make sense feel free to ask me to clear it up. Or if none of this even comes close to helping you, just disregard.

Well, hope this helps you out.

Edited August 16, 2005 by Guest

[The Gardener]

Hello,

This is a topic I am much interested in, also.

When you mentioned Quicken, I remembered that I have an old version five set of files distributed as freeware by Clickware. It is very much a Quicken style method of tracking your bank accounts, flow of funds between accounts and transactions. Has some nifty reporting features, too. I think the files total over one meg [i will have to look] so it must be too big to post here. If you or anyone is interested, perhaps you can post here and it can be worked out how to distribute the files.

[LisaE]

Randy, I think I reached the same end result using kinda-sorta the same theory, but different workabout. I'm completely self-taught and a bit dense, so I go the long way around. Since posting I've been on the case almost nonstop. I'll probably strip your coding apart in due course. But I did manage to figure out one way to do it and I'm chuffed. So, thanks for taking the time...truly appreciated!

Ugh, this is reeeeally embarassing, but you showed me yours, so I should show you mine. (Yes, my field names are unconventional.)

account transaction as each value

Calculation (Number)

If ( payee ≠ "Opening balance" ; Sum ( transation amount ) ; 0 )

account all transactions

Summary (Number)

Total of account transaction as each value

latest balance

Calculation (Number) Unstored

first opening balance + account all transactions

I did basically the same thing for number of transactions. Oh, and they're global, yes.

Okay, NEXT dilemma: I want to grap an up-to-date currency conversion off the Internet and have it fill in automatically so all my figures adjust automatically. B)

Edited August 19, 2005 by Guest