AnsweredAssumed Answered

Card list from SQL Datatype to return Folder value filtered by another Folder value

Question asked by Ronald Michaud on Oct 9, 2020

Hi Community,

I need your Kung Fu once again….

I need a card list from SQL Datatype that will return all of the Models that share the same Franchise.

I am able to get all of the Models listed in Folders.

Select Distinct vv.ValueCache

from VariableValue vv, Projects p

where p.ProjectID = vv.ProjectID and

p.Path is not null and

p.Deleted = 0 and

vv.VariableID =  ( Select VariableID

                   from Variable

                   where VariableName like 'Model') 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

 

I am able to get all of the Franchises listed in Folders.

Select Distinct vv.ValueCache

from VariableValue vv, Projects p

where p.ProjectID = vv.ProjectID and

p.Path is not null and

p.Deleted = 0 and

vv.VariableID =  ( Select VariableID

                   from Variable

                   where VariableName like 'Franchise') 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

 

But I can’t figure out how to modify the query to filter the Models based upon the Franchise.

I would like to use a static value for the Franchise.

 

In addition to listing the Models from all of the Folders of a given Franchise, I also want the results to include values from a Text list card. I have an example here where I have combined the card list values of the “Franchise” text list with the “Franchise SQL” list into a 3rd card list called “Franchise SQL Combined” using a union. This returns Franchises populated in the folder cards as well as Franchises listed in the Franchise card list.

Select CardListValues.Value From CardLists, CardListValues

Where CardLists.Name = 'Franchise'

And CardLists.Id = CardListValues.CardListID

union

Select Distinct vv.ValueCache

from VariableValue vv, Projects p

where p.ProjectID = vv.ProjectID and

p.Path is not null and

p.Deleted = 0 and

vv.VariableID =  ( Select VariableID

                   from Variable

                   where VariableName like 'Franchise') 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 1

 

If there is any way that this can be done for the Model SQL list too that would be awesome!

Outcomes