V_QUERY_SMARTFIELD
Provides support for querying on smart field metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(max) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| RECORDTYPENAME | nvarchar(50) | yes | Record type |
| VALUERECORDTYPENAME | nvarchar(50) | yes | Value record type |
| PROCEDURENAME | nvarchar(128) | Procedure name | |
| DATATYPE | nvarchar(7) | yes | Data type |
| SMARTFIELDFOLDER | nvarchar(500) | Smart field folder | |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| SMARTFIELDSPECXML | xml | Smart field spec xml | |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| CREATEPROCEDURESQL | nvarchar(max) | yes | Create procedure sql |
| 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 |
| HASSITEFILTER | int | yes | |
| BYPASSPROCESSORUPDATE | int | yes | |
| CURRENCYFIELDID | nvarchar(max) | yes | |
| RECORDTYPEID | uniqueidentifier | yes | |
| VALUERECORDTYPEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/6/2016 5:43:34 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.159.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SMARTFIELD AS
with xmlnamespaces ('bb_appfx_smartfield' as tns, 'bb_appfx_commontypes' as common)
select
S.DISPLAYNAME as NAME,
S.DESCRIPTION,
S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
RT.NAME as RECORDTYPENAME,
VRT.NAME as VALUERECORDTYPENAME,
S.PROCEDURENAME,
S.DATATYPE,
S.SMARTFIELDFOLDER,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(S.SMARTFIELDSPECXML.query('tns:SmartFieldSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(S.SMARTFIELDSPECXML.query('tns:SmartFieldSpec/common:InstalledProductList')) as INSTALLED,
S.SMARTFIELDSPECXML,
case S.SMARTFIELDSPECXML.exist('tns:SmartFieldSpec/tns:ResourceFile')
when 1 then
S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/tns:CreateProcedureSQL)[1]', 'nvarchar(max)') as CREATEPROCEDURESQL,
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,
coalesce(S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/@HasSiteFilter)[1]', 'bit'), 0) as HASSITEFILTER,
coalesce(S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/@BypassProcessorUpdate)[1]', 'bit'), 0) as BYPASSPROCESSORUPDATE,
S.SMARTFIELDSPECXML.value('(tns:SmartFieldSpec/@CurrencyFieldID)[1]', 'nvarchar(max)') as CURRENCYFIELDID,
RT.ID as RECORDTYPEID,
VRT.ID as VALUERECORDTYPEID
/*#EXTENSION*/
from dbo.SMARTFIELDCATALOG as S
left outer join dbo.RECORDTYPE as RT on S.RECORDTYPEID = RT.ID
left outer join dbo.RECORDTYPE as VRT on S.VALUERECORDTYPEID = VRT.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