Export Records using Calculations as well as Fields

Idea created by mrwatson-gbs on Feb 7, 2017
    • tomasd
    • intex
    • Johan Hedman
    • jbante
    • mrwatson-gbs



    Data is often moved around FM-databases using export and then import. Usually the target table of an export/import data transfer process has certain data requirements - ids, constant values, status info, etc. - which are not available in the source table, yet which need to be filled.


    The current method of dealing with this is to


    • manipulate the data after the import (which may not be possible if that should trigger auto enter fields to be (incorrectly) recalculated), or
    • create global or calculation fields in the source table to generate the required constant or dynamic data.


    The problem is that the more Import/Export processes you have, the more bloated the source table gets with fields the really do not belong there.




    It would be fantastic to be able to create dynamic data from within the export step - without having to bloat the source table.


    I could imagine two variants of this:


    1. A field override
      • where a field is chosen as the export field (and is used to generate the field name)
      • a calculation is specified which defines the data (and data type) to export
      • this could be used to set/reset fields which are in both source and target tables
    2. A virtual field (an extra column)
      • not connected to any field in the source table
      • the field name would be defined separately (see Export Records with Custom Field Names)
      • as above a calculation defines the data
      • This could be used to add columns that are not available in the source table.




    • Business logic of an export is coded within scripts instead of in the database schema => more maintainable (partic. in LIVE systems).
    • Sleeker tables => increased performance
    • Easier maintenance of tables due to reduced dependence between source and target tables
    • Makes it possible to create Export-then-Import processes which require NO post-processing in the target table => HUGE performance increase!


    Use Cases


    • In Export/Import data movement processes:
      • create data for the target table, which is not available (or different) in the source table
      • generate ids
      • generate data for all auto-enter fields of the target table, so that the fields can be populated using Export then Import with no calculations
      • Converting offer to invoice:
        • field: _ID, data = Get( UUID )
        • field: DocType, data = "Invoice" (i.e. a field override)
      • Creating to-dos for the current found set:
        • field name = "unread", data = 1 (i.e. a virtual field)
    • FileMaker as MiddleWare:
      • Exporting data to external interfaces
    • Reporting:
      • Export data and create statistic data as you go.


    Happy FileMaking