Jump to content

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

Recommended Posts

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 by Lee Smith
made post more readable
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 > )

)

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2810 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.