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

# ExecuteSQL problem

### 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

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

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 )

)