Skip to main content

Goal

The goal of this solution is to automatically add trace fields for setting a record to inactive for all tables that has a active field so the user can see who set the record to inactive. Since the records can have have additional changes to the records after setting them to inactive there are separate trace fields for this action. This looks like this in the GUI:

Solution

The solution consists of code for Dynamic Model (for adding the fields, for adding a prefilter to show only active records and add a task to deactivate active records and a task to activate inactive records. 

 

Dynamic model add_active_inactive_fields 

This code adds inactive trace columns to every table.

The trace fields are:

  • inactive_by

  • inactive_on

  • inactive_reason

These fields are added to every table that has a active column and doesn't have these inactive trace columns. The columns will be postioned just before the trace colums in a seperate form group.

Dynamic model prefilter_active

This code adds a prefilter active to every table that has a active column. The prefilter is default on unless a tab tag default_active_prefilter_off is added to the tab. 

Dynamic model de_activate_task

This dynamic model code adds an activate task and a deactivate task to every table that has a active column and a tab tag deactivate_master_table. The column active is set to read only. This is done so the task can be authorised separately. The parameters of the task are primary key field(s) of the table and the inactivity reason. The tasks are automatically translated and added to the authorisation structure. 

Control procedure context_activate_deactivate

This control procedure adds the template to shows or hides the (de)activate task based on the activity status of the record for tables with an active column. Use context is set to true for these tables

Template context_activate_deactivate

This templates hides the deactivate task for inactive records. The activate task is hidden for active records. 

Control procedure task_activate

This control procedure adds the template to tables with an active column. 

Template task_activate

This templates sets the inactive record to active. 

Control procedure task_deactivate

This control procedure adds the template to tables with an active column. 

Template task_deactivate

This templates sets the active record to deactive. 

 

 

 

Top, will start using it. Is the included zip compatible with version 2021.1?

I also have the impression that the code that is preceded bij "-- Control proc:” actually should be added to the dynamic model, also as there is no way of including code in a control procedure without also adding a template. In the cases where this is the case another label ("-- Control proc template:”) is places within the zipped script.

Also beware that the name of the control procedure and the name of the template must be identical as they are part of WHERE-clauses.

The active field that triggers this must be of a domain in which the values are:

  • 1 for active;
  • 0 for inactive.

How do I have to deal with the following situation (see code at the bottom)?

If I understand it correctly the dynamic model part of task_activate inserts program_objects and parameters based on the exact match of control_proc_id and template_id to 'task_activate'.

On the other hand is it necessary that the template must be adapted in such a way that the [tab_id] and the [col_id] have to be manually changed.

Something similar does apply to ‘context_activate_deactivate’ and 'task_deactivate'.

What is the best way to achieve the result for multiple tables? Wouldn't it be better to have the table name as part of the template_id and included in the code of the dynamic model?

What is the role of  col_id] = @ col_id] and what values have to be included? Or are block-squares automatically replaced by the SF (which would be a nice feature, but requires information about how it works)?

There are two messages that are called on (de)activation in case all selected records are already (de)activated. Please add these messages to one of the dynamic model code parts.

FROM tab_tag tt
JOIN control_proc_template cpt ON cpt.project_id = tt.project_id
   AND cpt.project_vrs_id = tt.project_vrs_id
   AND cpt.control_proc_id = 'task_activate'
   AND cpt.template_id = 'task_activate'
WHERE tt.project_id = @project_id
   AND tt.project_vrs_id = @project_vrs_id
   AND tt.tag_id = 'deactivate_master_table'
   AND EXISTS (
      SELECT 1
      FROM prog_object po
      WHERE po.project_id = @project_id
         AND po.project_vrs_id = @project_vrs_id
         AND po.prog_object_type_id = 'TASK'
         AND po.task_id = 'activate_' + tt.tab_id
      )

 

-- Control proc template: task_activate
IF EXISTS (
      SELECT 1
      FROM tab_id]
      WHERE active = 0
         AND >col_id] = @ col_id]
      )
BEGIN
   UPDATE =tab_id]
   SET active = 1
      ,inactive_by = NULL
      ,inactive_on = NULL
      ,inactive_reason = NULL
      ,modified_by = dbo.tsf_user()
      ,modified_on = getdate()
   WHERE active = 0
      AND )col_id] = @ col_id]
END


IF @@rowcount = 0
BEGIN
   EXEC dbo.tsf_send_message 'data_already_active'
      ,NULL
      ,0
END

Top, will start using it. Is the included zip compatible with version 2021.1?

I also have the impression that the code that is preceded bij "-- Control proc:” actually should be added to the dynamic model, also as there is no way of including code in a control procedure without also adding a template. In the cases where this is the case another label ("-- Control proc template:”) is places within the zipped script.

Also beware that the name of the control procedure and the name of the template must be identical as they are part of WHERE-clauses.

The active field that triggers this must be of a domain in which the values are:

  • 1 for active;
  • 0 for inactive.

Hi Roy,

This script is not fully compatible with 2021.1 because the script did not name the columns in which data should be inserted. The Insert statements for task_parmtr will fail and should be manually altered in order to make it work.

The Control procedures should be made as SQL procedures. The code between Control proc and Control proc template should be added to the Code of the Control procedure. The Template should be the template of that Control procedure.

Roy van Uffelen wrote:
How do I have to deal with the following situation (see code at the bottom)?

I suggest manually adding these Messages or writing the Code for it into the Dynamic model procedure.

 

On the other hand is it necessary that the template must be adapted in such a way that the etab_id] and the ecol_id] have to be manually changed.

These eparameters] will be changed upon generating the code for the Control procedure.

 

What is the best way to achieve the result for multiple tables? Wouldn't it be better to have the table name as part of the template_id and included in the code of the dynamic model?

Like stated above, using nparameters] it can be used for multiple tables. The power of using Control procedures of type SQL is that it can be assigned to multiple tables/tasks/objects without manually having to assign it to all tables. 

 

What is the role of lcol_id] = @ dcol_id] and what values have to be included? Or are block-squares automatically replaced by the SF (which would be a nice feature, but requires information about how it works)?

The SF will do it's magic when using parameters:

Template with eParameters] - (Templates)
aParameters] will be replaced by Value - (Assigning)
Result

But this is an example with 1 table using Assigning, but this can also be achieved by using SQL code in the Control procedure. 

Using prog_object_item you can assign the template to each Program object item you would like. Then by using prog_object_item_parmtr, you can make the mparameter] be replaced by the value.

This is a very powerful piece of the Software Factory that allows these kinds of procedures to be widely used (and automated for new tables!)

Reply


Terms & ConditionsCookie settings