Cash Flow Layout
I have a data table of financial Commitments listing pending payments to various entities with Due Dates for each payment. Each Commitment record has a serialized CommitmentID, Amount Due, Due Date, etc. I would like to make a layout that would give me a CASH FLOW chart based on these Commitments. So if I have a commitment due for $100 on Oct 1st, a commitment due on October 15th for $100 and a commitment due on Oct 30th for $100, it will show me in some kind of list view (or ideally some kind of Calendar view) that from October 1-14 I will have spent $100, by the 15th and until the 29th I will have spend $200 and by the 30th I will have spent $300.
I'm not sure how to approach this. I created a table called CashFlow with 3 fields: Date, CommitmentsToDate, zcConstant1 (a calc field that equals 1). But I'm not sure how to relate it to properly relate it to my Commitments table in order to calculate the CommitmentsToDate. I made the CashFlow layout a list view and created about 30 records for each day in the month. I have Commitment dates in the Commitments table with due dates from that month. In my CommitmentsToDate field I would like to execute a sum() function through a relationship from CashFlow to Commitments, where 2 zcConstant1 fields in each table are related and the date fields are related as such: CashFlowDate ≤ CommitmentDate... Needless to say my approach is failing and I can't seem to get the calc field to display anything. Any suggestions!???