View: dbo.Custom_Field_Base_Type_Calculation_View
View definition
-- A view used in adding to the Filter_Base_Type table.
create view dbo.Custom_Field_Base_Type_Calculation_View
as
select
CF.custom_field_KEY
, 100000000 * CF.custom_field_type_KEY + CF.custom_field_KEY as filter_base_type_KEY
, O.filter_type_KEY
, FLT.filter_line_type_KEY
, description + ' (custom)' as description
, case
when CF.custom_field_type_KEY in (5, 6)
then '[KEY(Custom Field Row("' + CF.description
+ '", ' + convert(varchar(20), CF.custom_field_KEY)
+ ', ' + replace(CF.owner_table_name, '_', ' ')
+ '))]'
else '[Custom Field ' + FLT.formula_name + '("' + CF.description
+ '", ' + convert(varchar(20), CF.custom_field_KEY)
+ ', ' + replace(CF.owner_table_name, '_', ' ')
+ ')]'
end as filter_column
from
dbo.Custom_Field as CF
inner join
(
select 'Client' as owner_table_name, 4 as filter_type_KEY
union select 'Employee' as owner_table_name, 32 as filter_type_KEY
union select 'Staff' as owner_table_name, 1 as filter_type_KEY
union select 'Vendor' as owner_table_name, 37 as filter_type_KEY
) as O
on
CF.owner_table_name = O.owner_table_name
inner join
(
select 6 as custom_field_type_KEY, 1 as filter_line_type_KEY, 'Staff' as formula_name
union select 5 as custom_field_type_KEY, 162 as filter_line_type_KEY, 'List' as formula_name
union select 1 as custom_field_type_KEY, 160 as filter_line_type_KEY, 'Text' as formula_name
union select 2 as custom_field_type_KEY, 97 as filter_line_type_KEY, 'Date' as formula_name
union select 3 as custom_field_type_KEY, 163 as filter_line_type_KEY, 'Number' as formula_name
union select 4 as custom_field_type_KEY, 161 as filter_line_type_KEY, 'Checkbox' as formula_name
) as FLT
on
CF.custom_field_type_KEY = FLT.custom_field_type_KEY