ExecuteSQL Syntax error

[Spidey]

Hi,

I have two table: Invoice and Customer.  I like to have the total of all the invoice for a customer between certain date in the Customer portal that show all the customers, but I got a error when I try to debug..

ExecuteSQL("SELECT SUM(I.TotalAmount) FROM Invoice I JOIN Customer C ON I._kf_CustomerID = C.__kp_CustomerID WHERE date(I.InvoiceDate) between date(C.SearchFromDate ) and   date(C.SearchToDate )" ; "" ; "" )

I have an error and couldn't figure it out.  Thanks...

KC

[comment]

A test file would have been helpful. As it is, I can only point out what jumps out at first glance:

Assuming that InvoiceDate and the two "search" fields are defined as Date fields, there is no need to try and convert them to dates. You can reference them directly. If - for some strange reason - they were defined as Text fields, then you would need the DATEVAL() function - not the DATE() function - to convert them to dates.

I don't see where you limit the query to a specific customer. Note that it is not necessary to define a join for this; you can do simply:

SELECT SUM(TotalAmount) 
FROM Invoice
WHERE _kf_CustomerID = ?

and pass the current customer's ID as an argument.

Also, IIRC, field names that begin with an underscore must be quoted.

---
P.S. IMHO, ExecuteSQL() is not the best tool to achieve your stated purpose. I would suggest using a summary field placed inside a portal filtered to show only invoices between the two dates.

 

Edited September 24, 2020 by comment