Paradise by the Dashboard Light – Bouncing around in FileMaker, building Charts

Have you ever tried to chart a set of summary data? Everything works wonderfully if your chart is on the same layout as the underlying data. You simply click the popup and select ‘Use Data from Current Found Set’ and then click the ‘Show data points from group of records when sorted’ option.

The Problem: But what if you want to gather data from different layouts and table occurrences. You need a technique to capture the data from multiple sorted layouts and bring them back to a specific charting layout. The ‘easy way’ technique above won’t work unless it is employed on the specific layout or table occurrence that the data exists on.

What follows is a description of Bob Bowers’ ‘tricky way’ to get around this problem.

#Generate Chart Info for SALES TRENDS

1) We start by opening a new window and going to the layout for the sub-summary report we wish to capture the data from.

New Window [ Name: "Sales Trends" ] #Clear out the two global variables 

2) Clear out the Global Variables that are ultimately going to be used to feed the data into the Chart Object.

Set Variable [ $$Data_31_Day_Sales_Report; Value:"" ] 
Set Variable [ $$Label_31_Day_Sales_Report; Value:"" ] 
# Go to the Sales Summary Layout and run a search for the 
# last 31 days of sales. Sort by date. 

3) Next we run a search, in this case a date range search, specifically the last thirty-one days of sales, then we sort the data, using the criteria that the sub summary report is based on. In this case, it was simply a Sort by the Invoice Date.

Go to Layout [ “Reports - Sales - Summary” (Receipts Master) ] 
 Set Error Capture [ On ] 
 Enter Find Mode [ ] 
 Set Field [ Receipts Master::Invoice Date; 
    Reports Master::Search Date-31 
    & "..."&Reports Master::Search Date ]
 Perform Find [ ] 

4) This is an important error capture step. If there are no records found, be sure to close the window, otherwise you may end up with unpredictable results.

If [ Get(LastError)>0 ]
 Close Window [ Name: "Sales Trends"; Current file ]
 Exit Script [ ] 
 End If

5) Sort the records. In this case our report is based on Date, so we are sorting on the Invoice Date. Your sort will vary depending what you want to show up in your Label data in your graph.

Sort Records [ Specified Sort Order: Receipts Master::Invoice Date; 
 ascending ] [ Restore; No dialog ]
 #Set the local variable counter field to '1'

6) Here comes the secret sauce – The Loop. To make this work, we need three things:

a) a summary count field defined
b) the field that the summary is based upon. – this will usually be your label or X-Axis when you get to the graph.
c) the total field that you want to capture for your data field (or Y-Axis) in the graph.

We start by naming a variable called $record_number and setting it to ‘1’.

Set Variable [ $record_number; Value:1 ] 

Finally, we are ready for our Loop that will move us through the records to grab the totals for each category.

# Loop through the summary records, collecting the date 
# for the labels and 
# the totals for the data row of the chart (as derived by
 GetSummary function)
 Loop 
 Go to Record/Request/Page [ $record_number ] [ No dialog ]

• Go to Record step calculated on the variable $record_number

 Set Variable [ $$Label_31_Day_Sales_Report; Value:
  $$Label_31_Day_Sales_Report
  & Receipts Master::Invoice Date&"¶" ]

• Set the Label data to a variable called $Date (or whatever your category field is). You set it to itself and the current value of the field and a carriage return.

 Set Variable [ $$Data_31_Day_Sales_Report; 
  Value:$$Data_31_Day_Sales_Report & 
  GetSummary(Receipts Master::Total of Receipts; 
  Receipts Master::Invoice Date)&"¶" ]

• Set the Data variable to $SalesTotal to your total field. The key here is to use FileMaker’s GetSummary function, in this instance based on the same field I have sorted on, the Date field.

Set Variable [ $record_number; Value:$record_number+ 
 GetSummary(Receipts Master::Total Number of Receipts; 
 Receipts Master::Invoice Date) ]

• Finally we increment the $record_number variable by the number of records in the next group of summarized records.

Exit Loop If [ $record_number>Get(FoundCount) ]

• There is an Exit Loop If statement if the $record_number exceeds the total number of records in the Found Set.

End Loop

7) We have accumulated in our loop through the sub-summary report the Data and Label content for our Graph axises so we can now close the Window.

Close Window [ Name: "Sales Trends"; Current file ]

Check FileMaker’s Help file for a reminder of how the GetSummary function works.

The Sales data is captured in the $$Data_31_Day_Sales_Report global variable and the Label ends up in $$Label_Sales_Report global variable. In addition to seeing this information in the Data Viewer, it is possible to display this data on the layout by using merge field brackets to surround the global variable. <<$$Data_31_Day_Sales_Report>> will output the contents on the layout when viewed in Browse mode. This is another nice new feature of FileMaker 11. Now FileMaker Inc. would just add the ability to set global and local variables in a dialog box, the circle would be complete.

1) and 2) Feed the Global Variables we created in the Script into the X and Y axis fields.

3) Set the ‘Use Data From’ popup to ‘Current Record (delimited data)’.

You can then go into the Format Chart area to set up the specifics of how you want the chart to display.

Assuming all the steps have been performed correctly, you should see your data nicely displayed as a bar chart.

Part of the beauty of this technique, is that you don’t really need an underlying sub-summary report for this technique to work. I tried it on a simple list, and the results were the same as if I had built a complex layout complete with sub-summary parts etc. It would work even with a data view, no list layout required. The Count Records field, and a Sort based on your Label information are the crucial elements.

The end result is that information is pulled from a number of different data tables to generate a graphic report. In this example we drew from Customers, Sales, Line Items, Notes and Activities databases, and displayed them all on a single layout based on the Reports table.

As you can see this technique frees you from generating reports on a layout tied to the data. You can now move the graphs to a dedicated graphing layout, perhaps tied to your reports table. If you set up your scripts using this technique, you can bounce all around your database gathering information from different layouts and table occurrences and display them all nicely on a single management dashboard layout. Your only constraint now, is how long each report takes to generate.

Related Articles:
Interactive Charts driven by Virtual Javascript files
Creating and Storing 2D Bar Codes in FileMaker
‘FileMaker Go for the iPhone’ – a First Look