2 Replies Latest reply on May 20, 2015 7:42 AM by nicolai

    ExecuteSQL problem

    cutykamu

      Title

      ExecuteSQL problem

      Post

       

      i have a minor problem in filemaker pro 12

      i'm using ExecuteSQL as calculation in a field as below. i'm  new to SQL functions.

      i need a chart to see top 5 clients in the current year where X axis should be client (company field) and Y axis is (total amount field)  and below is the single calculation field.

      the field is working fine and the results are correct its just i can't use it in the chart (chart is showing delimited data). 

      Let     (
          [
          year = Company Dashboard::Current Year ; 
          decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ; 
          SQL = LeftValues(ExecuteSQL    ( 
                          "
                          SELECT a.\"Company\", SUM(b.\"Total\") as InvoiceTotal 
                          FROM \"Customers\" a
                          LEFT JOIN \"Invoices\" b ON a.\"CUSTOMER ID MATCH FIELD\" = b.\"CUSTOMER ID MATCH FIELD\"
                          WHERE b.\"Year\" = ? 
                          GROUP BY a.\"Company\"
                          ORDER BY InvoiceTotal DESC
                          "
                          ; "|" ; ¶ ; 
                          year   
                          ) ; 5 )
          ] ; 
          
          Substitute ( SQL ; "." ; decimal )

          )

      any help i will appreciate.

        • 1. Re: ExecuteSQL problem
          nicolai

          Correct me if I am wrong, but this is not ExecuteSQL problem, but a chart problem.

          So instead of calculation, could you give details on your chart setup?

           

           

          • 2. Re: ExecuteSQL problem
            nicolai

            Sorry I did not get the pictures, Iso I have to guess.

            You probably have a problem separating the results of your calculation into X and Y axis. The easiest way to do it is by running two separate calculations:

            for x-Axis

            Let     (
                [
                year = Company Dashboard::Current Year ; 
                decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ; 
                SQL = LeftValues(ExecuteSQL    ( 
                                "
                                SELECT a.\"Company\"
                                FROM \"Customers\" a
                                LEFT JOIN \"Invoices\" b ON a.\"CUSTOMER ID MATCH FIELD\" = b.\"CUSTOMER ID MATCH FIELD\"
                                WHERE b.\"Year\" = ? 
                                GROUP BY a.\"Company\"
                                ORDER BY InvoiceTotal DESC
                                "
                                ; "" ; ¶ ; 
                                year   
                                ) ; 5 )
                ] ; 
                
                Substitute ( SQL ; "." ; decimal )

                )

            and than for the Y-axis:

            Let     (
                [
                year = Company Dashboard::Current Year ; 
                decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ; 
                SQL = LeftValues(ExecuteSQL    ( 
                                "
                                SELECT SUM(b.\"Total\") as InvoiceTotal 
                                FROM \"Customers\" a
                                LEFT JOIN \"Invoices\" b ON a.\"CUSTOMER ID MATCH FIELD\" = b.\"CUSTOMER ID MATCH FIELD\"
                                WHERE b.\"Year\" = ? 
                                GROUP BY a.\"Company\"
                                ORDER BY InvoiceTotal DESC
                                "
                                ; "|" ; ¶ ; 
                                year   
                                ) ; 5 )
                ] ; 
                
                Substitute ( SQL ; "." ; decimal )

                )