FmTableFields ( )

Function stats

Average user rating
573
416
9999
Support
FileMaker 7.0 +
Date posted
22 November 2017
Version
1
Recursive function
No

Author Info
 rivet

1 functions

Average Rating None

author_avatar



 

Function overview

Prototype

FmTableFields  ( tableName;   fieldsInclude;   fieldsExclude;   classExclude;   typeExclude;   prefixExclude )


Parameters

tableName  


fieldsInclude  


fieldsExclude  


classExclude  


typeExclude  


prefixExclude  


Description

retrieve a list of field names from a specified table

Examples

Sample input

FmTableFields ( "profile" ; "" ; "" ; "Calculated" ; "timestamp" ; "" )


Sample output

profile fields: first, last, fullname, tsCreated
function result : first, last

 

Function code

/*
====================================================================

purpose            retrieve a list of field names from a specified table
                
tableName        table name

fieldsInclude:    blank or comma separated list of field names

fieldsExclude:    blank or comma separated list of field names

classExclude:    blank or comma separated list of FieldClass;
                Calculated¶Normal¶Summary [propercase]

typeExclude:        blank or comma separated list of FieldType;
                binary¶date¶decimal¶time¶timestamp¶varchar [lowercase]

prefixExclude    bank or field name prefix.
                sql Wildcard: '%'
                sql Single Character: '_' Since this is a common field name character, it has been escaped in the 'pe1' variable


Release:            v1.0 2017Nov21
Author:            David Schellenberg, filemaker@rivetcom.ca

====================================================================
*/


Let ([

    t = tableName ;
    fi = fieldsInclude ;
    fe = fieldsExclude ;
    ce = classExclude ;
    te = typeExclude ;
    pe = prefixExclude ;
    

    //CLEAN
    fi1 = Case ( IsEmpty (fi) ; "" ; Substitute ( "'" & fi ; [ "," ; "','" ];[ "¶" ; "','" ] ) & "'" ) ;
    fe1 = Case ( IsEmpty (fe) ; "" ; Substitute ( "'" & fe ; [ "," ; "','" ];[ "¶" ; "','" ] ) & "'" ) ;
    ce1 = Case ( IsEmpty (ce) ; "" ; Substitute ( "'" & ce ; [ "," ; "','" ];[ "¶" ; "','" ] ) & "'" ) ;
    te1 = Case ( IsEmpty (te) ; "" ; Substitute ( "'" & te ; [ "," ; "','" ];[ "¶" ; "','" ] ) & "'" ) ;
    pe1 = Case ( IsEmpty (pe) ; "" ; Substitute ( "'" & pe ; [ "," ; "','" ];[ "¶" ; "','" ];[ "_" ; "\_" ] ) & "%'" ) ;


    //SQL
    wfi = Case ( IsEmpty (fi) ; "" ; "and FieldName IN ("& fi1 &")" ) ;
    wfe = Case ( IsEmpty (fe) ; "" ; "and FieldName NOT IN ("& fe1 &")" ) ;
    wce = Case ( IsEmpty (ce) ; "" ; "and FieldClass NOT IN ("& ce1 &")" ) ;
    wte = Case ( IsEmpty (te) ; "" ; "and FieldType NOT IN ("& te1 &")" ) ;
    wpe = Case ( IsEmpty (pe) ; "" ; "and FieldName NOT LIKE "& pe1 &"" ) ;


    //ARRAY
    fA =
    ExecuteSQL ("
    SELECT fieldName FROM FileMaker_Fields WHERE TableName = '"& t &"'" & wfe & wce & wte & wpe &"
    
    "&
    Case (
        IsEmpty(fi) ; "" ;
        "UNION SELECT fieldName FROM FileMaker_Fields WHERE TableName = '" & t & "'" & wfi
    )
    &"

    ORDER BY fieldName" ; "" ; "," ) ;
    
    fA1 =
    Substitute ( fA ; [ "," ; "¶" ] )


    ];


    fA1

)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/416

    Prototype: FmTableFields( tableName; fieldsInclude; fieldsExclude; classExclude; typeExclude; prefixExclude )
    Function Author: rivet (http://www.fmfunctions.com/mid/573)
    Last updated: 22 November 2017
    Version: 1

*/
// ===================================

 

 

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (32)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (8)