twelvetens

ExecuteSQL Query....

Discussion created by twelvetens on Nov 14, 2014
Latest reply on Feb 26, 2015 by twelvetens

Hi There,

 

Struggling (again!) with building my ExecuteSQL syntax.

 

In 'traditional' filemaker relationship definitions, I can use a carriage returned list of text values as the many side of a many-one relationship. Thus on on side, I can have:

 

Option A

Option B

Option C

Option D

 

And this will create a relationship with any joined tables as long as the match field caontains one (or many) of the Options A-D.

 

I'm struggling to re-create this matching using the WHERE segment of an ExecuteSQL clause. I'm trying this:

 

Let (

[

id = Items::items id ;

weight = weight in kg ; // weight is a number field

dic = user selected dispatch countries ; // a carriage returned list of text values.

so = user selected shipping options ; // a carriage returned list of text values.

dec = user selected destination countries // a carriage returned list of text values.

]

;

ExecuteSQL (

 

 

"SELECT " & SQLfield ( scenario planner::_scenario planner id )

& " FROM " & SQLTable ( scenario planner::_scenario planner id )

& " WHERE " &

SQLfield ( scenario planner::item id ) & " = " & id

 

& " AND " &

SQLfield ( scenario planner summary::max size ) & " ≥ " & weight

 

 

& " AND " &

SQLfield ( scenario planner summary::dispatch scenario planner country ) & " = " & dic

 

 

& " AND " &

SQLfield ( scenario planner summary::scenario planner shipping option ) & " = " & so

 

 

& " AND " &

SQLfield ( scenario planner summary::destination country ) & " = " & dec

 

 

& " AND " &

SQLfield ( scenario planner summary::match on format ) & " >0 "

*/

 

 

& " ORDER BY " & SQLfield ( scenario planner::sort order )

& " FETCH FIRST 3 ROWS ONLY"

;

 

 

"" ; "" )

 

 

//Close Let

)

 

But the match doesn't seemt to work - HELP - any takers????

Outcomes