1 Reply Latest reply on Mar 2, 2013 9:47 AM by Jean-LucBrousseau

    Erratum in FTS 12 Lesson 7.3, example 7.3.3

    Jean-LucBrousseau

      I am working on the French FTS program and I have a problem with the SQL sequence which does not produce the expected result. I reproduce here the English equivalent.

       

       

      Example 7 .3 .3

      Again working with 07a_Bonsai.fmp12 file, navigate to the Yearly Totals tab of the Customer_Detail layout. In this example, you will use the ExecuteSQL function to generate delimited data for a chart that shows the customer’s order totals by year.

      1. Enter Layout mode and place a new chart object on the Yearly Totals tab panel.
      2. In the Chart Setup dialog, specify Current Record (delimited data) as the Data Source.
      3. Click back to the Chart section of the Chart Inspector. This chart would work well either as a Column chart or Line chart, but for purposes of visual clarity, select Column as the Type for this example.
      4. For the X-Axis Data, select the Specify Calculation... option and enter “2011¶2012¶2013” as the formula (including the quotes).

        The Bonsai file only contains data for these three years, and hard-coding this list as the label series simplifies the example. The other concern with dynamically generating the label series is properly accounting for “holes” in the data; if a customer placed orders in 2011 and 2013 but not 2012,
        the label series dynamically derived from the order data would only contain 2011 and 2013, thereby presenting a very misleading picture of the data.

      5. For the Y-Axis Data, you will build a return-delimited list using the results of three ExecuteSQL( ) functions that query the order table and aggregate the sales for the current customer for each year. Do this by using the following formula:

        Let ([
        Cust = CUSTOMER::__kp_CustomerID;
        SQL = “Select Sum (Total) from ”Order” Where

        ”_kf_CustomerID” = ? and DateOrdered>= ? and DateOrdered<= ?”];

        ExecuteSQL(SQL;””;””;Cust;Date(1;1;2011);Date(12;31;2011))&”¶”&

        ExecuteSQL(SQL;””;””;Cust;Date(1;1;2012);Date(12;31;2012))&”¶”&

        ExecuteSQL(SQL;””;””;Cust;Date(1;1;2013);Date(12;31;2013))

        )

        See Lesson 5.5 for a detailed discussion of the syntax of the ExecuteSQL() and Let() functions.
        It can be helpful to use the Data Viewer feature of FileMaker Pro Advanced to test and experiment with ExecuteSQL queries before using them in a chart.

       

      6. With the label series and data series in place, all that remains is formatting. Use trial and error
      to format the chart to your liking; when you are finished, it should appear similar to Figure 7.3.12 (for the customer Lavish Landscape Services).

      Note: Most of the order data in the Bonsai file is for the year 2012; some customers do not have orders in 2011 or 2013, so do not be alarmed if your chart shows big changes from year to year.

      Figure 7.3.12

       

      My result is a blank graph with the proper information for X and Y axis. Nothing more.

       

      I have tried to Copy/Paste the sequence, to write manually the whole sequence, but I get the same result: NO GRAPH.

      I suspect the SQL instructions in step 9 to be defective.

      Has there been comment on this yet?

      Thanks

      Jean-Luc Brousseau