Generate prefilters based on domain elements

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
 )

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.  

• What happens to your ideas? (updated as per november 2024) F.A.Q.

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.

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!

You can now generate prefilters based on domain elements. The task is available in the prefilter screen and allows you to select a column that has a domain with elements. Optionally you can add them to a prefilter group. Once executed, the prefilters are generated and updated based on the element configuration including sorting, icon, and translation:

As this is a enrichment, you can also go into the Model enrichments screen and perform the generation over your entire model for a specific domain.