Jump to content

ExecuteSQL Syntax error


This topic is 1282 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

invoice.png

customer.png

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 1282 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.