AnsweredAssumed Answered

Variables in the Select and FROM statements of ExecuteSQL()

Question asked by Shauniedarko on Nov 7, 2012
Latest reply on Feb 27, 2013 by ReuvenHoch

I've been banging my head on the desk all day, so I'm hoping someone might be able to help.

 

Some quick background: I'm using a virtual list to create a list of tasks for a project. The list is dynamic based on where in the solution the user is and what's been completed. This works well. What I want to do now, is add a field that grabs the task in the list and pulls it's completion status.

 

I've gotten 99% of the way there. To do this, I created a calculation field that takes the task and uses ExecuteSQL to grab the fieldname from the task table. Then I break that down into the field and table names and use those variables to get the status of the task from the project table. Without using variables, this works as expected. I've got a variable in the Select statement (which works) and a variable in the FROM statement that doesn't work. When I set up the query like this:

 

ExecuteSQL (

 

 

// ------------ BEGIN QUERY ------------

 

 

"SELECT ?

FROM Projects

" ;

 

 

// ------------ END QUERY ------ ------

 

 

// ------------ BEGIN FIELD AND ROW SEPARATORS ------------

 

 

" " ; "|*|" ;

 

 

// ------------ END FIELD AND ROW SEPARATORS ------------

 

 

// ------------ BEGIN ARGUMENTS ------------

// ------------ These arguments are pulled from the values you entered when running your query. You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

 

 

Projects::__ID

 

 

// ------------ END ARGUMENTS ------------

 

 

) ] ;

 

It returns the expected parameters. However, when I set it up with a "?" in the FROM statement and add the table name to the arguments after the argument for the Select statement, it tells me that the Table Name is not valid. Obviously, I know it's valid. I tried using quotes, everything. It just doesn't want to work. Is there something that I'm missing? I've used the Dataviewer to break down each step of this calculation and I've pinpointed it to the fact that I can't get the FROM statement of the query to accept my table name dynamically.

 

Thanks for the help!

 

Shaun

Outcomes