V_QUERY_SEARCHLIST
Provides support for querying on Search List metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(60) | Name | |
| DESCRIPTION | nvarchar(1000) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| RECORDTYPENAME | nvarchar(50) | yes | Record type |
| IMPLEMENTATIONTYPENAME | varchar(3) | Implementation type | |
| IMPLEMENTATIONDESCRIPTION | nvarchar(264) | yes | Implementation description |
| SECURITYUIFOLDER | nvarchar(255) | Security UI folder | |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| IMAGEKEY | nvarchar(max) | yes | Image key |
| QUICKFINDFIELD | nvarchar(128) | Quick find field | |
| SEARCHLISTSPECXML | xml | Search list spec xml | |
| HASOPTIONALFILTERFIELDS | bit | yes | Has optional filter fields |
| HASOPTIONALOUTPUTFIELDS | bit | yes | Has optional output fields |
| PRIMARYKEYFIELD | nvarchar(100) | yes | Primary key field |
| TRANSLATIONFIELD | nvarchar(100) | yes | Translation field |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| PROCEDURENAME | nvarchar(128) | Procedure name | |
| CREATEPROCEDURESQL | nvarchar(max) | yes | Create procedure sql |
| ASSEMBLYNAME | nvarchar(128) | Assembly name | |
| CLASSNAME | nvarchar(128) | Class name | |
| FORMUICOMPONENTTYPE | varchar(16) | Form UI component type | |
| FORMCOMPONENTASSEMBLYNAME | nvarchar(max) | yes | Form component assembly name |
| FORMCOMPONENTCLASSNAME | nvarchar(max) | yes | Form component class name |
| WEBUICOMPONENTTYPE | varchar(17) | Web UI component type | |
| WEBUIMODELASSEMBLYNAME | nvarchar(max) | yes | Web UI model assembly name |
| WEBUIMODELCLASSNAME | nvarchar(max) | yes | Web UI model class name |
| WEBUIEXTERNALRESOURCEURL | nvarchar(max) | yes | Web UI external resource url |
| ID | uniqueidentifier | System record ID | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| SPECUINAME | nvarchar(60) | Name override | |
| UINAME | nvarchar(60) | Display name |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:30:43 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SEARCHLIST AS
with xmlnamespaces ('bb_appfx_searchlist' as tns, 'bb_appfx_commontypes' as common)
select
S.NAME,
S.DESCRIPTION,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
RT.NAME as RECORDTYPENAME,
S.IMPLEMENTATIONTYPENAME,
case S.IMPLEMENTATIONTYPE
when 0 then 'SP (' + S.PROCEDURENAME + ')'
when 1 then 'CLR (' + S.ASSEMBLYNAME + ', ' + S.CLASSNAME + ')'
end as IMPLEMENTATIONDESCRIPTION,
S.SECURITYUIFOLDER,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(S.SEARCHLISTSPEC.query('tns:SearchListSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(S.SEARCHLISTSPEC.query('tns:SearchListSpec/common:InstalledProductList')) as INSTALLED,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/@ImageKey)[1]', 'nvarchar(max)') as IMAGEKEY,
S.QUICKFINDFIELD,
S.SEARCHLISTSPEC as SEARCHLISTSPECXML,
S.SEARCHLISTSPEC.exist('tns:SearchListSpec/tns:OptionalFields/tns:FilterFields') as HASOPTIONALFILTERFIELDS,
S.SEARCHLISTSPEC.exist('tns:SearchListSpec/tns:OptionalFields/tns:OutputFields') as HASOPTIONALOUTPUTFIELDS,
coalesce(S.SEARCHLISTSPEC.value('(tns:SearchListSpec/tns:Output/@PrimaryKeyField)[1]', 'nvarchar(100)'), 'ID') as PRIMARYKEYFIELD,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/tns:Output/@TranslationField)[1]', 'nvarchar(100)') as TRANSLATIONFIELD,
case S.SEARCHLISTSPEC.exist('tns:SearchListSpec/tns:ResourceFile')
when 1 then
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
S.PROCEDURENAME,
case S.SEARCHLISTSPEC.exist('tns:SearchListSpec/tns:SPSearchList/common:CreateProcedureSQL')
when 1 then S.SEARCHLISTSPEC.value('(tns:SearchListSpec/tns:SPSearchList/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
else null
end as CREATEPROCEDURESQL,
S.ASSEMBLYNAME,
S.CLASSNAME,
case S.SEARCHLISTSPEC.exist('tns:SearchListSpec/common:FormMetaData/common:FormUIComponent')
when 1 then
case S.SEARCHLISTSPEC.value('(tns:SearchListSpec/common:FormMetaData/common:FormUIComponent/@FormUIComponentType)[1]', 'nvarchar(15)')
when 'CustomComponent' then 'Custom component'
else 'Default UI'
end
else
'None'
end as FORMUICOMPONENTTYPE,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@AssemblyName)[1]', 'nvarchar(max)') as FORMCOMPONENTASSEMBLYNAME,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@ClassName)[1]', 'nvarchar(max)') as FORMCOMPONENTCLASSNAME,
case S.SEARCHLISTSPEC.exist('tns:SearchListSpec/common:FormMetaData/common:WebUIComponent')
when 1 then
case S.SEARCHLISTSPEC.exist('tns:SearchListSpec/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource')
when 1 then 'External resource'
else 'Default UI'
end
else
'None'
end as WEBUICOMPONENTTYPE,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/common:FormMetaData/common:WebUIComponent/common:UIModel/@AssemblyName)[1]', 'nvarchar(max)') as WEBUIMODELASSEMBLYNAME,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/common:FormMetaData/common:WebUIComponent/common:UIModel/@ClassName)[1]', 'nvarchar(max)') as WEBUIMODELCLASSNAME,
S.SEARCHLISTSPEC.value('(tns:SearchListSpec/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource/@Url)[1]', 'nvarchar(max)') as WEBUIEXTERNALRESOURCEURL,
S.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
S.DATEADDED,
S.DATECHANGED,
S.TSLONG,
S.SPECUINAME,
S.UINAME
/*#EXTENSION*/
from dbo.SEARCHLISTCATALOG as S
left outer join dbo.RECORDTYPE as RT on S.RECORDTYPEID = RT.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = S.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = S.CHANGEDBYID