During my adventures in the world of FileMaker Development, I came across a request from a client to export records to Excel. “Sure, no problem” I said.  Then there was the twist… The client wanted to know if we could export the records, but instead of a mass export, they want to be able to group the line items together. Imagine an export similar to a Sub summary report. I started to shake my head no, but then realized, FileMaker can do just about anything if you put your mind to it…

Ladies and Gentlemen I give you the Sub Summary, Grouped records export to excel (cheated a little to get it to work, but hey…).

The trick to getting this to work is not to export records to excel, but rather Save Records as Excel through the script.

Scenario – Create a list of services provided by a Clinician by month range, filter out any services with zero minutes, and then export the values to excel in the following format:

Clinician Name, Service Code, Date, Service Fee

Service Code, Date, Service Fee

Clinician Name, Service Code, Date, Service Fee

Service Code, Date, Service Fee

Etc…

To get this to work, the first thing we need to understand is how to use filtered portals and using global fields for relationships.

Since the data I want to export is tied into a Users table, I create a new set of table occurrences, starting with the Users table and pointing to the services table.  See the image below:

In the relationship, I use a set of global date fields to set my data range, a Type field to filter out everything but a particular type (allows for export of different sets of data), and also a filter for all records that have a value greater than 1 in the Time field (so blank and missed services do not get exported or billed to the client).

Next I created a layout for the export process. The layout points to the new table occurrence we created for the Users.  The layout is really simple and only contains a couple of fields from the User table, and a portal that points to the table occurrence we just created for services (which uses globals to filter records).

Finally, I need a script to handle the export process. The script will need to grab data from the user, and then go to the new layout, and export the records. Below is the pseudo code for the script:

1. Ask the user for the date range of the report – set these to global date fields.
2. Perform a find for Users that have services for that range (filter out any users who do not have any related service records for the data range).
3. Sort the records by User name.
4. Set a variable to a file location (“filewin:” & Get(DesktopPath) & MonthName & Year &”.xls” – or something similar).
5. Save Records as Excel (instead of using export records as excel) and point to the variable we just created for the file location.

The script is then run from a Manager’s layout, it saves the records as excel, and you get a report that looks like:

The way the Save Records as Excel works, it takes all the fields that are on a layout and exports them to excel, which includes related fields. If the related fields sit within a portal, that portal’s line items are exported as you see  above. With this process, I can create any type of sub-summary report as an excel.

For example, if I have an Employees table, which contains all employees, managers and worker bees. I create an assignment process for managers to employees (a self join table that uses a Manager ID to link the employees to a manager). My process for getting a sub-summary export of all managers, and their subordinates into excel, is exactly as above except for the changes to the script. I would create the layout with all the employees for that manager, show only the fields I want exported, and then write the script for searching for the managers, sorting them, and saving the records as excel.

There are some limitations to this process, in that I cannot sub-summarize multiple times, i.e. I cannot write a sub-summary report that summarizes by Region, then Manager, then a list of employees. The reason is that I cannot by definition of FileMaker, put a portal within a portal.

If you have questions about this process, please feel free to comment.

If you would like learn more tips and tricks, I highly recommend our Foundations Training class. You can get more information at the link below:

http://anvildataworks.com/filemaker-training.php

Find me on twitter – DC_FileMakerGuy