Project - Cash Flow Schedule
I have a solution that tracks PROJECTS, TRANSACTIONS (cost incurred) & COMMITMENTS (projected cost not yet incurred), in respective tables. The TRANSACTIONS and COMMITMENTS tables have a kf_ProjectID foreign key field that links them to the PROJECTS table via the primary ProjectID field. That way I can easily summarize the total cost and total commitments for each project.
I'd like to figure out a way to create a "Cash Flow Schedule", with a column for each month that calculates the total sum of TRANSACTIONS and COMMITMENTS for each given month. I currently have a spreadsheet that does this, which I'd like to imitate in Filemaker. I imagine that the only way to feasibly do this is in the context of PROJECTS, so that each row represents a project. But I'm not exactly sure how I should create the array of columnar calculations for the "totals per month". And I'm not sure if it's possible to include totals looking backward (TRANSACTIONS) and forward (COMMITMENTS), since these are tracked in separate tables.
For example, a field that calculates the total Commitments for the Current Month followed by the total Commitments for the following months, each incremented by +1 month (going out a few years). Then setting up the same kind of field, or triggering another conditional (CASE) calculation for the tame field, that calculate the total Transactions incurred in previous months.
Does anyone have a recommendation for the best way to set this up? I'd welcome any suggestions anyone might have. Thanks!