2 Replies Latest reply on Feb 27, 2015 10:06 AM by ffdstudios

    Updating 'future' records only



      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!



        • 1. Re: Updating 'future' records only

          I would use a script to process the change. The script can find the records that need updating and use Replace field contents to update them. To make that easier to do, I'd define income as a number field and use the calculation as an auto-enter calculation that copies over a value when the record is first created only.

          • 2. Re: Updating 'future' records only

            THANK YOU PHIL!!

            I changed the calculation to an auto-enter, kept the equations the same and played around with the script.  I ended up adding another field Week::IncomeChangeDate, so a date could be entered for when the change should take place, and also allowed me to find records that occur after the Income Change Date.  Used replace field contents and it worked like a charm!  

            I appreciate your help and your quick response.  Thanks again!