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

Retrieve fields from list or table and filter by field type.

Average rating: 4.3 (29 votes) Log in to vote

Michael Wallace - Show more from this author
Empowered Data Solutions
http://empowereddatasolutions.com

Sample input:

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

Sample output:

textField1
textField2
numberField2

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

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

2021-02-03 - Corrected problem with getting all fields from table with "*" and re-wrote to use While

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

Let([~=""
/* Debugging */
/*
; ~dontLog = List ( 
	GetFieldNameOnly ( ExampleTable::z_creationAccount ) ;
	GetFieldNameOnly ( ExampleTable::z_creationDate ) ;
	GetFieldNameOnly ( ExampleTable::z_creationTime );
	GetFieldNameOnly ( ExampleTable::z_modificationAccount ) ;
	GetFieldNameOnly ( ExampleTable::z_modificationDate ) ;
	GetFieldNameOnly ( ExampleTable::z_modificationTime )
)
; theFieldTypes = ""
; theLayoutName = ""
; theTableName = ""
; theFileName = ""
; omitFields = ~dontLog
; localFieldsOnly= ""
*/
/* End Debugging */


; ~layoutName = Case (  IsEmpty ( theLayoutName ) ; Get ( LayoutName ) ; theLayoutName = "*" ; "" ; 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 )
; ~fieldTypes = If ( IsEmpty ( theFieldTypes ) ; "text¶number¶date¶time¶timestamp¶container¶calculation¶summary¶global" ; theFieldTypes )
; ~omitFields = omitFields
; ~fieldNames = Case ( 
					not IsEmpty ( ~layoutName ) ; 
						FieldNames ( ~fileName ; ~layoutName ) ;
					/* Otherwise get all fields from the specified table */
						ExecuteSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName=?" ; "" ; "" ; ~tableName )
				)
; ~validFieldTypes = "text¶number¶date¶time¶timestamp¶container¶calculation¶summary¶global"
; ~invalidParams = If ( 
	not IsEmpty ( theFieldTypes ) ; 
		Substitute ( 
			While ([~=""
			/* initialVariable */
			; ~count2 = ValueCount ( ~fieldTypes )
			; ~counter2 = 0
			; ~result2 = ""
			]
			/* condition */
			; ~counter2 < ~count2
			;[~="" 
			/* logic */
			; ~counter2 = ~counter2 + 1
			; ~fieldType = GetValue ( ~fieldTypes ; ~counter2 )  
  			; ~fieldType = If ( not PatternCount ( "¶" & ~validFieldTypes & "¶" ; "¶" & ~fieldType & "¶" ) ; ~fieldType ) 
			; ~result2 = List ( ~result2 ; ~fieldType )
			]
			/* result */
			; ~result2
			) 
		; "¶" ; ", " ) // End Substitute 
	) // End If

; ~list = While ([~=""

/* initialVariable */
; ~count = ValueCount ( ~fieldNames )
; ~counter = 0
; ~result = ""
]
/* condition */
; ~counter < ~count
;[~="" 
/* logic */
; ~counter = ~counter + 1

; ~fieldName = If ( not ~localOnly ; ~tableName & "::" ) & GetValue ( ~fieldNames ; ~counter ) 
; ~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" ;
 		) /* End Case */
; ~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 & "¶" ) 

; ~result = List ( ~result ; 	
				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 ;
			 		/* otherwise */
			 			""
				)/* End Case */
			) /* End List */
]
/* result */
; ~result
) 

; ~errorInList = PatternCount ( ~list ; "**ERROR:" )
; ~errorBegin = If ( ~errorInList ; Position ( ~list ; "**ERROR:" ; 1 ; 1 ) )
; ~errorEnd = If ( ~errorInList ; Position ( ~list ; "¶" ; ~errorBegin ; 1 ) )
; $$GetFieldsByType_ErrorMessage = Case ( 
				~errorInList ; 
					Middle ( ~list ; ~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 ) ; "" ; ~list )

)

This will get a list of fields from the current context or a specified context and filter the list by the types specified. Useful for audit log set up like UltraLog by Nightwing.

Now edited to use the While function

There is also a global variable that is set by this custom function for debugging purposes called: $$GetFieldsByType_ErrorMessage

If no fields are returned then check this variable for an error message.

A field set to the following will give you all of the non calc, summary, etc fields for you to use in your audit log calculation:
GetFieldsByType ( "text¶number¶date¶time¶timestamp" ; "" ; "" ; "" ; "AuditLog¶ModificationTimestamp" ; "" )

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

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

Comments

Support this website.

Support this website.