GetFieldsByType ( )

Function stats

Average user rating
154
370
9999
Support
FileMaker 8.0 +
Date posted
17 October 2014
Last updated
17 October 2014
Version
Recursive function
No

Author Info
 Michael W.

3 functions

Average Rating 5.0

author_avatar



 

Function overview

Prototype

GetFieldsByType  ( theFieldTypes;   theLayoutName;   theTableName;   theFileName;   omitFields;   localFieldsOnly )


Parameters

theFieldTypes  Any combination of the following: text¶number¶date¶time¶timestamp¶container¶calculation¶summary¶global


theLayoutName  Null, Layout Name, or


theTableName  Defaults to Get ( LayoutTableName ) or can be specified.


theFileName  Defaults to Get ( FileName ) or can be specified.


omitFields  Return delimited list of fields to omit from the returned set.


localFieldsOnly  Boolean defaults to true. Return only fields on the layout that belong to that layouts table otherwise false gives you related fields as well from the layout.


Description

Tags:  Layout   Filter   Fields   AuditLog   Audit  

Function originally written to aid in implementation of UltraLog by Nightwing Enterprises. In it's base form it will return a list of fields to be logged by the UltraLog custom function or you can use it any time you need a specific list of fields.

Requires the CustomList custom function

Examples

Sample input

GetFieldsByType ( "text¶number" ; "" ; "" ; "" ; "numberField1" ; "" )


Sample output

textField1
textField2
numberField2
On the context of a layout that has 2 text fields and 2 number fields and any number of other fields.

 

Function code

// GetFieldsByType ( theFieldTypes ; theLayoutName ; theTableName ; theFileName ; omitFields ; localFieldsOnly )


/********************************************************
Options:
theFieldTypes = return delimited list of field types that you want to show.
    Defaults to: "text¶number¶date¶time¶timestamp¶container¶calculation¶summary¶global"

theLayoutName = layout name used to filter the results
        OR
        "*" which will return all fields from the TABLE rather than the layout
        OR
        Defaults to: the current layout

theTableName = the table from which we are pulling the fields
        Defaults to: the current layout's table

theFileName = the file from which we are getting the fields
        Defaults to: the current file

omitFields = a list of fields that you want to omit seperately from the list returned.
        *Note: Omitting the audit fields and modification timestamps and such is a good idea.

localFieldsOnly =     true - return only fields from the current table even if on the current layout.
        false - return all fields
        Defaults to: true

*************************************************************************/


Let ([
~layoutName = Case ( theLayoutName = "*" ; "" ; IsEmpty ( theLayoutName ) ; Get ( LayoutName ) ; theLayoutName )
; ~fileName = If ( IsEmpty ( theFileName ) ; Get ( FileName ) ; theFileName )
; ~tableName = If ( IsEmpty ( theTableName ) ; Get ( LayoutTableName ) ; theTableName )
; ~localOnly = Case ( ~tableName <> Get(LayoutTableName) ; False ; IsEmpty ( localFieldsOnly ) ; True ; localFieldsOnly )
; ~validFieldTypes = "text¶number¶date¶time¶timestamp¶container¶calculation¶summary¶global"
; ~fieldTypes = If ( IsEmpty ( theFieldTypes ) ; "text¶number¶date¶time¶timestamp¶container¶calculation¶summary¶global" ; theFieldTypes )
; ~omitFields = omitFields
; ~fieldNames = FieldNames ( ~fileName ; ~layoutName )
; ~invalidParams = If (
    not IsEmpty ( theFieldTypes ) ;
        Substitute (
            CustomList ( 1 ; ValueCount ( ~fieldTypes ) ; "
            Let([
             ~validFieldTypes = " & Quote( ~validFieldTypes ) & "
             ; ~fieldTypes = " & Quote( ~fieldTypes ) & "
             ; ~fieldType = GetValue ( ~fieldTypes ; [n] )
            ];
            if ( not PatternCount ( \"\¶\" & ~validFieldTypes & \"\¶\" ; \"\¶\" & ~fieldType & \"\¶\" ) ; ~fieldType )
            )
            ") // End CustomList
        ; "¶" ; ", " ) // End Substitute
    ) // End If
; ~fieldNames = CustomList ( 1 ; ValueCount ( ~fieldNames ) ;"
    Let ([
     ~fieldNames = " & Quote( ~fieldNames ) & "
     ; ~fileName = " & Quote( ~fileName ) & "
     ; ~omitFields = " & Quote( ~omitFields ) & "
     ; ~localOnly = " & Quote( ~localOnly ) & "
     ; ~layoutName = " & Quote( ~layoutName ) & "
     ; ~tableName = " & Quote( ~tableName ) & "
     ; ~fieldTypes = " & Quote( ~fieldTypes ) & "
     ; ~fieldName = if ( not ~localOnly ; ~tableName & \"::\" ) & GetValue ( ~fieldNames ; [n] )
     ; ~fieldName = if ( not ~localOnly or not PatternCount ( ~fieldName ; \"::\" ) ; ~fieldName )
     ; ~typeData = FieldType ( ~fileName ; ~fieldName )
     ; ~typeArray = Substitute ( ~typeData ; \" \" ; \"\¶\" )
     ; ~fieldType = Case (
        PatternCount ( GetValue ( ~typeArray ; 1 ) ; \"calc\" ) ;
            \"calculation\" ;
        PatternCount ( GetValue ( ~typeArray ; 1 ) ; \"summary\" ) ;
            \"summary\" ;
        PatternCount ( GetValue ( ~typeArray ; 1 ) ; \"global\" ) ;
            \"global\" ;
        GetValue ( ~typeArray ; 2 ) = \"container\" ;
            \"container\" ;
        GetValue ( ~typeArray ; 2 ) = \"text\" ;
            \"text\" ;
        GetValue ( ~typeArray ; 2 ) = \"number\" ;
            \"number\" ;
        GetValue ( ~typeArray ; 2 ) = \"date\" ;
            \"date\" ;
        GetValue ( ~typeArray ; 2 ) = \"time\" ;
            \"time\" ;
        GetValue ( ~typeArray ; 2 ) = \"timestamp\" ;
            \"timestamp\" ;
        )
     ; ~standard = if ( GetValue ( ~typeArray ; 1 ) = \"Standard\" ; true )
     ; ~external = if ( PatternCount ( GetValue ( ~typeArray ; 1 ) ; \"External\" ) ; true )
     ; ~reps = FieldRepetitions ( ~fileName ; ~layoutName ; ~fieldName )
     ; ~repWarning = if ( GetAsNumber ( ~reps ) > 1 ; true )
     ; ~location = Substitute ( FieldBounds ( ~fileName ; ~layoutName ; ~fieldName ) ; \" \" ; \"\¶\" )
     ; ~repLocation = if ( ~repWarning ; \"(Left: \" & GetValue ( ~location ; 1 ) & \" Top: \" & GetValue ( ~location ; 2 ) & \")\" )
     ; ~valid = PatternCount ( \"\¶\" & ~fieldTypes & \"\¶\"; \"\¶\" & ~fieldType & \"\¶\" ) and not PatternCount ( \"\¶\" & ~omitFields & \"\¶\"; \"\¶\" & ~fieldName & \"\¶\" )
     ];
    Case (
        ~valid and ~repWarning ;
            \"**ERROR: Not all repetitions of \" & ~fieldName & ~repLocation & \" are listed seperate from each other. They can only be returned if placed seperately on the layout**\" ;
        ~valid ;
            ~fieldName ;
    )
     )
" )// End CustomList
; ~errorInList = PatternCount ( ~fieldNames ; "**ERROR:" )
; ~errorBegin = If ( ~errorInList ; Position ( ~fieldNames ; "**ERROR:" ; 1 ; 1 ) )
; ~errorEnd = If ( ~errorInList ; Position ( ~fieldNames ; "¶" ; ~errorBegin ; 1 ) )
; $$GetFieldsByType_ErrorMessage = Case (
                ~errorInList ;
                    Middle ( ~fieldNames ; ~errorBegin ; ~errorEnd - ~errorBegin ) ;
                not IsEmpty ( ~invalidParams ) ;
                    "**ERROR: Invalid FieldTypes(s) - " & ~invalidParams & "¶Valid Types = " & Substitute ( ~validFieldTypes ; "¶" ; ", " ) ;
                // Default Clear global if no errors
                    ""
                )
];
If ( not IsEmpty ( $$GetFieldsByType_ErrorMessage ) ; "" ; ~fieldNames )
)

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

    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/370

    Prototype: GetFieldsByType( theFieldTypes; theLayoutName; theTableName; theFileName; omitFields; localFieldsOnly )
    Function Author: Michael W. (http://www.fmfunctions.com/mid/154)
    Last updated: 17 October 2014
    Version: 1.2

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