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:

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

If we run this query against a table called company with these 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.