I'm having an issue with an SQL database search used to make a list in a template card. The list mostly works, however I'm getting some extra data in their that I don't want. We have many project folders, each with many files within. I want to get a list of all of the "Client" variables specified in the various project folders.
1) I'm getting all the "Client" variables from the folder; however I'm also getting "Client" returns from .slddrw files within these project folder. I want to exclude these (i.e. only grab variables from folders).
2) I'm also getting values returned that are not currently used by any folder or file within the vault (e.g. "Test Client A"). If I do a PDM search for all folders/files with Client = "Test Client A" I get no results so I don't know where this is coming from. I have attempted to only search latest revisions of each file, but maybe I'm doing this incorrectly?
Any assistance would be much appropriated. My code to date is below:
Select Distinct vv.ValueCache
from VariableValue vv, Projects p
where p.ProjectID = vv.ProjectID and
vv.VariableID = ( Select VariableID
where VariableName like 'Client') and
vv.RevisionNo = ( Select Max( Vv1.RevisionNo )
From VariableValue vv1
where vv1.VariableID = vv.VariableID and
vv1.ProjectID = p.ProjectID and
vv1.ValueText is not null ) and
vv.ValueCache != ''
order by vv.ValueCache ASC