AnsweredAssumed Answered

SQL Database List, Only Search Folders

Question asked by Brian Poelman on Dec 10, 2018
Latest reply on Dec 12, 2018 by Brian Poelman

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

                   from Variable

                   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