Hide empty fields when not editing

  • 8 June 2021
  • 0 replies
  • 46 views

Userlevel 3
Badge +10

Goal

Some table might contain allot of fields. A good way to show the user the fields that contain important information is to hide all empty fields. By saving all the space that the empty fields normally would use, more (important) fields that contain data will be instantly visible to the user.

Solution

You could manually set up a layout procedure to hide all these empty fields. But since we are using the Software Factory, why bother with setting up manually assigned templates. Instead it would be great to have a dynamic control procedure that automatically sets up such a layout procedure. That is exactly what this topic contains.

All you have to do is create the tag HIDE_EMPTY_FIELD and assign this tag to the specific table. The layout procedure will automatically hide all fields when they are empty or contain a empty string. Fields that are already set to hidden on column level are excluded.

-----------------------------------------------------------
-- link program object items
-----------------------------------------------------------
insert into prog_object_item
(
project_id
,project_vrs_id
,prog_object_id
,prog_object_item_id
,order_no
,control_proc_id
,template_id
)
select
pg.project_id -- project_id
,pg.project_vrs_id -- project_vrs_id
,pg.prog_object_id -- prog_object_id
,pg.prog_object_id + '_' + cpt.template_id -- prog_object_item_id
,cpt.order_no -- order_no
,cpt.control_proc_id -- control_proc_id
,cpt.template_id -- template_id
from tab t
join control_proc cp
on cp.project_id = t.project_id
and cp.project_vrs_id = t.project_vrs_id
join control_proc_template cpt
on cpt.project_id = cp.project_id
and cpt.project_vrs_id = cp.project_vrs_id
and cpt.control_proc_id = cp.control_proc_id
join prog_object pg
on pg.project_id = t.project_id
and pg.project_vrs_id = t.project_vrs_id
and pg.tab_id = t.tab_id
where cp.project_id = @project_id
and cp.project_vrs_id = @project_vrs_id
and cp.control_proc_id = @control_proc_id
and pg.prog_object_id = 'LAYOUT_' + t.tab_id
and exists(
select
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'HIDE_EMPTY_FIELD'
)
;

-----------------------------------------------------------
-- add program object item parameters
-----------------------------------------------------------
insert into prog_object_item_parmtr
(
project_id
,project_vrs_id
,prog_object_id
,prog_object_item_id
,parmtr_id
,parmtr_value
,order_no
,no_line_when_empty
)
-----------------------------------------------------------
-- column
-----------------------------------------------------------
select
pg.project_id -- project_id
,pg.project_vrs_id -- project_vrs_id
,pg.prog_object_id -- prog_object_id
,pg.prog_object_id + '_' + cpt.template_id -- prog_object_item_id
,'column' -- parmtr_id
,c.col_id -- parmtr_value
,c.order_no -- order_no
,0 -- no_line_when_empty
from col c
join control_proc cp
on cp.project_id = c.project_id
and cp.project_vrs_id = c.project_vrs_id
join control_proc_template cpt
on cpt.project_id = cp.project_id
and cpt.project_vrs_id = cp.project_vrs_id
and cpt.control_proc_id = cp.control_proc_id
join prog_object pg
on pg.project_id = c.project_id
and pg.project_vrs_id = c.project_vrs_id
and pg.tab_id = c.tab_id
where cp.project_id = @project_id
and cp.project_vrs_id = @project_vrs_id
and cp.control_proc_id = @control_proc_id
and pg.prog_object_id = 'LAYOUT_' + c.tab_id
and c.type_of_col <> 3 -- not hidden
and exists(
select
1
from tab_tag tt
where tt.project_id = c.project_id
and tt.project_vrs_id = c.project_vrs_id
and tt.tab_id = c.tab_id
and tt.tag_id = 'HIDE_EMPTY_FIELD'
)

union all

-----------------------------------------------------------
-- comma
-----------------------------------------------------------
select
pg.project_id -- project_id
,pg.project_vrs_id -- project_vrs_id
,pg.prog_object_id -- prog_object_id
,pg.prog_object_id + '_' + cpt.template_id -- prog_object_item_id
,'comma' -- parmtr_id
,case -- parmtr_value
when c.abs_order_no = fc.min_abs_order_no
then null -- hide comma
else ','
end -- parmtr_value
,c.order_no -- order_no
,0 -- no_line_when_empty
from col c
join control_proc cp
on cp.project_id = c.project_id
and cp.project_vrs_id = c.project_vrs_id
join control_proc_template cpt
on cpt.project_id = cp.project_id
and cpt.project_vrs_id = cp.project_vrs_id
and cpt.control_proc_id = cp.control_proc_id
join prog_object pg
on pg.project_id = c.project_id
and pg.project_vrs_id = c.project_vrs_id
and pg.tab_id = c.tab_id
-------------------------------------
-- determine first visible column
-------------------------------------
cross apply(
select
MIN(fc.abs_order_no) as min_abs_order_no
from col fc
where fc.project_id = c.project_id
and fc.project_vrs_id = c.project_vrs_id
and fc.tab_id = c.tab_id
and fc.type_of_col <> 3 -- not hidden
) fc
where cp.project_id = @project_id
and cp.project_vrs_id = @project_vrs_id
and cp.control_proc_id = @control_proc_id
and pg.prog_object_id = 'LAYOUT_' + c.tab_id
and c.type_of_col <> 3 -- not hidden
and exists(
select
1
from tab_tag tt
where tt.project_id = c.project_id
and tt.project_vrs_id = c.project_vrs_id
and tt.tab_id = c.tab_id
and tt.tag_id = 'HIDE_EMPTY_FIELD'
)
;

-----------------------------------------------------------
-- enable layout procedure on table level
-----------------------------------------------------------
update t
set t.use_layouts = 1
from tab t
join prog_object po
on po.project_id = t.project_id
and po.project_vrs_id = t.project_vrs_id
and po.tab_id = t.tab_id
join prog_object_item poi
on poi.project_id = po.project_id
and poi.project_vrs_id = po.project_vrs_id
and poi.prog_object_id = po.prog_object_id
where poi.project_id = @project_id
and poi.project_vrs_id = @project_vrs_id
and poi.control_proc_id = @control_proc_id
and t.use_defaults = 0 -- default staat uit
and exists(
select
1
from tab_tag tt
where tt.project_id = t.project_id
and tt.project_vrs_id = t.project_vrs_id
and tt.tab_id = t.tab_id
and tt.tag_id = 'HIDE_EMPTY_FIELD'
)
;

 


0 replies

Be the first to reply!

Reply