Do I need relationship, script or both?

[Martin Rice]

I'm brand spanking new at FM and working on my first app which is to track my budget and expenses.

My db has two tables: Budget and Transactions.

In the Budget table I have the following fields:

Account Name; Account Type; Budgeted Amount; Current Balance; Difference; Budget Month

In the Transactions table I have the following fields:

Date; Description; Comment; Check Number; Amount; From Account; From Account Type; To Account; To Account Type

What I want to do is increment or decrement the Budget::Current Balance field for given Accounts when I enter a transaction.

In other words, I'll enter a transaction, say for 15 dollars, from the Cash account to the Eating Out account. In the Budget table, I want the current balance for Cash decremented by 15 dollars and the current balance for Eating Out incremented by 15 dollars.

I guess I need to do this by a script, but I'm not sure. And if so, would I need to build some relationships between the two tables?

Currently, the tables are related as follows:

Budget::Account Name -> Transactions::From Account

Budget 2::Account Name -> Transactions::To Account

I do this so that I can automatically enter From Account Type and To Account Type in the transactions table. I use these for various reports.

I hope I've made this clear. I'm not too sure about the terminology yet.

Any help would be greatly appreciated.

Thanks!