Global Summary Fields
I know that I cannot make a summary field global, but I am trying to find a way to accomplish calculations based on un-related fields.
I have three tables, two of which are related and one that is not. I would like to be able to use summary fields from each table to figure out a balance.
Table 1 is an Orders Table - Order amounts are input with a total in a summary field sumOrders
Table 2 is an Sessions Table - Session fees are input with a total in a summary field sumSessions
Table 1 and 2 are related by a Clients table, so these two work fine.
Table 3 is an Expenses Table - Expense amounts are input with a total in a summary field sumExpenses
I would like to add the sumOrders and sumSessions together and then subtract the sumExpenses.
I have tried several ideas already, one of which was to place all the money data into one table and then just create different layouts for each of the sections (Orders, Sessions and Expenses). The problem I run into here is when I want to produce a list of Expenses with a Grand Total on the bottom along with a Net Income (calculation from above) I got blank spaces where the other records where (Orders and Sessions). I then performed a find to only show the Expense records, but then the Net Income only calculated the records found and not the income from the other two tables.
I also tried to make a calculation field that just equaled the Summary field (GlobalOrder = sumOrders). This partially works but I am sure you may have already figured out that the amounts to not change all the time, depending on what field changes.
Does anyone else have any ideas? I have pulled what little hair I have left out, so I would be glad to try anything.