Parsing a date to ExecuteSQL SUM syntax

[Greg Hains]

Good morning.

Despite there being numerous examples out there (the following being just one of-), for the life of me  I am unable to get a specific calculation to work.
https://fmforums.com/topic/93770-sum-function-with-execute-sql/

Here's a simplified version of my query:

I have a table called DL_AT.
In this table I have three fields Date: (date), Service (text), Amount (number).

I have a second table called Invoices
In this table I have three fields: Date (date), Service (text), Total (number)

The relationship between the two tables is DL_AT::Date = Invoices::Date  AND   DL_AT::Service = Invoices::Service
I have a portal setup within Invoices, and as log as Date and Service is set, I can view the corresponding records in DL_AT within the portal. Easy.

The problem I am experiencing is doing a sum of the values in Amount in DL_AT (of the related records) using ExecuteSQL SUM.  In the portal I can see the values of DL_AT::Amount that I wish to add up, but I think there is something very wrong with the syntax of my statement.  I think it is table and field names within the SQL statement, and also perhaps the way I use the date variable.
I have used ExecuteSQL successfully a number of times before, but not parsing date variables. 

The version below is one of many different attempts to get the syntax right.  When the script is saved with no errors, I still just get a question mark or blank as a result.
(I know I have mixed use of reference to tables and fields here (quotes and not quotes) but this is the core of what I'm trying to do. 

I get an error sometimes saying "List Usage is not allowed in this calculation" and it's to do with the closed double quotes before the first parsed variable.

Let ( [
  ~svctype = Invoices::Service ;
  ~svcdate = Invoices::Date ;
  ~sql = "
    SELECT SUM ( "DL_AT | Amount" )
    FROM DL_AT 
    WHERE 
      "DL_AT | Service" = ? AND 
      "DL_AT | Date" = ? 
    " ;
  ~total = ExecuteSQL ( ~sql ; "" ; "" ; ~svctype ; ~svcdate )
  ] ;
  ~total 
)

Could somebody please show me where I am going wrong?

Cheers,
Greg
 

Edited February 23, 2020 by Greg Hains

[comment]

First and foremost, I don't see why you would need (or want) to use ExecuteSQL() to sum related records. A simple =

Sum ( DL_AT::Amount )

should give you the result you are after. Alternatively, define a summary field in the DL_AT table as Total of Amount and place it on the layout of Invoices.

Now, to answer your question: you need to escape the quotes you use within the test of your query. Try (untested) changing:

~sql = "
    SELECT SUM ( "DL_AT | Amount" )
    FROM DL_AT 
    WHERE 
      "DL_AT | Service" = ? AND 
      "DL_AT | Date" = ? 
    " ;

to:

~sql = "
    SELECT SUM ( \"DL_AT | Amount\" )
    FROM DL_AT 
    WHERE 
      \"DL_AT | Service\" = ? AND 
      \"DL_AT | Date\" = ? 
    " ;

 

Edited February 24, 2020 by comment

[Greg Hains]

Hi Comment,

Thank you for your reply.
I can use the relationship to get the figures, but I found that in an earlier project the ExecuteSQLs were running incredibly quickly, and as I had many thousands to do, I thought would start with that method. Sadly, I cannot get the example you sent through to work - I know it was untested and that's OK - but I will persevere to see if I can assemble enough examples that it will play ball for me.

Cheers,
Greg

[comment]

1 hour ago, Greg Hains said:

I cannot get the example you sent through to work - I know it was untested

It is tested now - see the attached demo.

 

1 hour ago, Greg Hains said:

I found that in an earlier project the ExecuteSQLs were running incredibly quickly,

In an unstored calculation field??

ESQLtest.fmp12

[Greg Hains]

Hi Comment.

Thank you very much for putting this together and sending it to me. I will use this method over the ExecuteSQL now.

Greg