How to use a script to create data for a Filemaker Chart

In my previous post on Filemaker Charts, I touched on how to create a table and use it to create charts. In daily operation, the data are actually daily transactions. it is never meant for charts. How then could we transform the data into charts. It is easily done in Excel where user can create summaries to be used in charts. There is a summary in Filemaker too but it cannot be used for charts.

Here I propose using scripts to populate a summary table from a details table. The example code will use Filemaker Charts as an example.

The first thing to fill is "Months" field. Create a calculated text field in the main table and call it "YearMonth". It is necessary to add year as the data may span a number of years. Use the formula below as calculation assuming the field that shows the date of record creation is called CreateDate.

Year(CreateDate) & Case(Month(CreateDate) < 10;"0" & Month(CreateDate); Month(CreateDate)

The above will generate a text like "201604" for current month. It is necessary to insert "0" for single digit month so that the sequence of the data created will be correctly done.

Create the summary table and chart according to examples shown in Filemaker Charts. Create a script called "Summary".

Usually a summary table will pull the data for the entire last year. So the script will start with getting the year.

Set Variable($year;Year(Get(CurrentDate)-1

With the year, append the month value in a loop like below

Set Variable($mth;1)
Loop
   If($mth < 10)
      Set Variable($ym; $year & "0" & $mth)
   else
      Set Variable($ym; $year & $mth)
   End If
New Record/Request
...
Set Variable($mth;$mth+1)
Exit Loop If($mth>12)
End Loop

The above script only set the loop to get the correct YearMonth value in 12 loops. We need to fill the "Month" field in the summary table. The following script assume you are already in the summary table layout using summary table as database. The subsequent scripts are to be inserted in the line before "Set Variable($mth;$mth+1)" represented by "..."

Set Field(Summary::Month;$ym)

The next task is to fill SiteA, SiteB, and SiteC with the correct summary data from the sales data field "TotalSales". Well, you guess it right, we will use ExecuteSQL function.

Set Field(Summary::SiteA; ExecuteSQL("SELECT Sum(TotalSales) FROM MainTable WHERE SiteLocation = 'SiteA' AND YearMonth=?";"";"";$ym)

Repeat it for SiteB and SiteC.

Run the script and you will get 12 records with the completed sales figure for last year. Your chart will then be able to show the yearly sales figure for the three sites.

A reminder: You need to delete all records of previous data in summary table. Filemaker Charts does not have the facility to define which data to use in the table.  Its useless to set a filter as it only applies while the table is in view.

A suggestion is to copy the data into a history record of the yearly performance table. Extract the necessary data back into summary table to show the yearly figure for the year you want to see the chart.

One last thing, The SQL is a very simple one. You could extract the whole year data for all three sites in just one ExecuteSQL

Set Variable($data; ExecuteSQL("SELECT YearMonth,SiteLocation,Sum(TotalSales) FROM MainTable WHERE YearMonth LIKE '" & $year & "*' GROUP BY YearMonth, SiteLocation" ;"";",";)

Use a loop to extract the various rows using GetValue($data;$loop) then extract each item by replacing "," with "¶" then use GetValue to extract the items.

The complex SQL method only useful if you extract just the sales figure. In many situations you need to get more result than that. Usually they use different criteria like TotalSales > 1000000, Total Sales >100000000. One SQL will not be able to extract such criteria and it will be difficult to update the table if you use more than one SQL. Since the first SQL will fill all the 12 records already. You will then have to use filter to choose the right record then add the subsequent SQL result into the correct record.

It is impossible to use ExecuteSQL to update the records. ExecuteSQL does not allow Insert or Update (3rd party SQL plugin does have this capability).  It is easier to use simple SQL and then create a record to fill the appropriate fields with the correct SQL result with the resultant multiple SQL that loads the system runtime. The choice is up to you.