V_QUERY_SEARCHLISTFIELDS
Provides support for querying on the fields for search lists from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| SEARCHLISTID | uniqueidentifier | Search List ID | |
| FIELDID | nvarchar(max) | yes | Field ID |
| DATATYPE | nvarchar(18) | yes | Data type |
| REQUIRED | int | yes | Required |
| READONLY | int | yes | Read only |
| HIDDEN | int | yes | Hidden |
| MAXLENGTH | int | yes | Max length |
| CAPTION | nvarchar(max) | yes | Caption |
| DESCRIPTION | nvarchar(max) | yes | Description |
| MAXVALUE | int | yes | Max value |
| MINVALUE | int | yes | Min value |
| PRECISION | int | yes | Precision |
| SCALE | int | yes | Scale |
| TIMEPRECISION | int | yes | Time precision |
| DATETIMEWITHOFFSETPRECISION | int | yes | DateTimeWithOffset precision |
| INCLUDETIMEONDATE | int | yes | Include time on date |
| ALLOWMONTHDAYONFUZZYDATE | int | yes | Allow Month/Day on fuzzy date |
| DEFAULTVALUETEXT | nvarchar(max) | yes | Default value text |
| FIXEDVALUETEXT | nvarchar(max) | yes | Fixed value text |
| CAPTIONRESOURCEKEY | nvarchar(max) | yes | Caption resource key |
| CATEGORY | nvarchar(max) | yes | Category |
| CATEGORYRESOURCEKEY | nvarchar(max) | yes | Category resource key |
| DONOTAPPLYFORMAT | int | yes | Do not apply format |
| MULTILINE | int | yes | Multiline |
| APPLYPHONEFORMATTING | int | yes | Apply phone formatting |
| ALLOWZOOM | int | yes | Allow zoom |
| AVAILABLETOCLIENT | int | yes | Available to client |
| DESCRIPTORTYPE | varchar(14) | Descriptor type | |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed product list |
| INSTALLED | bit | yes | Installed |
| ISOPTIONALFIELD | int | Is optional field |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/2/2010 7:07:10 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.7.1654.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SEARCHLISTFIELDS AS
with xmlnamespaces ('bb_appfx_searchlist' as tns, 'bb_appfx_commontypes' as common)
select
S.ID as SEARCHLISTID,
search.fields.value('@FieldID', 'nvarchar(max)') as FIELDID,
coalesce(search.fields.value('@DataType', 'nvarchar(18)'), 'String') as DATATYPE,
coalesce(search.fields.value('@Required', 'bit'), 0) as REQUIRED,
coalesce(search.fields.value('@ReadOnly', 'bit'), 0) as READONLY,
coalesce(search.fields.value('@Hidden', 'bit'), 0) as HIDDEN,
coalesce(search.fields.value('@MaxLength', 'integer'), 0) as MAXLENGTH,
search.fields.value('@Caption', 'nvarchar(max)') as CAPTION,
search.fields.value('@Description', 'nvarchar(max)') as DESCRIPTION,
coalesce(search.fields.value('@MaxValue', 'integer'), 0) as MAXVALUE,
coalesce(search.fields.value('@MinValue', 'integer'), -1) as MINVALUE,
coalesce(search.fields.value('@PRECISION', 'integer'), -1) as PRECISION,
coalesce(search.fields.value('@Scale', 'integer'), -1) as SCALE,
coalesce(search.fields.value('@TimePrecision', 'integer'), 0) as TIMEPRECISION,
coalesce(search.fields.value('@DateTimeWithOffsetPrecision', 'integer'), 3) as DATETIMEWITHOFFSETPRECISION,
coalesce(search.fields.value('@IncludeTimeOnDate', 'bit'), 0) as INCLUDETIMEONDATE,
coalesce(search.fields.value('@AllowMonthDayOnFuzzyDate', 'bit'), 0) as ALLOWMONTHDAYONFUZZYDATE,
search.fields.value('@DefaultValueText', 'nvarchar(max)') as DEFAULTVALUETEXT,
search.fields.value('@FixedValueText', 'nvarchar(max)') as FIXEDVALUETEXT,
search.fields.value('@CaptionResourceKey', 'nvarchar(max)') as CAPTIONRESOURCEKEY,
search.fields.value('@Category', 'nvarchar(max)') as CATEGORY,
search.fields.value('@CategoryResourceKey', 'nvarchar(max)') as CATEGORYRESOURCEKEY,
coalesce(search.fields.value('@DoNotApplyFormat', 'bit'), 0) as DONOTAPPLYFORMAT,
coalesce(search.fields.value('@Multiline', 'bit'), 0) as MULTILINE,
coalesce(search.fields.value('@ApplyPhoneFormatting', 'bit'), 0) as APPLYPHONEFORMATTING,
coalesce(search.fields.value('@AllowZoom', 'bit'), 0) as ALLOWZOOM,
coalesce(search.fields.value('@AvailableToClient', 'bit'), 1) as AVAILABLETOCLIENT,
case
when search.fields.exist('common:CodeTable') = 1 then 'CodeTable'
when search.fields.exist('common:ValueList') = 1 then 'ValueList'
when search.fields.exist('common:SimpleDataList') = 1 then 'SimpleDataList'
when search.fields.exist('common:Collection') = 1 then 'Collection'
when search.fields.exist('common:SearchList') = 1 then 'SearchList'
when search.fields.exist('common:Link') = 1 then 'Link'
when search.fields.exist('common:File') = 1 then 'File'
when search.fields.exist('common:CreditCard') = 1 then 'CreditCard'
when search.fields.exist('common:Html') = 1 then 'Html'
else 'None'
end as DESCRIPTORTYPE,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(search.fields.query('common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(search.fields.query('common:InstalledProductList')) as INSTALLED,
case
when exists (select 1
from S.SEARCHLISTSPEC.nodes('tns:SearchListSpec/tns:OptionalFields/tns:FilterFields/tns:FilterFieldID') as optional(fields)
where optional.fields.value('.', 'nvarchar(max)') = search.fields.value('@FieldID', 'nvarchar(max)'))
then 1
else 0
end as ISOPTIONALFIELD
/*#EXTENSION*/
from dbo.SEARCHLISTCATALOG as S
cross apply S.SEARCHLISTSPEC.nodes('tns:SearchListSpec/common:FormMetaData/common:FormFields/common:FormField') as search(fields)