• Please enter your DISQUS subdomain in order to use the 'Disqus Comments (for Joomla)' plugin. If you don't have a DISQUS account, register for one here

Core FileMaker Blog

Keep up to date with CoreSolutions

Dynamic Reports with FileMaker

I was recently asked by a client to create a dynamic report. By dynamic, I am referring to the ability to choose different fields. At first I thought this was impossible with FileMaker, then I thought about creating a bunch of layouts. Unfortunately the requirements had 12 fields and the need for 5 on the report making the number of combinations – 792.

Although this would not be impossible it would be extremely time consuming and extremely unmanageable, say if they wanted something added or removed from this report. While talking with some colleagues about some options, the ExecuteSQL function was brought up.

I created a proof of concept to attempt to use this, and it turns out its fairly simple.

The report will use 12 fields from the same table for simplicity
Managing a databse built from 12 possible fields
I then created an indexed calculation field to be a list based on the values in this table.
Specifying calculations for creating dynamic lists

I created a value list to be based on this field.
Creating the value lists based on the fields

The next step is to create a report based on a virtual list.
Within the virtual list table I created 5 fields. 1 for each column.
Setting up the variables in the databse management

The PickAPart__cf custom function is defined as this.
Building the custom function

For the layout I created 5 global fields to be used for the choice of headers.

Final setup of value fields

I set the value list for the fields to be based on the value list I created above which will give an option of the fields you need.

The Generate SQL button calls a script that sets the virtual list field

with a sql statement
ExecuteSQL ( “Select ” & VirtualList::zi__field1__t & “, ” & VirtualList::zi__field2__t & “,” & VirtualList::zi__field3__t & “,” & VirtualList::zi__field4__t & “,” & VirtualList::zi__field5__t & ” from company”; “|”; “”)

This set field is then followed by 1 more script used to set the virtual list “Make Sure Enough Records Exist In Virtual List Table” which sets the correct amount of records.
I change the field headers to be merge fields of the global fields used for the SQL.


Leave a comment


  • thong
    thong 03 December 2015 Report


    Hope all is well. I would appreciate if you can share your sample file to me.

    Thank you.


    Comment Link

  • Shai
    Shai 26 August 2015 Report

    I would like to ask if do you have a DYNAMIC REPORTS file to share with me ?
    Thank in advance

    Comment Link