8 Replies Latest reply on May 23, 2014 3:59 PM by philmodjunk

    Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data

    MorkAfur

      Title

      Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data

      Post

           I have the ExecuteSQL below that gives me a list in the "Result:" window of the data viewer. However, when I try to include these values (a single list of numbers like 436, 356, etc.), I get total garbage on the chart. I get values on either side of the chart going up and down the chart and the actual "Y values" all appear to have the same number.

           The type of chart is Column

           The X-axis data is blank as there is too much data for the "date" labels.

           The Y-Axis Title is: "Error Count"

           The Y-Axis data is the ExeucteSQL Query below. (I've tried using paragraph markers only, or just "", but get the same thing.)

           X-Axis and Y-Axis Options are default.

           ExecuteSQL ( "Select ROUND(NUM_ERRORS,0) from TABLE1
           WHERE NUM_ERRORS > 0 order by theDate asc, num_errors";
           ¶;
           ""
           )

           Sample section of part of the output is below.

           What gives here?

           Thanks in advance.

      StrangeChartDisplay.png

        • 1. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
          philmodjunk

               Did you use the "delimited data' data source option for your chart?

               That is the option to use with a list of data all in the same field--whether from the List function or ExecuteSQL. And the data should be separated by returns so use that paragraph symbol when setting this up with ExecuteSQL.

          • 2. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
            MorkAfur

                 Thanks Phil, but if I use Delimited Data, I get nothing in the chart at all.

                 I also changed the ExecuteSQL to use "P" markers for the last two and just for the next to the last entry, but made no difference.

                  

                 ???

            • 3. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
              MorkAfur

                   Phil, Others,

                   Here is a link to a quick sample FMP 13 file that demonstrates the issue I'm having.

                   I have two fields Value1 (x value) and Value2 (y value).

                   There is a simple ExecuteSQL which correctly shows, in the data viewer, the different values. Yet, when put in for the y data values, the data values are all the same. This is the same issue I see in my actual app.

                   No sort should be necessary since there are just 10 pairs of numbers.

              https://www.dropbox.com/s/iifh6c7373c5lk2/SampleExecuteSQLChart.fmp12

                   Hope this example helps demonstrate the problem.

                   (see the chart layout)

                   Look forward to suggestions.

              • 4. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
                philmodjunk

                     And did you also specify the same number of delimited items for the X series?

                • 5. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
                  MorkAfur

                       Yes, I just put the field for "Value1" (the x series) as the Data for the x-axis. That part is fine.

                       Were you not able to download the actual sample I created?

                       I'll leave it on DB for today.

                       Thanks,

                  • 6. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
                    philmodjunk

                         And as we discovered in your other thread, you did not actually have the same number of delimited items in the X series, you had a single value.

                         I try to avoid downloading shared files if I see a way to move forward without downloading. It probably was not the case with this demo file, but often, I find all kinds of different layouts and stuff in a file and end up spending a fair amount of time just figuring out which layout, script, field, etc to look at in order to answer someone's question.

                    • 7. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
                      MorkAfur

                           You know, I did try the x values at one time with this data series for the x-axis ("P" = Paragraph Mark): "1P2P3P4...P10", but I think in this case, I didn't choose the delimited option.

                           Using ExecuteSQL on both X AND Y seems to be the best method here to make sure you're not doing something wrong (me doing something wrong, that is). :)

                           In the current version of this logic in FMP, I'm using the new FMP 13 "FIRST 10" syntax, DESC ORDER BY, and the dynamic query field (using ?) so that the graph auto updates to show the last 10 values as the user moves the record slider. Nice!

                           ---

                           I'm cool with you not downloading the sample file. I created it solely for just the single purpose of demonstrating this single problem, however. It only had two layouts (x-y values) and the chart. I'm sure you would have found it extremely simple to look at and understand quickly

                           When stuck on difficult development issues (in other languages), I often create a quick sample "app", standalone program, or whatever to factor out the issue I'm having. Then, I can work on and test/fix just that code without having to recompile and redeploy, each time. Saves me lots of time in the long run.

                           Thanks again Phil. Hope you have a great holiday.

                           -m

                      • 8. Re: Strange Results Using ExecuteSQL for Chart (FMP 13 Pro) Y-Axis Data
                        philmodjunk

                             Yes, the FileMaker platform makes it very easy to set up a small file to test a new idea to see if it works. Often, I do not know the answer, (with complete confidence) to someone's question when they post it to this forum. I then create a small test file to confirm my idea for an answer before posting a suggested answer.