Core FileMaker Blog

Keep up to date with CoreSolutions

Getting a list of Field Names – Design Functions and ExecuteSQL

Recently, I wanted to pull a list of field names from a table to display to the user. The first place I looked was the Design functions. If you haven’t had a look at these, I’d suggest taking some time to have a quick look at them; they can give you some interesting information about the structure of your database including:

  • LayoutNames – the name of layouts in a given file
  • ScriptNames – the name of the scripts in a file
  • ValueListNames – the name of the value lists in a file
  • ValueListItems – the values in a given value list
  • RelationInfo – a list of all relationships for a given table

They aren’t something that you’ll use everyday, but there are some creative uses for them; I’ve used the ValueListItems in quite a few calculations to get (for example) a unique list of values from a field. Unfortunately, the design functions didn’t solve the problem that I had – there is a Design function called FieldNames, but it lists the fields that are on a layout, not for a table. I could have worked around it by keeping a hidden layout that just contains all the fields for each table ( I usually have something like this anyway; since I find them useful for scripting purposes), but I’d have to worry about it not being up to date if I added some fields to the table, but forgot to add them to the layout.

I did some digging, and found a custom function at briandunning.com that would do exactly what I wanted (click here to view the custom function) – by submitting a table name, I could get a list of field names for that table. Looking at the code behind the function, I realized that it used the new ExecuteSQL function introduced in FileMaker 12 (which I’ve written about in a previous blog post). The actual code for the ExecuteSQL command looks something like this:

I did some digging, and found a custom function at briandunning.com that would do exactly what I wanted ( click here to view the custom function) – by submitting a table name, I could get a list of field names for that table. Looking at the code behind the function, I realized that it used the new ExecuteSQL function introduced in FileMaker 12 (which I’ve written about in a previous blog post). The actual code for the ExecuteSQL command looks something like this:

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’company’” ; “” ; “” )

If we run this query against a table called company with these fields:

Company Fields

then we will get this list of fields:

id
firstName
lastName
phone
email
address
city
province
postalCode

This SQL statement takes advantage of the fact that there are 2 hidden tables in each FileMaker file that contain information about the database structure. The first one is called FileMaker_Tables, which returns information about the table occurrences in the file (such as Table Occurrence Name, the Base Table Name, the Table ID, etc). The second table (which is being used in the ExecuteSQL statement above) is called FileMaker_Fields, and contains information an all the fields in the file (the Field Name, the Table Occurrence it belongs to, the Field Type, number of repetitions, etc). There doesn’t seem to be much documentation on these hidden tables, but I found an excellent article on Databuzz’s website (you can find it here) which goes into more detail on these tables, what they contain and more examples on how to use them.

Like Design Functions, you won’t need to use this information everyday, or even in every solution, but there are times when being able to a list of field names or table names in your solutions can be handy.

Leave a comment

2 comments

  • Simon
    Simon 02 February 2016 Report

    In the current version of Filemaker Pro Advanced 14, the function FieldNames works also with a table as input parameter (instead of a layout). So actually the FieldNames function does the same thing like your custom function!

    Comment Link

  • Allen
    Allen 24 June 2013 Report

    Thanks for sharing this... it's a handy trick! Note that the sample code doesn't quite work, at least not to copy-paste, because the characters used for quotation marks aren't those FileMaker or SQL expect. Once I spotted the difference and used quotation marks FileMaker understands it worked like a charm.

    Comment Link

CoreSolutions Logo

London Office:
1-1615 N Routledge Park
London, ON N6H 5L6

Toronto Office:
2425 Matheson Blvd East
7th Floor
Mississauga, ON L4W 5K4

Toll Free: (800) 650-8882
London: (519) 641-7727
Toronto: (416) 410-8649
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

SUBSCRIBE

Click the 'Subscribe' button to sign up for news & software tips.

Beyond Software

Copyright © 2018 CoreSolutions Software, Inc. All Rights Reserved