You can use your existing relationships but put calculations in place of your current data field in Budget.
Current Balance could be defined as:
Sum ( Budget 2::Amount ) - Sum ( Budget::Amount)
The Sum and other aggregate functions such as Average, Max and Min, when used in this format reference the designated field for all the related records to compute a total, average or other such value.
Although you could use calculations in the Budget table that refer to the Transaction tables, the results will not be stored. At first, this will not cause any issue. In the long run, the calculations will visibly take more and more time to return a result.
As an alternative to calculation, you can make use of scripts.
Depending on the transaction rules you have implemented, there are three possible scenarios that will affect the totals: the addition of a transaction; the modification of a transaction; the deletion of a transaction. In the case of modifications and deletions, this means you need to remember what the original transaction amount was before allowing the user to change the value.
Whenever we say script, we need to keep in mind the user interface. How will the script that keeps your records straight be activated without fail by the user?
Forcing a user to enter a new/modify/delete transaction window is one approach that works. It needs to ensure a user can not simply cancel the action by, say, simply closing the window. There are a number of scenarios to look after in the scripts. There is a downside to this approach: it is more cumbersome to the user who needs to perform regular data entry, as it normally forces the user to click on buttons.
Using script triggers tied to a transaction amount field is another approach that works. Remember, however, that script triggers only work when a user performs an action on the object that contains the script trigger. Importing data, for example, will not launch the script trigger. In that case, you would need a script that updates the totals based on the imported data.
As a final note, there is no script trigger for record deletion. If a user can delete a Transaction record either by deleting a record in a layout belonging to the Transaction table or by deleting a portal row of a portal displaying records of the Transaction table, then your totals will no longer be correct. You will need to tie the record deletion to scripts via buttons. This is normally the expected approach.
I hope this helps!
Phil and David, thanks so much for the responses. They really help and I appreciate them.