Can anyone see whats wrong here:

Let     (     [     invoiceID = id ;     decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;     SQL = ExecuteSQL    (                     "                     SELECT SUM ( b.\"Amount\" )                     FROM \"Invoice Data\" b                     WHERE b.\"id_Invoice\" = ? AND b.\"Type\" = Rental                     "                     ; "" ; "" ;                     invoiceID                     )     ] ;     If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )     )

There are 2 tables Invoice and Invoice Data. The items added to the invoice can either be a rental, Sale or service item and want to total each type on the invoice layout.. Thank you 

Instead of Rental in the statement, try either... 'Rental' or ?

If the latter, then you'll need to change this...

    ; "" ; "" ; invoiceID 

to

    ; "" ; "" ; invoiceID ; "Rental" 

Also bear in mind case-sensitivity... for SQL, Rental is not the same as rental

A couple other thoughts:

1. in general, I avoid using SQL in calculated fields, for performance reasons

2. I have no idea what problem you're trying to solve with...

decimal = Left ( Evaluate ( 1/2 ) ; 1 )

...but I'm guessing there's a less convoluted way to go about it.

This:

 decimal = Left ( Evaluate ( 1/2 ) ; 1 ) 

can be simplified to:

 decimal = Left ( 1/2 ; 1 ) 

or even:

 decimal = Left ( .5 ; 1 )

This last one needs to entered with the correct decimal separator being used by the file at the time of entry - see:
http://fmforums.com/topic/71052-get-system-decimal-separator/

 

3 hours ago, Kevin Frank said:

2. I have no idea what problem you're trying to solve with...

decimal = Left ( Evaluate ( 1/2 ) ; 1 )

 

Well obviously they're trying to determine the decimal separator character being used by the file.

The main reason I want to use SQL is I dont want to create a relationship via key to get what I need. Could I please ask if someone could write the SQL code to achieve what I require. Above is giving me $0.00 and I don't know enough about SQL yet to solve..

Invoice - Prime key is id
Invoice Data - Foriegn Key is id_Invoice

Each invoice line item is either a "Rental" , "Product" or "Service" item and "Amount" is the total for the line.

I just want to show total amount for  Services, Products and Rentals... I thank you so much for your help... 

 

4 minutes ago, Ron Neville said:

The main reason I want to use SQL is I dont want to create a relationship via key to get what I need.

Hi Ron,

I believe it is a mistake to prefer SQL over relationships in a case such as this.  You will be displaying invoices and their lines and totaling them frequently.  ExecuteSQL() is great for gathering values while running a script but not for displaying related data.  And Kevin's statement about ExecuteSQL() being a larger performance hit is SPOT ON, particularly on tables which are known to be frequently viewed, worked in, and totaled.

Thank you Loretta.. You used to help me 10 years ago  and will create a relationship.. Thank you all

21 hours ago, comment said:

 

Well obviously they're trying to determine the decimal separator character being used by the file.

Obvious now that you mention it. Thanks.