I'm using this database where I have several tables which store the information about receipts and expenses.
What I would like to do is to produce a format where I put the overall balance, i.e. (Table1.Receipts_total + Table2.Receipts_total) - ( Table3.Expenses_total + Table4.Expenses_total)
The main problem here is that these tables are not related each-other and it seems very difficult to deal with this aspect in FileMaker.
I thought that there was a way to runa a low-level "raw" SQL statement, but I was not able to do that.
First, I would suggest you normalize your data structure - since whatever you do with the current one will be just a hack.
Anyway, it's quite easy to get the figure you want even without using ExecuteSQL() - just run a simple script like:
Go to Layout [ Table1.Receipts ]
Show All Records
Set Variable [ $balance; Value:Table1.Receipts::sTotalAmount ]
Go to Layout [ Table2.Receipts ]
Show All Records
Set Variable [ $balance; Value:$balance + Table2.Receipts::sTotalAmount ]
Go to Layout [ Table3.Expenses ]
Show All Records
Set Variable [ $balance; Value:$balance - Table3.Expenses::sTotalAmount ]
At this point, the $balance variable will contain the requested number, and you can do whatever you want to do with it.
Note: the sTotalAmount fields are summary fields defined as Total of [Amount] in each of the three tables.
Alternatively, you could relate the three tables using the x relational operator (i.e. create a Cartesian product relationship), then use a calculation, say in the Table1.Receipts table, =
sTotalAmount + Table2.Receipts::sTotalAmount - Table3.Expenses::sTotalAmount
But I would not recommend you place this as a "live" calculation field on the layout, as this will get increasingly slower as the number of transaction grows.