Generate prefilters based on domain elements

Related products: Software Factory
A lot of times I create prefilters for a subject based on domain elements. For example, in my order table, I have a column order_status with domain order_status with elements like concept, confirmed, cancelled etc. I want a prefilter for each of those statuses.



Now I have to create a dynamic model procedure to create prefilters based on a column, which works, but is a hassle. I'd like a task within Subjects > Prefilters to generate prefilters based on a column with the corresponding domain elements. The task should generate the prefilters including the icons, translations, prefilter columns etc and could group the prefilter in a group that is named after the column.



This would save a lot of work and really improve the developer experience.
You could even take it a step further: Automatically create tasks for each status too!
I think it would be nice if the elements then have a checkbox "include in generated prefilter". So if I have elements I don't want in my filter I can leave them out
Yes you can solve this in different ways I think:



1) Make it generated prefilters, so you can't change them and nee a checkbox like you described.

2) Generate 'regular' prefilters, so it just inserts prefilters and you can alter/delete them if you like.

At pre-sales i had a change to work on solution number 2 mentioned by Jop.

The code below will add prefilters based on the provided domain elements. 

Also it will create a new group based on the name of the domain which will include all prefilters.

If domain elements have a picture associated with them it will also use this picture as icon for the prefilter.

To use this code, replace [SF_DATABASE], [PROJECT_ID], [PROJECT_VERSION] and [DOMAIN_NAME] with your own parameters.

use [SF_DATABASE]

declare @project_id varchar(max) = '[PROJECT_ID]'
declare @project_vrs_id varchar(max) = '[PROJECT_VERSION]'

declare @dom_id varchar(max) = '[DOMAIN_NAME]'

--create prefilter group
insert into tab_prefilter_grp
(
 [project_id]
,[project_vrs_id]
,[tab_id]
,[tab_prefilter_grp_id]
,[tab_prefilter_grp_desc]
,[sub_menu]
,[icon]
,[exclude]
,[mand]
,[order_no]
,[abs_order_no]
,[insert_user]
,[insert_date_time]
,[update_user]
,[update_date_time]
)
select @project_id as project_id
 , @project_vrs_id as project_vrs_id
 , t.tab_id as tab_id
 , concat(t.tab_id, '_', c.dom_id) as tab_prefilter_grp_id
 , null as tab_prefilter_grp_desc
 , 0 as sub_menu
 , null as icon
 , 1 as exclude
 , 0 as mand
 , 10 as order_no
 , 1 as abs_order_no
 , SUSER_SNAME() as insert_user
 , GETDATE() as insert_date_time
 , SUSER_SNAME() as update_user
 , getdate() as update_date_time
 from tab t
 join col c --join column to get all columns associated with tab
 on t.project_id = c.project_id
 and t.project_vrs_id = c.project_vrs_id
 and t.tab_id = c.tab_id
--only select values corresponding to the provided project_id and project_version_id
where t.project_id = @project_id
 and t.project_vrs_id = @project_vrs_id
 --only columns that are @dom_id
 and c.dom_id = @dom_id
 and not exists ( --check if prefilter group doesn't already exist in SF database
 select 1
 from tab_prefilter_grp tgrp
 where tgrp.project_id = @project_id
 and tgrp.project_vrs_id = @project_vrs_id
 and tgrp.tab_id = t.tab_id
 and tgrp.tab_prefilter_grp_id = concat(t.tab_id, '_', c.dom_id)
 )

--create prefilters
insert into 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]
,[icon]
,[order_no]
,[abs_order_no]
,[limits_offline_set]
,[generated]
,[insert_user]
,[insert_date_time]
,[update_user]
,[update_date_time]
)
select @project_id as project_id
 , @project_vrs_id as project_vrs_id
 , t.tab_id as tab_id
 , e.elemnt_id as tab_prefilter_id
 , 1 as prefilter_type
 , null as query
 , concat(t.tab_id, '_', c.dom_id) as tab_prefilter_grp_id
 , 1 as main_prefilter_state
 , 1 as detail_prefilter_state
 , 1 as look_up_prefilter_state
 , null as shift_code
 , null as ascii_code
 , e.picture_file as icon
 , 10 as order_no
 , 1 as abs_order_no
 , 0 as limits_offline_set
 , 0 as generated
 , SUSER_SNAME() as insert_user
 , getdate() as insert_date_time
 , SUSER_SNAME() as update_user
 , getdate() as update_date_time
 from tab t
 join col c --join column to get all columns associated with tab
 on t.project_id = c.project_id
 and t.project_vrs_id = c.project_vrs_id
 and t.tab_id = c.tab_id
 join elemnt e --join elemnt to get all elements associated with domain
 on c.project_id = e.project_id
 and c.project_vrs_id = e.project_vrs_id
 and c.dom_id = e.dom_id
--only select values corresponding to the provided project_id and project_version_id
where t.project_id = @project_id
 and t.project_vrs_id = @project_vrs_id
 --only columns that are @dom_id
 and c.dom_id = @dom_id
 and not exists ( --check if prefilter doesn't already exist in SF database
 select 1
 from tab_prefilter tp
 where tp.project_id = @project_id
 and tp.project_vrs_id = @project_vrs_id
 and tp.tab_id = t.tab_id
 and tp.tab_prefilter_id = e.elemnt_id
 )

--Insert prefilter columns
insert into tab_prefilter_col
(
 [project_id]
,[project_vrs_id]
,[tab_id]
,[tab_prefilter_id]
,[col_id]
,[filter_condition]
,[filter_value]
,[filter_value_until]
,[insert_user]
,[insert_date_time]
,[update_user]
,[update_date_time]
)
select @project_id as project_id
 , @project_vrs_id as project_vrs_id
 , t.tab_id as tab_id
 , e.elemnt_id as tab_prefilter_id
 , c.col_id as col_id
 , 0 as filter_condition
 , e.db_value as filter_value
 , null as filter_value_until
 , SUSER_SNAME() as insert_user
 , GETDATE() as insert_date_time
 , SUSER_SNAME() as update_user
 , GETDATE() as update_date_time
from tab t
 join col c --join column to get all columns associated with tab
 on t.project_id = c.project_id
 and t.project_vrs_id = c.project_vrs_id
 and t.tab_id = c.tab_id
 join elemnt e --join elemnt to get all elements associated with domain
 on c.project_id = e.project_id
 and c.project_vrs_id = e.project_vrs_id
 and c.dom_id = e.dom_id
--only select values corresponding to the provided project_id and project_version_id
where t.project_id = @project_id
 and t.project_vrs_id = @project_vrs_id
 --only columns that are @dom_id
 and c.dom_id = @dom_id
 and not exists ( --check if prefilter column doesn't already exist in SF database
 select 1
 from tab_prefilter_col tpc
 where tpc.project_id = @project_id
 and tpc.project_vrs_id = @project_vrs_id
 and tpc.tab_id = t.tab_id
 and tpc.tab_prefilter_id = e.elemnt_id
 and tpc.col_id = c.col_id
 )

 

Updated idea status Open → On the backlog

Does this idea need upvotes to get higher on the backlog? How are backlog items prioritized? 

Hi @BasWesselink,

That's an excellent and fair question.

In short: the more votes your idea gets, the more likely it is to be implemented. So, it never hurts to try and collect more votes throughout the Community 😉. However, there are some other criteria that we use to prioritize ideas on our backlog.

We already provide an article explaining which states an idea can go through.  

I just updated this article with the criteria we use when 1) assessing new ideas and 2) prioritizing these ideas when they are placed on our backlog.

Hopefully this provides some more transparency as to what happens with ideas submitted in this section.

Should you have any more questions or remarks concerning this, please let me know in a reaction on that article.

Terms & ConditionsCookie settings