I want to use the values in a retool table as the 'right-hand-side' of a SQL WHERE clause to query my database. Seems like a noob question - I'm a noob!
This doesn't work, I think, because the data isn't structured right (not a list of objects, not an array (not sure on the right language)). I imagine this is quite easy. Any assistance would be great!
You may want to try using = ANY (formatDataAsArray({{table1.data.ProjectID}}))
Posting more information and screenshots always leads to a faster solution.... 
Thanks @ScottR I'm getting the following error:
The retool table:
Populated using the following query:
select
Projects.ProjectID,
Projects.ProjectName,
ProjectType.ProjectDescription,
Projects.ProjectType,
Projects.ProjectSubType,
DesignCriteria.GIAIncludingBasement,
DesignCriteria.Address,
Cost.CostTotal,
Cost.CostTotal/DesignCriteria.GIAIncludingBasement AS [Cost Per m2],
ProjectImages.ImageURL,
ProjectImages.ImageURL1,
ProjectImages.ImageURL2,
ProjectImages.ImageURL3,
Projects.County,
Cost.PracticalCompletionDate
from ((((Projects
INNER JOIN DesignCriteria ON Projects.ProjectID = DesignCriteria.ProjectID)
INNER JOIN ProjectType ON ProjectType.ProjectID = Projects.ProjectID)
INNER JOIN Cost ON Cost.ProjectID = Projects.ProjectID)
INNER JOIN ProjectImages ON Projects.ProjectID = ProjectImages.ProjectID)
WHERE (1 = {{ !cascader1.value['3'] ? 1 : 0 }}
OR Projects.ProjectType = {{cascader1.value['3']}})
AND (1 = {{ !select1.value ? 1 : 0 }}
OR ProjectSubType = {{ select1.value }})
AND (1 = {{ !select2.value ? 1 : 0 }}
OR County = {{ select2.value }})
my mistake -- needed more coffee....
= ANY ({{formatDataAsArray(table1.data.ProjectID)}})
but since the data may already be an array you may only need to do the following:
= ANY ({{table1.data.ProjectID}})
What db are you using?
You can also try replace = ANY with IN
OK so two things
perhaps dbo.ProjectID is needed and you need to check the following box in the Resource being used...
Try the first and then the second option...
Thanks, Scott still no luck I'm afraid. I'm using an mssql database. The 'best outcome' I can get is a blank (but apparently successful) query
Using the above queries without IN or ANY
EG:
Hey @thomasatkinson! Hopping in here with Scott to try helping as well
Have you tried
...WHERE ProjectID IN(array)
Syntax from this doc: https://docs.retool.com/docs/sql-cheatsheet#use-arrays-in-queries
And the prepared statements shouldn't need to be disabled since the value is the only dynamic part of the query! They would need to be disabled if your operator or column name were dynamic
