Dynamic Reports with FileMaker

Dynamic Reports with FileMaker

Keep up to date with CoreSolutions

Dynamic Reports with FileMaker

Dynamic Reports Main Title Image

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


zi__textForVirtualListToParse__t

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.

Comments

Leave a Comment