thirdsun

Tricky ExecuteSQL Situation - Help with Query needed

Discussion created by thirdsun on Nov 6, 2013
Latest reply on Dec 2, 2014 by beverly

Hello,

 

While working on an on-the-fly reporting module for a large WAN-hosted FileMaker solutions I've seem to have run into some limitations of the SQL Engine that is used in FileMaker. Here's a rough description of said module:

 

There's a large solution with various tables and sections, like Sales, Orders, Production, Working Schedule, etc. The reporting module has several tables that contain pre-defined parts of an SQL query, which can later be mixed and matched in a human-readable and easily approachable fashion in the, well, let's call it Report Editor. This editor puts the query together and outputs a result that is customized to be processed by the HighCharts JS Charting library, which visualizes the results in a web viewer. All of this works quite well and the chart is displayed fine - chart types (like pie, bar, scatter, radar charts) and the data (basically the SELECT, FROM, WHERE, ORDER BY, GROUP BY statements - though the user never sees these raw queries) can be adjusted by the user on the fly.

 

The data needs to match a certain format in order to be processed by HighCharts. Basically a data series is bunch of key-value pairs which I collect with the SQL query and it looks like this:

 

{ name: "Sales", data: [ [" September 2013 ", 30555 ], [" Oktober 2013 ", 24870 ], [" November 2013 ", 4155 ], [" März 2013 ", 24850 ], [" Mai 2013 ", 43620 ], [" Juni 2013 ", 38025 ], [" Juli 2013 ", 35625 ], [" Januar 2013 ", 19758 ], [" Februar 2013 ", 19785 ], [" August 2013 ", 36910 ], [" April 2013 ", 39835 ] ] },

 

The key becomes the label or x-axis point of the chart while the value is the y-axis point, or actual datapoint. Again, all of this works right now. However often the report needs to be sorted chronologically in which I need to SELECT a field for the label (X), a field for the value (Y) and a field for the ORDER BY statement.

 

First let's take a simple query that works as example. Nothing fancy here:

 

Note: I left out all the stuff that formats the result to a HighCharts compatible data series.

 

SELECT month_year_as_text, SUM(price)

FROM line_items

WHERE date >= '01.01.2013' AND brand = 'SomeBrand'

GROUP BY month_year_as_text

 

This works, as expected.

 

Now I'll add the order by statement:

 

SELECT month_year_as_text, SUM(price) AS sales

FROM line_items

WHERE date >= '01.01.2013' AND brand = 'SomeBrand'

GROUP BY month_year_as_text

ORDER BY sales DESC

 

This works fine again.

 

Now for the chronological order I need to use a field that shouldn't be displayed in any other part of the chart or the result, something like this:

 

SELECT month_year_as_text, SUM(price) AS sales

FROM line_items

WHERE date >= '01.01.2013' AND brand = 'SomeBrand'

GROUP BY month_year_as_text

ORDER BY date

 

Of course date is a valid field in the line_items table, however the query will fail returning a ?. I checked back with various SQL documentations and it seems to be perfectly fine to ORDER BY a column that is not part of the SELECT statement. Well, even if I add the date field to the SELECT part of the query it won't work as I now (as far as I know) also have to add the field to the GROUP BY statement which moves me even further away from the result I need.

 

Unfortunately it's very hard to debug those ExecuteSQL queries since FileMaker won't give any reason for a failed query. Does anyone here know how to solve this problem and ORDER BY a field that shouldn't be part of the rest of the result. Or asked in a different way: What would be a query that leads to this result:

 

Month Sales

------------------------------------

January 2013 434634

February 2913 32455

March 2013 74112

...

You get the idea.

 

By the way I also tried to use the SQL Date fucntions hoping to be able to just deal with date field (no helper fields like month_year_as_text) but apparently they aren't supported in FileMaker's ExecuteSQL.

 

Any help is appreciated. Thanks for your time.

Outcomes