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

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.

 

Leave a comment

2 comments

  • thong
    thong 03 December 2015 Report

    Hello,

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

    Thank you.

    Thong

    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
    Shai

    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