I am trying to create a SQL List to populate a drop down list on a datacard. I need the SQL list to be populated with all files in a specific folder that are in a specified state. I know how to generate a SQL List for all sub-folders in a specific folder, but I have been unable to modify the script to find files and I am at a complete loss for how to filter by state.
Here is the script for the sub-folders list:
Select Distinct P.Name
From Projects As P,ProjectTree As PT
Where P.ProjectID=
(Select PT.ChildProject where PT.ProjectID=
(Select distinct ProjectID from dbo.Projects where Name like 'Corrective Action Reports'
)
)
Also, I can run a SQL-Query (attached) to get the information I need, but when I cut and paste the Query into the SQL List, I get an error (see attached screen shot).
Any help would be appreciated.
Thank you,
Tara
Join the Status table in your query, it contains the states.
If you have access to the SQL server, it would be easier to create a view in the db and query that instead.