Troubleshoot SQL Syntax Error

[adehner]

I am relatively new to sql queries in Filemaker, and I'm receiving the ? in response to the following queries. Because I don't have the advanced version of FMP, I used Evaluate() to return the error code 8310 (syntax error). However I can't figure out where my syntax error is. Hopefully more experienced eyes will spot my problem(s).

The following is entered in a calculated field that returns text:

Let( [

$initials=ExecuteSQL("SELECT TaskAssignedTo FROM workflow WHERE" & Quote("_IDDocLog") & "=workflow::" & Quote("_IDDocLog") & "AND workflow::TaskNumber=9"; "" ; "" );

$name=ExecuteSQL ("SELECT FullName FROM staff WHERE AccountName= $initials"; "" ; "") ];

$name )

Thanks!!

 

Oops I meant to say that I used EvaluationError()

[Kris M]

ExecuteSQL("SELECT TaskAssignedTo FROM workflow WHERE" & Quote("_IDDocLog") & "=workflow::" & Quote("_IDDocLog") & "AND workflow::TaskNumber=9"; "" ; "" );

This is a non functional SELECT statement. I cant even begin to guess what you are trying to accomplish.

You should look into using ? as variable value placeholders in SQL...i.e

ExecuteSQL ( "SELECT field FROM table WHERE field=? and WHERE field=?" ; "" ; "" ; value_for_first? ; value_for_second? )

Edited April 25, 2016 by Kris M

[adehner]

Thanks Kris M.

I replaced what I was using with your placeholder suggestion (below) but I'm still getting the 8310 error. One problem might be the underscores. But there may be other problems as well.

ExecuteSQL("SELECT TaskAssignedTo FROM workflow WHERE _IDDocLog=? AND workflow::TaskNumber=?"; "" ; "" ;  workflow::_IDDocLog ; 9);

What am I trying to accomplish? I have 2 tables, workflow (see attached file) and document. They are related by fields workflow::_IDDocLog and document::_IDDocLog. For one record in document, there are 16 related records in workflow. Each of the related records in workflow has a taskNumber, value incrementing from 1 to 16. I want to grab the value of workflow::TaskAssignedTo from the record in workflow with workflow::TaskNumber=9.

If there is a better way to do this than ExecuteSQL(), I'm all ears!

 

[adehner]

I think I got this. The following works...

GetNthRecord(workflow::TaskAssignedTo ; 9)

[LAIRY]

Hi,

Try use "workflow._IDDocLog" (only one dot for all names of fields) and not workflow::_IDDocLog.

Yes underscores may be a problem at the begining of names

Have a good day

G