need to blend two tables for totals?

[Mark Reed]

Ive reached a point where I have become brain dead and cannot think how to approach something, so Im turning to you guys and gals to help kick the cobwebs out of me...

I have two tables:

Income

Expenses

The income table has all the transactions of money coming in

The expenses table has all the outgoing spending

I am looking to do two things and am drawing a blank in my head:

1. I have an entry layout for each table. I would like to put a box showing all the records for each table. I tried a portal, but I cant seem to get that working since its not connecting to a different table, it basically needs to show the records from itself in the box. I thought about a list view and making the top header the entry, but Id rather have a more condensed box like a portal on my entry screen.

2. I would like to have a layout that takes the two tables and creates something like a balance sheet - taking both and listing the records by date and doing the proper addition or subtraction to get get an ending balance.

My apologies if these are basic in nature, but Ive been looking at this for a day or two and my brain just not connecting the dots!

[LaurenKuhlman]

1. I have an entry layout for each table. I would like to put a box showing all the records for each table. I tried a portal, but I cant seem to get that working since its not connecting to a different table, it basically needs to show the records from itself in the box. I thought about a list view and making the top header the entry, but Id rather have a more condensed box like a portal on my entry screen.

In case you were unaware of this feature, list view is the standard way to do this. Sometimes for user interface purposes, it is helpful to use a portal.

On the relationships graph, duplicate the table occurrence of table your layout is based on. Draw a relationship between the original and the duplicate, choose the "X" relationship where an equal sign "=" is by default. This creates a cartesian relationship that will show all records from the "child" table on the "parent." Create a portal on the layout and base it on the duplicate table occurrence, all the fields within the portal should also be specified as from this duplicate table occurrence.

2. I would like to have a layout that takes the two tables and creates something like a balance sheet - taking both and listing the records by date and doing the proper addition or subtraction to get get an ending balance.

Here you have a bit of a flaw in construction. If these are in separate tables, they can not really be listed together. They can be summed and then subtracted from one another but it sounds like you want them all listed together with a running balance. In this case, you need to put your income and expense in one table like a transactions table.

The way I would recommend building this is by having at least these three fields on your transactions table: type (income or expense), amount and amountCalc.

Type would be a dropdown and you could do some nice things with pulling in values automatically from relationships. Amount would just be a number that the user enters. amountCalc would look like this:

Case ( 

   type = "income"; amount;

   type = "expense"; amount * (-1)

)

Your running balance would be based on this amountCalc field. Also, to create a running balance. You would need to create a separate table occurrence that looks through a less than based on the date of the transaction. Essentially, to say add everything that happened before today.

This is a lot of information but the most important part is that a "running" balance is not really possible when the income and expense transactions are in two separate tables.

Edited July 28, 2009 by Guest

[Mark Reed]

Thank you so much Lauren.

Im kicking myself for trying to keep things "too organized" and not having one table. Luckily its early enough to revamp it.