1 2 Previous Next 23 Replies Latest reply on Dec 2, 2014 4:47 AM by beverly

    Tricky ExecuteSQL Situation - Help with Query needed

    thirdsun

      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.

        1 2 Previous Next