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 OpenOn 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.


Love to see this feature available. I notice that it currently takes an unnecessarily long time to create the prefilters and that avoidable mistakes are being made in the translations.


Love to see this feature available. I notice that it currently takes an unnecessarily long time to create the prefilters and that avoidable mistakes are being made in the translations.

Would an Enrichment suffice? That way we can create a list of model updates, such as creating prefilters and translations, and allow the developer to configure it a little beforehand.

As Input parameter, I think only the Domain ID would suffice to achieve what this idea needs. 

As bonus, we could make a button to run this enrichment seamlessly from the Prefilter screen 🚀


Love to see this feature available. I notice that it currently takes an unnecessarily long time to create the prefilters and that avoidable mistakes are being made in the translations.

Would an Enrichment suffice? That way we can create a list of model updates, such as creating prefilters and translations, and allow the developer to configure it a little beforehand.

As Input parameter, I think only the Domain ID would suffice to achieve what this idea needs. 

As bonus, we could make a button to run this enrichment seamlessly from the Prefilter screen 🚀

This seems like an excellent solution to me.

And the button to be able to do this from the prefilter screen would be a great addition for the developer!