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

retrieve field names from specified table

Average rating: 3.5 (2 votes) Log in to vote

David Schellenberg - Show more from this author

Sample input:

FmTableFields ( "table" ; "" ; "id,mod" ; "Calculated" ; "timestamp" ; "z_" )

Sample output:

field1
field2
field3

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

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

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

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

UPDATE:			v1.1, 2018Mar02 — Allow for 3 prefixExclude. Add wildcard to substitute in pe1 
 
====================================================================

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

*/


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 ; [ "  " ; " " ];[ "," ; "%','" ];[ "¶" ; "','" ];[ "_" ; "\_" ] ) & "%'"  ) ; 


	//PREFIX ARRAY
	peA = Substitute ( pe1 ; ",";"¶" ) ;


	//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 &")" ) ;

	wp1 = Case (	IsEmpty (GetValue(peA;1)) ; "" ; "and FieldName NOT LIKE  "& GetValue(peA;1) &"" ) ;
	wp2 = Case (	IsEmpty (GetValue(peA;2)) ; "" ; "and FieldName NOT LIKE  "& GetValue(peA;2) &"" ) ;
	wp3 = Case (	IsEmpty (GetValue(peA;3)) ; "" ; "and FieldName NOT LIKE  "& GetValue(peA;3) &"" ) ;




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

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


	];


	fA1

)

retrieve field names from specified table

Comments

		Nick Stockbridge, Transmedia Communications Ltd Jun 17, 2018
Hi I'm trying to use your very useful-looking function but can't get it to work. Please could you help? I'm trying to get all the field names from a table called Staff. My syntax is: FmTableFields ( "Staff" ; "" ; "" ; "" ; "" ; "" ) I have also tried storing the filed name in a field but that doesn't work for me either. Many thanks. Regards Nick

		David Jun 18, 2018
Hi Nick, The function is case sensitive, perhaps that might be the issue. https://www.dropbox.com/s/rkizyum1jl0rp99/FmTableFields.fmp12.zip?dl=0 David

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

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

Comments

Support this website.

Support this website.