BaseTableName ( fileName ; TableName ) (modular FileMaker)

Idea created by mrwatson-gbs on Nov 2, 2015

    The Idea


    A new Design function to discover the name of the BaseTable behind a table occurrence - of ANY currently open file.


    This is just one of the functions that is needed for a more modular FileMaker.




    Since FileMaker 7 the design functions have not been able to provide a complete picture of the data model. Particularly the introduction of the relationship diagram has left a meta-data-gap between the names of the table occurrences and those of the base tables which lurk behind them. In other words, it is not possible to easily identify which base table a table occurrence really points to without resorting to testing the fields which it contains, thus the benefits:


    • Particularly TOs which point to external files can be checked that the BaseTable is really the expected BaseTable
    • Updater functions will be easier and quicker to build correctly
    • Less errors will occur during update, and thus be faster
    • Customers must spend less money updating to the new version of the solution
    • Customers can update more often and thus transition to new releases of Filemaker earlier
    • Developers are happy
    • Customers are happy
    • FileMaker can sell more licenses.


    Use Cases


    1. Check Modular External Reference

    If OPTIONAL External Reference (Modular FileMaker) are also implemented in FileMaker, so that the presence of a file (optional module) can be tested, the BaseTableName function is the sister function, to test that the CORRECT file is present, with all tables correctly in place.


    2. FileMaker Solution Updater Tool


    An updater tool to import customers data from an old solution into a new solution often works by importing data from TOs pointing to data in the old files into TOs pointing to the tables in the new files. Usually this works fine, because the internal IDs of tables (pointed to by TOs) are usually consistent.

    HOWEVER, if customizing has taken place after delivery of the database, the expected and actual internal IDs of the tables may differ (Specifically: Say a file is delivered to a customer in which the last table "Test" has internal ID 122. The customer needs some extra functionality, so a new table "AAACustomerThing" is created in the customer version. This of course is given the internal ID 123. Maybe months or years later a new table is added to the master of the file - "YouCantLiveWithoutThisThang" Table (internal ID 123) and the customer flips out at the cool new Thang and asks to have it built into his old version. This is built into the customers version, and naturally gets the next internal ID, 124!

    Now, the separate updater tool has been built using two copies of the master files (in the NEW + OLD folder) and the two TOs "YouCantLiveWithoutThisThang.OLD" and "YouCantLiveWithoutThisThang.NEW" point to the BaseTable ""YouCantLiveWithoutThisThang" in the old and new files respectively - BOTH of which have the internal ID 123.

    HOWEVER, when the updater tool is used to update the customers old database to the newest version the "YouCantLiveWithoutThisThang.OLD" TO - which points to BaseTable # 123 - is ACTUALLY pointing at Table "AAACustomerThing" (which has the internal ID 123).

    In the current FM there is no way of checking this, without reverting to checking the names of fields (which make a complex tool TOO complicated and error prone).

    However, with a BaseTableName function we can simply go to a layout and then call a standard 5 line script to check the BaseTable pointed to by the TO:

    Set Variable[$PTOName ;  Get( LayoutTableName )]

    Set Variable[$TableName ; BaseTableName( Get( FileName ) ; $PTOName )]

    If [$TableName ≠ Substitute($PTOName ; [".OLD";""] ; [".NEW";""] )]

          Show Custom Function[ "ERROR - Table occurrence " & $PTOName  & " is pointing at the wrong table: " & $TableName )

    End If


    (We could also consider a similar function to return the name of the file pointed to by a TO, ... but one step at a time)


    Looking forward to your comments and votes