Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2415

Help to decipher an ExecuteSQL statement in Starter Solution calc.

Show all headers | View raw

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 theory SQL SUM() Function should be like: SELECT SUM(column_name) FROM table_name , so I don't really know what the ' a ' or ' a. ' are there for unless they're something to do with the question mark which I believe means variable , and will be defined by user?!

Back to comp.databases.filemaker | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

Help to decipher an ExecuteSQL statement in Starter Solution calc. JayBee <Jennifer_Beecroft@hotmail.com> - 2015-10-02 09:02 -0700
  Re: Help to decipher an ExecuteSQL statement in Starter Solution calc. Howard Schlossberg <howard@nospam.fmprosolutions.com> - 2015-10-02 09:46 -0700
    Re: Help to decipher an ExecuteSQL statement in Starter Solution calc. JayBee <Jennifer_Beecroft@hotmail.com> - 2015-10-02 10:21 -0700
      Re: Help to decipher an ExecuteSQL statement in Starter Solution calc. Howard Schlossberg <howard@nospam.fmprosolutions.com> - 2015-10-02 11:08 -0700
  Re: Help to decipher an ExecuteSQL statement in Starter Solution calc. JayBee <Jennifer_Beecroft@hotmail.com> - 2015-10-11 11:16 -0700

csiph-web