New to SQL

[Ron Neville]

HI Guys

I am new to SQL in filemaker and am trying to get SQL Data into variables and hope you can help please.

I am running a script trigger after a user exits the "sku" field in a sku added to an invoice. I set the sku into $id and I want to get itemName and price from the products table for that sku and add the result to the variables $itemName and $price. Could I ask what the code would look like?

Edited August 2, 2016 by Lee Smith
made post more readable

[bcooney]

Hmm.. why? Not being difficult, but this is a scenario where you could use the Lookup function to copy the item name and price into the invoice line item table based on a relationship by product id (not sku).

SQL isn't necessary here, imho. Check out the invoice sample file. 

[Wim Decorte]

Agreed.  And if not a Lookup (which has to be set at the schema level) then a simple Set Field / Set Variable using what I'm sure is an existing relationship will do just fine instead of having to do one ExecuteSQL() call then split the result to get to the two pieces of data and then setting those into the fields/variables.

 

Other than that, something like this (note that I am using the _FieldRef custom functions to make the sql query safe against TO and field name changes - as opposed to hard coding the TO and field names):

 

Let( [

_name = _FieldRef_getField ( <TO:: field name for name>  ; True ) ;

_price = _name = _FieldRef_getField ( <TO:: field name for price>  ; True ) ;

_table = _FieldRef_getTO (  <TO:: field name for name> ; True ) ;

_sku = _FieldRef_getField ( <TO:: field name for sku> ; True ) ;

_sql =

"SELECT " & _name & "," & _price &
" FROM " & _table & 
" WHERE " & _sku & " = ?"

];

ExecuteSQL( _sql ; "" ; "" ; <TO::field name for sku input > )

)

 

 

[Fitch]

I do something like that with custom functions too, but I like to write the query in a more readable (IMHO) way, like:

"SELECT _name, _price
 FROM  _table
 WHERE  _sku = ?"

... followed by a Substitute function to insert the actual field and table names.