TableColumns function - for tabulated text/SQL-result (+1 for non EN locales!)

Idea created by mrwatson-gbs on May 10, 2016
    Active
    Score3
    • arnoldkegebein
    • mrwatson-gbs
    • TobiasLiebhartKoschierSE

    Context

     

    Powerful string-processing functions are a core component of FileMaker data processing functionality. FileMaker already has powerful native functions for processing rows of string data (value and list functions and the string concatenation with '& ”¶” &’), however,

     

    • there are NO native functions for processing columns of string data. - not for concatenating columns, nor for extracting columns, nor for processing single columns of a tabulated text.

     

    Such functionality would be particularly useful when creating FileMaker Charts using ExecuteSQL - especially in non EN locales where the decimal point needs to be changed into a a comma.

     

    Idea

     

    There should be function(s) for processing text columns.

     

    I’d like to suggest a combo-function TableColumns to both concatenate and extract columns of data, something like this:

     

    TableColumns( text1 ; text2 ; appendAtColumn ; extractColumns {; columnDataTypes} {; columnSeparator} {; rowSeparator} )

     

    where

     

    • text1 is a tabulated or delimited string - for example the result of an ExecuteSQL function.
    • text2 - optional - text to be (horizontally) concatenated onto text1
    • appendAtColumn - the number of columns to pad/truncate text1 to
      • empty = NO extra column separators should be added between the rows
      • 0 = automatically pad text1 to ‘square it off'
      • any other number = column to which the text should be padded / truncated before appending text2
    • extractColumns is an expression to specify one or more columns to be extracted from the text
      • A number specifies the column number
      • A range of columns can be indicted using a range delimiter, “..” or “-“, e.g. “2..5” or “2-5"
      • A list of columns can be separated using a list separator (white-space, semicolon or comma)
      • E.g. “12 1-5 7” would mean column 12 columns 1 thru 5 and column 7
    • columnDataTypes is an optional expression to specify the data type of the selected columns
      • Specifying a data type of “number” would convert a column of SQL-numbers to a column of numbers using the appropriate system decimal separator.
      • Specifying a data type of “timestamp” would convert a column of SQL-timestamps to a column of dates using the appropriate system date+time separators and format.
    • columnSeparator and rowSeparator define the delimiters for fields and rows
      • equivalent to the fieldSeparator & rowSeparator parameters in the ExecuteSQL function
      • empty strings are interpreted as the default delimiters <TAB>  and value-separators (“¶”).

     

     

    Examples

     

    The following tables are represented in the following strings (I’m using <TAB> to help visualize tab characters)

     

    Table 1

    CatQty
    A1.1
    B2.2
    C

     

    Set Variable[ $Table1 ; “Cat<TAB>Qty¶A<TAB>1.1¶B<TAB>2.2¶C” /* Note that row C has NO second column */ ]

     

    Table 2

    Date
    2016-01-01

    2016-02-01

    2016-03-01

     

    Set Variable[ $Table2 ; “Date¶2016-01-01¶2016-02-01¶2016-03-01” ]

     

    Example 1 - Simple table concatenation (with automatic column padding)

     

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; $Table2 ; 0 ) ]

     

    Concatenates the two tables using the default delimiters of <TAB> and ¶, placing enough <TAB> delimiters between the columns of the first and second strings to ’square off’ table 1.

     

    $ResultingTable:

     

    CatQtyDate
    A1.12016-01-01
    B2.22016-02-01
    C2016-03-01

     

    “Cat<TAB>Qty<TAB>Date¶A<TAB>1.1<TAB>2016-01-01¶B<TAB>2.2<TAB>2016-02-02¶C<TAB><TAB>2016-02-01”

     

    Example 2 - Table concatenation with column padding

     

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; $Table2 ; 4 ) ]

     

    Appends table 2 at column 4 of table 1 (using the default delimiters of <TAB> and ¶, andplacing up to 4 <TAB> delimiters between the columns of the first and second strings).

     

    $ResultingTable:

     

    CatQtyDate
    A1.12016-01-01
    B2.22016-02-01
    C2016-03-01

     

    “Cat<TAB>Qty<TAB><TAB>Date¶A<TAB>1.1<TAB><TAB>2016-01-01¶B<TAB>2.2<TAB><TAB>2016-02-02¶C<TAB><TAB><TAB>2016-02-01”

     

    Example 3 - Table concatenation with column truncation

     

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; $Table2 ; 2 ) ]

     

    Truncates table 1 to 1 column and appends table 2 at column 2.

     

    $ResultingTable:

     

    CatDate
    A2016-01-01
    B2016-02-01
    C2016-03-01

     

    “Cat<TAB>Date¶A<TAB>2016-01-01¶B<TAB>2016-02-02¶C<TAB>2016-02-01”

     

    Example 4 - Table concatenation with NO delimiter

     

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; $Table2 ) ]

    or

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; $Table2 ; “" ) ]

     

    Concatenates table 2 directly onto table 1:

     

    $ResultingTable:

     

    CatQtyDate
    A1.12016-01-01
    B2.22016-02-01
    C2016-03-01

     

    “Cat<TAB>QtyDate¶A<TAB>1.12016-01-01¶B<TAB>2.22016-02-02¶C2016-02-01”

     

    Example 5 - Column extraction

     

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; “" ; “" ; “2,1" ) ]

     

    Returns column 2 and column 1 of table 1(using default delimiters)

     

    $ResultingTable:

     

    QtyCat
    1.1A
    2.2B
    C

     

    “Qty<TAB>Cat¶1.1<TAB>A¶2.2<TAB>B¶<TAB>C”

     

    Example 6 - Concatenation and data type casting

     

    Set Variable[ $ResultingTable ; TableColumns( $Table1 ; $Table2 ; 0 ; “” ; “<TAB>Number<TAB>Date") ]

     

    Concatenates the tables as in example 1,and then applies the type casting (from SQL-Data types) to the third and fourth columns.

     

    The following result assumes a german locale where the decimal separator is a comma and the system date format is d.m.yyyy.

     

    $ResultingTable:

     

    CatQtyDate
    A1,11.1.2016
    B2,21.2.2016
    C1.3.2016

     

    “Cat<TAB>Qty<TAB>Date¶A<TAB>1,1<TAB>1.1.2016¶B<TAB>2,2<TAB>1.2.2016¶C<TAB><TAB>1.3.2016”

     

    Example 7 - Extract data from SQL result for charting

     

    Given an $SQLResults table:

     

    A1.12016-01-01 01:23:00
    B2.22016-02-01 12:34:00
    C2016-03-01 23:45:00

     

    You could extract the data for a chart using the following expressions

     

    X-Values = TableColumns( $SQLResults ; “" ; “" ; 3 ; “Date") ]

    Y-Values = TableColumns( $SQLResults ; “" ; “" ; 2 ; “Number") ]

    Labels = TableColumns( $SQLResults ; “" ; “" ; 1 ) ]

     

     

    ….and  you can imagine other combinations of append, truncate, extract & cast columns, and, of course, using other delimiters.

     

    Benefits

     

    • Powerful string-processing functions
    • Text tables can be processed easily, reliably - and error free.
    • Synergy with existing functions:
      • Makes charting with ExecuteSQL easier - particularly for non EN locales
      • List function + fields
    • Large text tables can be processed - where CFs quickly fail due to recursion limit.
    • Increased performance
    • Simplified code
    • Increased maintainability
    • Increased code readability
    • Increased productivity
    • Code is more interchangeable due to reduced dependance on developer-specific custom functions

     

    Use Cases

     

    • Processing SQL results & converting SQL data types to FM data types:
      • SQL-Timestamps
      • SQL-numbers ( on computers with non EN Locale)
    • FM-Charting using SQL results
    • Creating a table of two related fields
      • using TableColumns( List( Related::Col1 ) ;List( Related::Col2 ) )
    • Processing TAB-delimited data files
    • Displaying tabulated data in monospace font
    • Processing Name & Value tuples
    • Building HTML tables
    • and thousands more

     

    Action

     

    Please vote if you agree with the general idea - then we can haggle about the details / exact syntax ;-)