Form field group labels

  • 16 October 2020
  • 0 replies
  • 25 views

Userlevel 2
Badge +2

Goal

 

This validation checks whether at least one group label exists on each form tab in a table (variant).

 

Solution

-- Table form field group labels.
;with min_form_no as (select MIN(c.form_order_no) as min_form_order_no
,MAX(c.form_order_no) as max_form_order_no
,c.tab_id
from col c
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
group by c.tab_id)
select c.project_id
,c.project_vrs_id
,c.tab_id
,c.col_id
,c.field_on_next_tab
,c.next_tab_label
,ISNULL(c.next_tab_label,c.tab_id) as form_label
,c.abs_order_no
,c.form_order_no as current_form_order_no
,LEAD(c.form_order_no,1,m.max_form_order_no + 1) over (partition by c.tab_id order by c.tab_id, c.form_order_no, c.col_id) as next_form_order_no
into #col
from col c
join min_form_no m
on m.tab_id = c.tab_id
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and (c.field_on_next_tab = 1
or c.form_order_no = m.min_form_order_no)

insert validation_msg
select
@project_id,
@project_vrs_id,
@validation_id,
@project_id as pk_col_1,
@project_vrs_id as pk_col_2,
c.tab_id as pk_col_3,
null as pk_col_4,
null as pk_col_5,
null as pk_col_6,
null as pk_col_7,
0,
dbo.tsf_user(),
getdate(),
dbo.tsf_user(),
getdate()
from #col tc
join col c
on c.project_id = tc.project_id
and c.project_vrs_id = tc.project_vrs_id
and c.tab_id = tc.tab_id
and c.col_id = tc.col_id
outer apply (select COUNT(1) as aantal_labels
from col c
where c.project_id = tc.project_id
and c.project_vrs_id = tc.project_vrs_id
and c.tab_id = tc.tab_id
and c.form_order_no between tc.current_form_order_no and tc.next_form_order_no - 1
and c.form_next_grp_label is not null) x
where x.aantal_labels = 0
-- There is at least one form field visible on the tab.
and exists ( select 1
from col d
where d.project_id = c.project_id
and d.project_vrs_id = c.project_vrs_id
and d.tab_id = c.tab_id
and d.form_type_of_col < 2
)
group by c.tab_id

-- Table variant form field group labels.
;with min_form_no as (select MIN(c.form_order_no) as min_form_order_no
,MAX(c.form_order_no) as max_form_order_no
,c.tab_id
,c.tab_variant_id
from tab_variant_form c
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
group by c.tab_id, c.tab_variant_id)
select c.project_id
,c.project_vrs_id
,c.tab_id
,c.tab_variant_id
,c.col_id
,c.field_on_next_tab
,c.next_tab_label
,ISNULL(c.next_tab_label,c.tab_id) as form_label
,c.form_order_no as current_form_order_no
,LEAD(c.form_order_no,1,m.max_form_order_no + 1) over (partition by c.tab_id order by c.tab_id, c.tab_variant_id, c.form_order_no, c.col_id) as next_form_order_no
into #col_var
from tab_variant_form c
join min_form_no m
on m.tab_id = c.tab_id
and m.tab_variant_id = c.tab_variant_id
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and (c.field_on_next_tab = 1
or c.form_order_no = m.min_form_order_no)

insert validation_msg
select
@project_id,
@project_vrs_id,
@validation_id,
@project_id as pk_col_1,
@project_vrs_id as pk_col_2,
c.tab_id as pk_col_3,
c.tab_variant_id as pk_col_4,
null as pk_col_5,
null as pk_col_6,
null as pk_col_7,
0,
dbo.tsf_user(),
getdate(),
dbo.tsf_user(),
getdate()
from #col_var tc
join tab_variant_form c
on c.project_id = tc.project_id
and c.project_vrs_id = tc.project_vrs_id
and c.tab_id = tc.tab_id
and c.tab_variant_id = tc.tab_variant_id
and c.col_id = tc.col_id
outer apply (select COUNT(1) as aantal_labels
from tab_variant_form c
where c.project_id = tc.project_id
and c.project_vrs_id = tc.project_vrs_id
and c.tab_id = tc.tab_id
and c.tab_variant_id = tc.tab_variant_id
and c.form_order_no between tc.current_form_order_no and tc.next_form_order_no - 1
and c.form_next_grp_label is not null) x
where x.aantal_labels = 0
and c.form_next_grp_label is null

 


0 replies

Be the first to reply!

Reply