AnsweredAssumed Answered

Help to decipher an ExecuteSQL statement in Starter Solution calc.

Question asked by jenbee on Oct 2, 2015
Latest reply on Oct 11, 2015 by jenbee

Hi

I'm on the 'invoices' starter solution in FMP Advanced 14.0.2

 

and the TotalAllSQL field in Company Dashboard table.

They've used a calculation field with a SQL SUM function to add the values in a portal as an alternative to creating a report with grouped data and subtotals.  Here's the definition of calculation field: TotalAllSQL

Let  (
[
month =  Company Dashboard::Current Month ;
year =  Company Dashboard::Current Year ;
status =  Status Filter ;
decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
SQL =  ExecuteSQL (
     "
     SELECT SUM ( a.\"Total\" )
     FROM \"Invoices\" a " &
     If  (
      IsEmpty ( status ) ;
      "WHERE a.\"Month\" = ? AND a.\"Year\" = ? AND a.\"Status Order\" IN ( 0 ,1, 2 ) " ;
      "WHERE a.\"Month\" = ? AND a.\"Year\" = ? AND a.\"Status Order\" = ? "
      )
     ; "" ; ""  ;
     month ; year ; status
     )
] ;

If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )

)

 

 

 

 

I'm just trying to work my way through understanding it.  I've done a basic SQL course before, but have way more experience with FileMaker on its own than I do with SQL.  Firstly what's the purpose of the '  a. ' in the part that I've highlighted red?  That's what I want to know , hope yous can help

Outcomes