AnsweredAssumed Answered

ExecuteSQL problem

Question asked by cutykamu on May 20, 2015
Latest reply on May 20, 2015 by nicolai

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.

Outcomes