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

I then created an indexed calculation field to be a list based on the values in this table.

I created a value list to be based on this field.

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.

The PickAPart__cf custom function is defined as this.

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

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

zi__textForVirtualListToParse__t

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.