Manage from Database Design Tables & Layouts (DDT & DDL)

Idea created by eric on Mar 11, 2018
    • Hemant Kumar Patel
    • BMyers
    • eric
    • optimeering
    • BeatriceBeaubien
    • Malcolm
    • Markus Schneider
    • DavidThorp

    There should be default Database Design Tables (DDT) in every solution, with default table occurrences and default relationships on the graph, as well as default Database Design Layouts (DDL). (Or substitute "Solution" for "Database" to get SDT & SDL.)
    These default tables and layouts would be every developer's new default interfaces for managing all aspects of a FileMaker solution: Database (Tables, Fields), Security (Accounts, Privilege Sets), Value Lists, Scripts, Layouts, Custom Functions, etc.


    In addition to the protected default table schema, table occurrences, relationships, and layouts,

    a FileMaker developer would be able to add the following:


    1. Unstored calculations and summary fields in the default Database Design Tables (kind of like ESS shadow tables).
    2. Table occurrences and relationships based on the default Database Design Tables.
    3. Layouts using the table occurrences and relationships based on the default Database Design Tables.


    I wonder if we could even count the number of other Product Ideas that would be immediately addressed by this idea alone, since advanced developers should then have the means to develop many of the ideas being submitted here, which are merely waiting and hoping for other developers to like and for FMI to consider. In fact, even ideas that got voted down would now be possible for the minority who liked those ideas.


    If there is any question what a DDT should look like or what fields it should include, consider the output of a Database Design Report, particularly the tables of an HTML report or the results when the XML is imported into an app like InspectorPro. If DDT records were to include creation and modification time stamps and account names, administrators would begin to have the robust means to audit database management just like any other data audit.


    DDTs would be similar to metadata tables found in Oracle such as ALL_TABLES and ALL_TAB_COLUMNS, which provide extensive structural and statistical information about tables, columns, etc. (Oracle metadata - Wikipedia) Only FileMaker's metadata tables would be far superior, because you would be able to manage the solution from them.


    DDTs would be powerful and superior alternatives to all kinds of database design and management features.

    DDTs would provide the kind of information Design Functions only begin to provide, only with greater details, with related design information from other design objects and with all the functionality, ease, and power of having data in FileMaker tables.


    DDTs would provide all the kinds of information available from Database Design Reports, but users and databases could directly interact with that informations in layouts, value lists, calculations, and scripts. Furthermore, if many of the DDT fields were editable (at least for Advanced developers), FileMaker would make a quantum leap in speed, ease, and power over database design and maintenance. Imagine if we could use our favorite DDR solution (like InspectorPro or BaseElements) to directly manage our solutions. In fact, if this idea were a reality, InspectorPro or BaseElements would already be helping us directly manage our solutions.


    For example, suppose a developer wanted to suffix all the calculation fields with "_c". He could simply find all the calculation fields (regardless of the tables to which they belong) in the "ALL_TAB_COLUMNS" table and perform a calculated replace on those field names. (Carefully, though.) But suppose you also have text fields you use in Evaluate or ExecuteSQL functions referencing those filenames, or use to generate PHP code; those  expressions would break. No problem: Use the DDT data to find (and change) all filename references in those ordinary text fields before you change them in the "all_tab_columns" DDT.


    Another example: Suppose an administrator needs to quickly audit who has access to a Social Security Number field and to calculations and scripts that may also reveal its contents, and must quickly correct all of several privilege sets with that access. Instead of navigating each of the privilege sets separately through Manage Security, an administrator could perform a find against the related privilege set DDTs to find all privilege sets with access to the whole table and find all privilege sets with limited table access to that field. While reviewing the found set, the administrator would be able to correct access as needed in the DDTs.


    Any part of a FileMaker schema that can be represented in a FileMaker table and layout should. Once FileMaker solutions and their developers can use the solution to inspect, use, and manipulate its own design, the development possibilities would be endless. At this point we would have such an extraordinarily powerful self-referential system, it would be kind of scary. It would make all solution objects as easily and thoroughly findable, sortable, relatable, calculable, manipulable, and scriptable as any data in a database.


    You'll know when you have arrived when you can use FileMaker layouts to not only manage entire solutions, but even to enhance and reinvent any FileMaker Pro management interface. Most of these requests for enhancement would either go away or would simply be submitted as files with the layout enhancements already built by FileMaker Pro developers, as if FileMaker Pro were some sort of open source project.


    Related to Every FileMaker Pro Application Interface, a Layout Interface.

    When I have time, I want to make a list in the comments below of some of the top product ideas that could be addressed or enhanced by this idea.