Solved

How to define a task that can do the same update in multiple tables

  • 15 March 2021
  • 7 replies
  • 184 views

Userlevel 3
Badge +5

I have multiple tables with an is_active column. On each grid I want to have a table task that triggers the same task. It is possible to link one task to multiple tables.

But what I want, is to define a single template that can work on each of these tables. And maybe can use a placeholder for the actual table name.

icon

Best answer by Mark Jongeling 29 March 2021, 10:46

View original

7 replies

Userlevel 7
Badge +23

Hi Roy,

This is certainly possible by using [parameters] inside your Control procedure template. For example:

Usage of parameters inside a Template

Then in the Assigning screen, assign it to the table, or tables, you would like. Automatically it should create an entry for you under Parameters. You can see the amount of parameters by looking at the Badge present on the tab.

Assigning and replacing parameters inside the template with a static value

Note there is a column called 'No line when empty’. This is handy to use when you have a template that is used for multiple tables (or other objects) that do not always share the same columns/parameters or others. When 'No line when empty’ is checked and the Parameter value is empty, the complete line will be erased upon generating for that Program object ↔️ template assignment. 

After generating, the code will look like this:

Product table - [table] renamed to Product​​​
Relation table - [table] renamed to Relation

Above you see that the parameters have been replaced by the Parameter value set in the previous screen.

This can also be done by using a Control procedure with Assignment = SQL. With this you can make use of Table tags for example to Tag the objects that will be affected by the Control procedure. This is a bit advanced stuff but very powerful and so I highly recommend looking into it.

But for convenience, let's say we want to assign this example template to all tables and replace the parameter with the table name.

Control procedure entry

First of we need to define all the Program object items followed by filling the Program object item parameters table. The following code is needed. Enter this code on the Code tab on Control procedure.

Again, using Tags makes this much more maintainable as there will be most likely excluded objects:

--Enable use_defaults for all tables
update t
set use_defaults = 1
from tab t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id

--For all tables
insert into prog_object_item
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
order_no,
control_proc_id,
template_id
)
select @project_id,
@project_vrs_id,
'default_'+t.tab_id, --The name of the Program object
'default_each_table_sql_'+t.tab_id, --Name of the Assignment (see screenshot below)*
100,
@control_proc_id,
'default_each_table_sql' --Template name to use
from tab t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id

--Assign the parameters and set the parameter values
insert into prog_object_item_parmtr
(
project_id,
project_vrs_id,
prog_object_id,
prog_object_item_id,
parmtr_id,
parmtr_value,
order_no,
no_line_when_empty
)
select @project_id,
@project_vrs_id,
'default_'+t.tab_id, --The name of the Program object
'default_each_table_sql_'+t.tab_id, --Name of the Assignment (see screenshot below)*
'table', --Don't include the [ and ]
t.tab_id, --Replacement value
10, --order no, keep between 1 and 100000
0 --Set to 1 if you want the line to be
--erased when no replacement value
--was given
from tab t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id

*Program object item will look like:

Program object item

And here's the result:

Result

Hope this helps you immensely :wink:

Userlevel 6
Badge +16

Please note that when using a template parameter you can no longer use results to edit your code. Since this would overwrite the parameter name in the template to a fixed value. As a fail safe. the SF automatically set's the import settings on result to: do not save.

So when using template parameters you will have to modify the code on the templates screen. 

Userlevel 3
Badge +5

The above description is for a default_procedure. Unfortunately a task is slightly different as the program_object seems not to be related to the table. So no separate program_objects means that I cannot put parameters at individual program_objects.

So how to achieve this for one task, connected to different tables, that update the same field in different tables?

Is there a way I can achieve the same thing by setting a default in a parameter of the table task?

Userlevel 7
Badge +23

Correct, with Tasks it's a little different. While with Defaults the Program objects are there for every table (and task and reports), for Tasks there is only 1; the Task itself. What you can do is create multiple entries for Program object item. For the Task that would mean its code will be long as it all depends how many tables it needs to cover.

First of you need a generic template that would work for all tables. Then write the neccesary code to replace all these parameters for the correct values. In this case it is needed to create an entry in prog_object_item for each table, so take the Tables (either from tab or tab_tag if it has one) and create items. This will mean the result will feature 1 Task that consist out of 1 Template that is duplicated for each table.

For each duplication we want to replace the parameters. If you have 1 parameters, one insert statement into prog_object_item_parmtr will do, but with multiple parameters, you'll have to union all the results, 1 select for parameter 1 and 1 select for parameter 2. This is the most easy way to do it. There's an alternative by cross joining a (Values) but it may not fit the solution anymore when using more than 2 parameters. Sometimes they also require more/different tables to be joined. so I suggest making use of Union all.

After that, if the code is correct, generate the code group and thereafter the definition of the Task and see the result. If everything goes well, there's one Program object and X-amount of Program object items that together make the complete procedure. To make sure the execution of the Task executes in the correct table, be sure to have a Task parameter with the table name for example. You can add to all tables a hidden expression column with the table name as value. But if there's any other way you can distinguish that, that is also possible of course. 

For example, the template could look like this:

if @table_name = '[table]'
begin
insert into [table] (time_stamp)
select getdate()
end

Then the result could look like this:

create procedure community_example (
@table_name varchar(200)
)
as
begin
SET NOCOUNT ON;

if @table_name = 'Product'
begin
insert into [table] (time_stamp)
select getdate()
end

if @table_name = 'Relation'
begin
insert into [table] (time_stamp)
select getdate()
end

if @table_name = 'Country'
begin
insert into [table] (time_stamp)
select getdate()
end

if @table_name = 'Employee'
begin
insert into [table] (time_stamp)
select getdate()
end


end

You can also add a [col] parameter and add columns to the insert, by using string_agg() for example. This way you can get all col_id's from the tab and select it comma separated. 

Userlevel 6
Badge +16

Alternatively you could also use dynamic sql to create a smaller stored procedure which has the same effect. If you have a large amount of tables you want to use this for then this might be a bit cleaner. For a small amount this might not be the best solution. So it's up to you what to use.: 
 

declare @tablename varchar(10)
declare @sql varchar(100)
;
set @tablename = 'persons'
;
set @sql = 'insert into ' + @tablename + ' (time_stamp)
                   select getdate()'
;
exec (@sql)

 

Userlevel 3
Badge +5

Difficult to award a single answer as Best answer. In this case it is the combination of answers from Mark & Erwinthat make the whole.

Userlevel 7
Badge +23

There are two possible ways:

Correct, with Tasks it's a little different. While with Defaults the Program objects are there for every table (and task and reports), for Tasks there is only 1; the Task itself. What you can do is create multiple entries for Program object item. For the Task that would mean its code will be long as it all depends how many tables it needs to cover.

First of you need a generic template that would work for all tables. Then write the neccesary code to replace all these parameters for the correct values. In this case it is needed to create an entry in prog_object_item for each table, so take the Tables (either from tab or tab_tag if it has one) and create items. This will mean the result will feature 1 Task that consist out of 1 Template that is duplicated for each table.

For each duplication we want to replace the parameters. If you have 1 parameters, one insert statement into prog_object_item_parmtr will do, but with multiple parameters, you'll have to union all the results, 1 select for parameter 1 and 1 select for parameter 2. This is the most easy way to do it. There's an alternative by cross joining a (Values) but it may not fit the solution anymore when using more than 2 parameters. Sometimes they also require more/different tables to be joined. so I suggest making use of Union all.

After that, if the code is correct, generate the code group and thereafter the definition of the Task and see the result. If everything goes well, there's one Program object and X-amount of Program object items that together make the complete procedure. To make sure the execution of the Task executes in the correct table, be sure to have a Task parameter with the table name for example. You can add to all tables a hidden expression column with the table name as value. But if there's any other way you can distinguish that, that is also possible of course. 

For example, the template could look like this:

if @table_name = '[table]'
begin
insert into [table] (time_stamp)
select getdate()
end

Then the result could look like this:

create procedure community_example (
@table_name varchar(200)
)
as
begin
SET NOCOUNT ON;

if @table_name = 'Product'
begin
insert into [table] (time_stamp)
select getdate()
end

if @table_name = 'Relation'
begin
insert into [table] (time_stamp)
select getdate()
end

if @table_name = 'Country'
begin
insert into [table] (time_stamp)
select getdate()
end

if @table_name = 'Employee'
begin
insert into [table] (time_stamp)
select getdate()
end


end

You can also add a [col] parameter and add columns to the insert, by using string_agg() for example. This way you can get all col_id's from the tab and select it comma separated. 

Alternatively you could also use dynamic sql to create a smaller stored procedure which has the same effect. If you have a large amount of tables you want to use this for then this might be a bit cleaner. For a small amount this might not be the best solution. So it's up to you what to use.: 
 

declare @tablename varchar(10)
declare @sql varchar(100)
;
set @tablename = 'persons'
;
set @sql = 'insert into ' + @tablename + ' (time_stamp)
                   select getdate()'
;
exec (@sql)

 

 

Reply