Active prefilter

  • 2 April 2021
  • 8 replies
  • 125 views

Userlevel 3
Badge +10

Goal

Applications often use a checkbox to determine whether a record is active or inactive. In most tables you would like to display the records which are marked as active and hide the records marked as inactive. This means you will have to set up a prefilter to hide the inactive records. Image how great it would be if you could generate those prefilters automatically. This topic will teach you how to configure the automatic generation of prefilters.

Solution

This topic will provide you with a dynamic model procedure which generated prefilters on tables that contain a column named inactive. It also provides this prefilter with an image and sets the prefilter to enabled and visible on the main, detail and popup screens. The icon and the column which triggers this script, can be ajusted to match your own application.

Create a dynamic procedure and submit the following control procedure code:

-------------------------------------------------------------
-- create prefilter active where not exists
-------------------------------------------------------------
insert tab_prefilter
(
project_id
,project_vrs_id
,tab_id
,tab_prefilter_id
,prefilter_type
,query
,tab_prefilter_grp_id
,main_prefilter_state
,detail_prefilter_state
,look_up_prefilter_state
,shift_code
,ascii_code
,order_no
,generated
,icon
)
select
c.project_id -- project_id
,c.project_vrs_id -- project_vrs_id
,c.tab_id -- tab_id
,'active' -- tab_prefilter_id
,1 -- prefilter_type
,null -- query
,null -- tab_prefilter_grp_id
,2 -- main_prefilter_state
,2 -- detail_prefilter_state
,2 -- look_up_prefilter_state
,null -- shift_code
,null -- ascii_code
,999 -- order_no
,0 -- generated
, pv.project_folder_spec -- icon
+ 'Icons\SVG\Afwijkend\active.svg' -- icon
from col c
join project_vrs pv
on pv.project_id = c.project_id
and pv.project_vrs_id = c.project_vrs_id
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and c.col_id = 'active'
and not exists(
select
1
from tab_prefilter tp
where tp.project_id = c.project_id
and tp.project_vrs_id = c.project_vrs_id
and tp.tab_id = c.tab_id
and tp.tab_prefilter_id = 'active'
)
and not 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 = 'DO_NOT_USE_ACTIVE_PREFILTER'
)
;

-------------------------------------------------------------
-- add filter condition
-------------------------------------------------------------
insert tab_prefilter_col
(
project_id
,project_vrs_id
,tab_id
,tab_prefilter_id
,col_id
,filter_condition
,filter_value
,filter_value_until
)
select
c.project_id -- project_id
,c.project_vrs_id -- project_vrs_id
,c.tab_id -- tab_id
,'active' -- tab_prefilter_id
,c.col_id -- col_id
,0 -- filter_condition
,'1' -- filter_value
,null -- filter_value_until
from col c
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and c.col_id = 'active'
and not exists(
select
1
from tab_prefilter_col tpc
where tpc.project_id = c.project_id
and tpc.project_vrs_id = c.project_vrs_id
and tpc.tab_id = c.tab_id
and tpc.tab_prefilter_id = 'active'
)
and not 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 = 'DO_NOT_USE_ACTIVE_PREFILTER'
)
;

Note that you can mark tables as exception, therefor not generating this prefilter. This is done by tagging these tables with the tag DO_NOT_USE_ACTIVE_PREFILTER

Also note that the prefilter will by created as a non generated prefilter. This means when the Software Factory will drop all generated objects, these prefilters will not be dropped. You might wonder why. Otherwise changes made in table variants, in regards to these prefilters, will be removed upon generating your project (dropping generated objects). This also would cause a problem with setting up the Access Rights for your roles. These role rights otherwise would be dropped upon generating your project.


8 replies

Userlevel 6
Badge +18

Hey Arjan, could you add for which Software Factory version this code is tested/implemented?

Userlevel 3
Badge +10

@Mark Jongeling this solutionhas been used in version 2020.1 till 2021.1. Note that from version 2020.2 icons will be uploaded in the database. So before each release, do not forget to run the task to upload images and files into the Software Factory

@Arjan Sollie we use something similar and while looking how to get it working again (since the icons are stored in the db it doesn't work anymore) I stumbled on this example of yours.

While reading thru your solution I noticed you refereced a column in called prefilter_type in your insert tab_prefilter but to my knowlage that column doesn't exist?! Same applies to the columns  order_no, generated and icon.
 

Additionally the table tab_tag doesn't seem to exist in my TW db.

Could you clarify this? Maybe I'm misunderstanding?

Userlevel 3
Badge +10

@Alban_T ,

These columns should exist in your Software Factory Database. If they are missing, I would advise contacting your Thinkwise Contact.

It is important to determine why these columns are missing. Often this indicates that someone has executed a partial Software Suite upgrade.

Userlevel 6
Badge +18

Hi Alban,

Which version of the Software Factory are you running?

Here is a screenshot of the tab_prefilter table. The column prefilter_type should exist:

Table structure of tab_prefilter

The tab_tag table should also exists:

Table structure of tab_tag

Like Arjan suggest, contact your Thinkwise contact regarding this. 

@Arjan Sollie  @Mark Jongeling  Thanks guys!!!
I'll try to find which of my colleagues did the upgrade to TW2021.1 and see what he can tell me.
@Onno Spoor do you know anything about this? I seem to recall you did the upgrade?

Userlevel 1
Badge +4

@Alban_T @Mark Jongeling @Arjan Sollie 

I can't recall if this was done. We've done the upgrade a couple of month ago.
But it should have been either part of the upgrade itself or as a upgrade script with a validation on all exsisting project reminding you to perform the script.

@Mark Jongeling@Arjan Sollie  FYI it was my fault :( I was looking in the IAM db instead of the SF db… I found the table and fields and got it working again.

Reply