3 Replies Latest reply on May 26, 2016 3:17 AM by anko

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

    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

        • 1. Re: Issue with dividing numbers (using /) in SELECT clause of excuteSQL function
          anko

          I manipulated the example provided by Soliant Consulting, to show one trace with original values and the other trace with the same original values / 10:

           

           

          When the original value is less than 10, the expression "value / 10" returns nothing. When the original value is a ten-fold, "value / 10" returns really value / 10. When the original value is not a ten-fold, "value / 10" returns value.

           

          Please ignore the strange X-axis labels.

          • 2. Re: Issue with dividing numbers (using /) in SELECT clause of excuteSQL function
            anko

            Kinda silly, but I figured out the cause / solution myself.

             

            First, as a workaround I added a Calculated Field to my table, using the same calculation: (A*256+B-10000)/10. The values in the new field were calculated properly. Then I changed the graph to use the new Calculated Field. Unexpected, the graph was still distorted.

             

            Then I noticed, the numbers in the Calculated Field showed up as (for example) -41,3. And not -41.3. This is because this is how I have configured it on my Mac (living in the Netherlands, that seems like a proper thing to do). After changing the number format on my Mac, the graph was no longer distorted.

             

            I suspect a conflict between the comma as part of my number format and the comma of the comma separated list of values produced by the executeSQL statement.

            1 of 1 people found this helpful
            • 3. Re: Issue with dividing numbers (using /) in SELECT clause of excuteSQL function
              anko

              Can't see how I could have missed it the other day, but I see now that lots of stuff has been written about the exact same issue in the last few years . Sorry for that.