anko

Issue with dividing numbers (using /) in SELECT clause of excuteSQL function

Discussion created by anko on May 25, 2016
Latest reply on May 26, 2016 by anko

Hi All,

 

This is my first attempt to making a chart in FM. I am using a (demo) license of the latest version.

 

I have a table with test data from which I want to create a graph. The first field (DELTA) is time elapsed since beginning of test (expressed in milliseconds). This is going to be the source for my X-axis. The second field (A) and third field (B) contain actual data and I want to create a single trace for these fields, using this formula:

 

(A * 256 + B - 10000) / 10

 

When I create a graph straight forward, it all works well. But I have other rows in my table that I need to filer out dynamically, based upon a fourth field (HEADER). So, I have switched to using executeSQL statements, pretty much following Dynamic Charting in FileMaker - Soliant Consulting . For my X-axis values I use:

 

ExecuteSQL("SELECT delta FROM TestData WHERE header = ? ORDER BY delta"; ""; ""; "287")

 

and for my Y-axis values:

 

ExecuteSQL("SELECT (A*256+B-10000)/10 FROM TestData WHERE header = ? ORDER BY delta"; ""; ""; "287")

 

But the resulting graph is distorted. When I try with this expression for the Y-axis values:

 

ExecuteSQL("SELECT (A*256+B) FROM TestData WHERE header = ? ORDER BY delta"; ""; ""; "287")

 

I get this:

Which is fine. When I adapt the Y-axis expression to:

 

ExecuteSQL("SELECT (A*256+B-10000) FROM TestData WHERE header = ? ORDER BY delta"; ""; ""; "287")

 

I get this:

 

 

So far, so good. But when I add division by 10:

 

ExecuteSQL("SELECT ((A*256+B-10000)/10) FROM TestData WHERE header = ? ORDER BY delta"; ""; ""; "287")

 

The graph gets distorted:

 

It looks as if only a few of the selected values were divided by 10 but the most were not. It is almost as if only values ending with 0 are divided and other values are not. Any hints / tips / suggestions are appreciated.

 

For those who wonder: the data I have is captured by monitoring CANBUS traffic via the OnBoardDiagnosis (OBD) port of a Plugin Hybrid Electric Vehicle, and represents a Request for Torque sent by the main ECU to the front E-Motor. Values are expected to range from approx. -200 to +200 (Nm). The value 287 represents the type of CANBUS frame (header) that contains this information. Other frame types are used for other frames that carry completely different information. By database contains a mix of close to 100 different frame types.

 

Thanks,

 

Anko

Outcomes