Recalculate Auto Enter field Function

Idea created by mrwatson-gbs on Nov 26, 2015

    The Problem


    When one corrects or updates the calculation of a calculation field (for example in a deployed solution) FileMaker helpfully and automatically recalculates all  the existing data for you. Fine and good, however, if the field happens to be an AutoEnter (or a Lookup) field FileMaker does NOT recalculate the data for you.


    This often presents a bit of a problem to the developer, who has various options - all of which are complicated, time-consuming and error-prone:


    1. change one of the fields that are referenced in the new calculation, triggering it to recalculate
    2. copy the new AutoEnter calculation and use it in a Replace Field Contents command - either manually or in a script


    This requires that every correction of AutoEnter field calculations (in working customer databases) has a custom solution for correction of the data.


    Where this is scripted the biggest disadvantage is that the business logic is duplicated and decentralized! YUK!



    The first Idea (New Script Steps)


    A new script step to trigger a field to recalculate:


    Recalculate field [ Current Found Set ; Field {by Name} ]


    This triggers the specified field to recalculate using the


    • With the option [X] Current Found Set selected it operates on the whole column (like Replace Field Contents)
    • With the option [  ] Current Found Set deselected it operates only on the field of the current record (like Set Field)
    • There could either be (my preferred solution) an option to Recalculate Field by name, or a separate command.


    The second better Idea (New Function)


    Instead of a new script step, a new function seems to offer a less complicated solution to the problem:


    RecalculateField( Field )


    The function does not return the CURRENT value of the field, rather it returns the RESULT OF RECALCULATING the field.


    Thus this function can be simply combined with the existing script steps (Set Field / Set Field by Name / Replace Field Contents), e.g.


    Replace Field Contents[ Products::Price ; RecalculateField( Products::Price ) ]




    • Updating AutoEnter fields becomes (virtually) as easily as updating calculated fields
    • The data correction script becomes trivial
    • Above all: Business Logic in Field definitions REMAINS in Field definitions
      • DB code  has less inter-dependencies
      • DB is more maintainable
      • Developer is more productive
    • Easier maintenance
    • Easier updates



    Use Cases


    1. Maintenance
      1. Correcting field definitions in deployed solutions
    2. Update
      1. A change is made in the data model in the master database
      2. When a customer upgrades to the latest version of the database the update-process to import customer data into the new solution need only know WHICH FIELDS need recalculating BUT NEEDS TO KNOW NOTHING ABOUT THE CALCULATION ITSELF