SQL SELECT with WHERE clause uses a checkbox field

[eoneon123]

Hey guys, I was wondering if anyone knew the answer to this problem. I'm trying to generate a value list based on a checkbox field in another table. I know how to achieve the value list portion of it, as this works when I change the checkbox field to a text field with a single value. But I can't seem to avoid getting the "?" when I use a checkbox field for the WHERE criteria. Any ideas? 

 

Let([

 

      ~sql = " SELECT Field1 FROM Table1 WHERE CheckboxFieldValue = ?"

 

      ];

 

      ExecuteSQL ( ~sql ; "" ; "¶" ;  "Value" )

 

      )

[Ocean West]

remember a check box field is a normal field with return separated text, so the if the checkbox field had 

 

cat¶dog¶mouse all selected and you are trying to find dog you may need to use a more loose search because the the contents doesn't equal the string.

 

or you may need to create a Field that uses pattern match such as isDOG and results to a boolean. using that in your sql.

[eoneon123]

Sorry to be dense, but what do you mean by a "more loose search"? I'm pretty new to SQL. I'm equally confused as to the second alternative. 

[Ocean West]

i was thinking of the LIKE operator

 

http://www.w3schools.com/sql/sql_like.asp

 

-

If your searching for DOG via SQL create a field that is: PatternCount ( table::CheckBoxFieldValue ; "dog" ) this field will evaluate to 1 or 0 use it in your field

 

Let([
 
      ~sql = " SELECT Field1 FROM Table1 WHERE isDOG = ?"
 
      ];
 
      ExecuteSQL ( ~sql ; "" ; "¶" ;  "1" )
 
      )

[eoneon123]

I'm going to work on the second option now, but the query worked when using the "LIKE" operator. Just one quick question, what if I wanted to use a global variable as a match for the CheckboxFieldValue? Meaning, how do I frame the WHERE Value and the % operator when said Value is a global variable? By the way, thanks so far for the help.

 

 

Let([

 

      ~sql = " SELECT Field1 FROM Table1 WHERE CheckboxFieldValue = ?"

 

      ];

 

      ExecuteSQL ( ~sql ; "" ; "¶" ;  $$Value% )

 

      )

 

 

[Ocean West]

you can get some more ideas from

 

http://fmforums.com/forum/files/file/25-sql-builder/ 

 

or 

 

http://www.seedcode.com/cp-app/ste_cat/sqlxfree

[eoneon123]

I downloaded both files and will take a deep look into them but it looks like the pattern count method was the way to go. Thanks again for the help!