Update FMPXMLRESULT (to allow better separation of raw data versus formatting)

Idea created by Doug Staubach on Apr 9, 2016
    Active
    Score6
    • Doug Staubach
    • Benjamin Fehr
    • David Julot
    • isakjorge
    • lmarcinelli
    • Markus Schneider

    Problem

     

    FileMaker supports exporting records in XML format, and that capability is extremely useful (thank you). When combined together, XML and XSLT give FileMaker developers the ability to 'transform' on-screen data into other globally-supported file formats, including Excel, Word, and PDF.

     

    I have seen other developers post a recommendation to add Support for XSLT Version 2.0, and I fully support that goal, because doing so would make our XSLT scripts cleaner, more efficient, and more powerful.

     

    However, the majority of problems that I personally encountered with the XML + XSLT export process were not problems or limitations with XSLT at all - the root cause of the issue is the lack of sufficient information in the XML data (the FMPXMLRESULT format).

     

    Use Cases


    Any project that involves exporting FileMaker records, using XML format.


    For example, 50% of the effort and 75% of the code that is contained in my FMP2XLS project (which converts FileMaker records into a fully-operational Excel worksheet) is completely dedicated to "decoding" values in the source data - specifically date, time, timestamp, and number fields. (While working on this project, I found that the vast majority of errors that I encountered were 'Table Errors', which are caused by an inability to transform the source information, into date, time, timestamp, or number values.)


    The same principles apply to ANY project involving XML export. (The FMPXMLRESULT format should provide better separation of raw data versus formatting. If data and formats had better separation, either or both of them could be more easily translated to other file formats.)


    Idea #1

     

    Add some new METADATA information to the current FMPXMLRESULT file when exporting records.


    Note: Since adding new information will not "break" existing solutions, this would be completely safe and backwards-compatible.


    (1) In the "MetaData" node, for each FileMaker "Field", add two new "attributes"

        • FORMATTING
        • CALCULATION


    Example 1 - For a calculated field that returns a number (and has a U.S. currency format):


         BEFORE (CURRENT):

            <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="MyNumberField" TYPE="NUMBER"/>


         AFTER (PROPOSED):

    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="MyNumberField" TYPE="NUMBER" FORMATTING="$#,0.00" CALCULATION="=MyAmount+MyTax"/>


    Example 2 - For a date field that has an international date format:


         BEFORE (CURRENT):

            <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="MyDateField" TYPE="DATE"/>


         AFTER (PROPOSED):

    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="MyDateField" TYPE="DATE" FORMATTING="d.m.yy" CALCULATION=""/>


    (2) In the main "Database" Node, add a bunch of new "attributes"

        • SYSTEMPLATFORM ... same as Get(SystemPlatform)
        • SYSTEMVERSION ... same as Get(SystemVersion)
        • SYSTEMLANGUAGE ... same as Get(SystemLanguage)
        • SYSTEMIPADDRESS ... same as Get(SystemIPAddress)
        • USERNAME ... same as Get(UserName)
        • ACCOUNTNAME ... same as Get(AccountName)
        • HOSTNAME ... same as Get(HostName)
        • HOSTIPADDRESS ... same as Get(HostIPAddress)
        • HOSTAPPLICATIONVERSION ... same as Get(HostApplicationVersion)
        • DESKTOPPATH ... same as Get(DesktopPath)
        • DOCUMENTSPATH ... same as Get(DocumentsPath)
        • TEMPORARYPATH ... same as Get(TemporaryPath)
        • USESYSTEMFORMATSSTATE ... same as Get(UseSystemFormatsState)
        • CURRENTDATE ... same as Get(CurrentDate)
        • CURRENTHOSTTIMESTAMP ... same as Get(CurrentHostTimeStamp)
        • CURRENTTIME ... same as Get(CurrentTime)
        • CURRENTTIMESTAMP ... same as Get(CurrentTimestamp)
        • CURRENTTIMEUTCMILLISECONDS ... same as Get(CurrentTimeUTCMilliseconds)
        • SYSTEMTIMEZONE
        • SYSTEMDECIMALCHAR
        • SYSTEMGROUPINGCHAR
        • SYSTEMCURRENCYCHAR
        • SYSTEMCRLFCHAR ... what sort of carriage return or line feed combination did the system use?
        • EXPORTFORMATSSELECTED ... did the user choose to export data using layout formats?

     

    The FORMATTING attribute for each field would be a textual representation of the currently-applied "data formatting" options (the same options that are visible using the field "inspector" tool in FileMaker's layout mode, in the section titled "data formatting"). For example, these settings could be represented as "mmm-dd-yyyy" for a date field, or "$#.00" for a number field. -- If the user chooses a named option (like "use system formats" or "short date format"), the text in this attribute should show the actual format for the data, not the abbreviated name of the format. (For example: "d-m-y" is correct, but "short system date" is not correct). -- For text fields, this attribute would always be empty.


    The CALCULATION attribute for each field would be the actual text contained inside the FileMaker formula screen that is used to produce the field's contents. For example, "=Upper(MyTextField)" or "=MayDateField+30" or "=Minimum of MyAmountField" -- This value would only be populated for calculated fields and summary fields -- for other types of fields, this attribute would always be empty.


    The DATABASE ATTRIBUTES would mostly be identical to the FileMaker Get() functions of the same name. The last 5 functions in the list do not have equivalent FileMaker Get() functions, so they would have to be queried from the underlying operating system. This set of attributes presents a very good "snapshot" of the system variables that were in place when the file was exported (the instant it was exported), and can be used by developers to make all sorts of useful formatting and translation decisions.


    Idea #2

     

    If the user DOES NOT choose the option to "Apply current layout's formatting to exported data", then the values stored inside the DATA nodes should not contain ANY characters used for formatting (they should be converted to a valid XML format like xs:datetime and xs:decimal or xs:long ).

     

    Note: This represents a pretty big change, so it might be worthwhile to give this a new name in the GRAMMAR dropdown list.

     

    • For DATE fields, the information inside the <DATA> tags should be translated to valid XML date format (ISO format): "yyyy-mm-dd"
    • For TIME fields, the information inside the <DATA> tags should be translated to 24-hour ISO format: "hh:mm:ss[.###}"
    • For TIMESTAMP fields, the information inside the <DATA> tags should be translated to ISO format: "yyyy-mm-ddThh:mm:ss[.###][-#]"
    • For NUMBER fields, the information inside the <DATA> tags) should be translated to a valid XML number type like: "[-]#[.#]"


    Benefits


    • Idea #1 could be implemented quickly and easily (in the next update).
    • Idea #2 might require a little more testing, but since it reduces complexity, it should be relatively easy to implement.
    • If BOTH ideas are implemented, (storing data in a way that is independent of visual formatting, PLUS providing field-level metadata attributes to allow visual formatting to be easily translated into other technologies), then we will have really good separation of data layer and presentation layer, which make the whole solution more portable, compatible, and extensible.
    • Reduces the number of "custom plug-ins" needed. This puts power in the hands of end-users.
    • Performance of XML and XSLT scripts would be improved (less code needed to extract and transform data).
    • Since FileMaker already has the option to "Apply current layout's formatting to exported data", you are making a situtation that is the best of both worlds for both novice users and experts:
      • Novice users, who want a simple XML file export option, will be satisfied with the option to apply layout formatting (they get a WYSIWYG representation of their data, exactly as it appears on the screen).
      • Advanced users, who want clear separation of data verus visual formatting, will be satsified with the option to export the data in a way that makes it universally compatible across applications (and much simpler to extract and work with).
    • Helps Improve FileMakers Integration Technologies