Updating 'future' records only
Hi! I am working with Filemaker Pro Advanced 13. I am creating a budget database, where each record equals 1 week of income & expenses. Records will be created in advance, meaning that every week in 2015, for example, will be included, even though we are only beginning March. My issue is in if something changes, say income increases, how to project that increase to "future" records (aka records that are already created, but are in the future date wise) without changing past records. I have attempted using global fields, creating other tables, using calculations and always come up with some sort of problem. Here are the logistics of the database so far...
Issue: having an income field that typically stays the same, but allows for the occasional increase and also only updates records that have a future date based on when income increase occurs without changing past records.
Week::Income is a stored calculation field with calculation -- If(IsEmpty(IncomeOverride);GetnthRecord(Income;Get(RecordNumber)-1;IncomeOverride) // this allows the income field to automatically enter the calculation from the previous record. I realize this can be an issue if records are moved around/sorted, however since the purpose of this database is to go from week to week, I don't see a reason why the database records will be moved/sorted (as of now, anyway).
Week::IncomeOverride is a number field, set up to override the above field, and change income for that particular record. This field is set in a popover so when the income is clicked, the user can change the income.
I have tried a few things, but my brain is swirling with possibilities, and I cannot sort through them logically. Perhaps a script attached to a button also on the popover to force already created records with future dates on them to recalculate? Or perhaps adding something else to the existing calculation? Since a whole year is created in advance, it would be great to automate this process. Thanks in advance!