Multirow task execution

  • 4 February 2019
  • 36 replies
  • 2284 views
Multirow task execution
Userlevel 7
Badge +11

When executing a task for a selection of rows, the user interface first executes the context procedure for every single row to check if the user is allowed to execute the task for that row, and then executes the task for each permitted row. This can be quite time consuming, especially for web clients where the progress is also reported back to the client.

To speed up multirow task execution, a new feature has been developed to execute tasks for a selection of rows.

To enable multirow execution for a task:

  1. Add an XML-typed parameter to the task
  2. Make sure Popup for each row is switched off
  3. Set the MultiselectParameterID extended property with the name of the parameter


With multirow execution enabled for a task, when the user interface executes the task, the specified XML parameter is filled with the parameter values for all rows. Even if only one row is selected.

The parameter is only filled when the task is executed. The XML is therefore not visible in the popup or available in the default and layout logic.


Example XML parameter value:

<rows>
<row>
<param1>1</param1>
<param2>a</param2>
</row>
<row>
<param1>3</param1>
<param2>b</param2>
</row>
</rows>

Be aware that both the default and the context logic are executed only once and not for every row. The parameter values for all rows are included in the XML, even if the context logic would have disabled the execution of the task for a specific row. The user interface also does not remove duplicate parameter rows when composing the XML, so you'll need to take care of that yourself.

In the task logic, use the XML parameter value instead of the regular parameters, for example:

T-SQL

select distinct
row.value('./param1[1]', 'int'),
row.value('./param2[1]', 'varchar(10)')
from @xml.nodes('/rows/row') as t2(row)


IBM i SQL

select distinct 
r.param1,
r.param2
from xmltable ('$xml/rows/row' passing v_xml as "xml"
columns
param1 integer path './param1[1]',
param2 varchar(10) path './param2[1]'
) as r;

For information on working with XML data on SQL Server, see this link.
More info on SQL XML programming for IBM i can be found here.
 


36 replies

Userlevel 4
Badge +14

I have a multi row task that starts a process flow. Now I want to show progress (tsf_send_progress), but this progress is not shown in the GUI. When I look in the debug tab I can see the messages are sent. What could be the reason?   

  @Robbert van Tongeren  thanks for sharing your dynamic assigned CP code, 2 years later it has come in handy for us. Here is an upgraded version of the code that is compatible with at least version 2023.01 

In Roberts’ words:
"
The goal of this procedure is that we don't have to retrieve the XML manually for all the XML variables and we have the same xml_table for each task procedure, also when we add a new parameter to the table task, this will be added automatically. This way you can use the table @xml_table in the template code for the task, instead of reading out the XML parameter every time you need some data.


All credits to @Robbert van Tongeren 

Code for the template:

declare @xml_table table (
[comma][task_parmtr_id] [dom_id]
)
insert into @xml_table (
[comma][task_parmtr_id]
)
select
[comma]row.value('./[task_parmtr_id][1]', '[dom_id]')
from @[xml_variable].nodes('/rows/row') as c(row)



Code for the control_procedure (Assignment "SQL”, code group "TASKS”):
Make sure you are using Strategy ‘staged’ - see this page for explanation. 

--Definition of the MultiSelectParameterID
declare @extended_property_id extended_property_id = 'MultiselectParameterID'

--Variable to save the value of the MultiSelectParameterID
declare @xml_variable property_value

--Table to save the task_id's which have a MultiSelectParameterID
declare @task table (task_id task_id)

--Retreive the tasks which have a task parameter, which have a MultiSelectParameterID connected.
insert into @task (task_id)
select t.task_id
from task t
where t.model_id = @model_id
and t.branch_id = @branch_id
and exists (select 1
from task_parmtr tp
where tp.model_id = t.model_id
and tp.branch_id = t.branch_id
and tp.task_id = t.task_id
and exists (select 1
from runtime_extended_property ep
join runtime_configuration rc
on rc.model_id = ep.model_id
and rc.branch_id = ep.branch_id
and rc.runtime_configuration_id = ep.runtime_configuration_id
and rc.default_configuration = 1
where ep.model_id = tp.model_id
and ep.branch_id = tp.branch_id
and ep.extended_property_id = @extended_property_id
and ep.property_value = tp.task_parmtr_id
)
)

--Retreive the value of the MultiSelectParameterID
select @xml_variable = ep.property_value
from runtime_extended_property ep
join runtime_configuration rc
on rc.model_id = ep.model_id
and rc.branch_id = ep.branch_id
and rc.runtime_configuration_id = ep.runtime_configuration_id
and rc.default_configuration = 1
where ep.model_id = @model_id
and ep.branch_id = @branch_id
and ep.extended_property_id = @extended_property_id
group by ep.property_value

--Connect the task to the template
insert into #prog_object_item (
prog_object_id
,prog_object_item_id
,order_no
,template_id)
select concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,5
,@control_proc_id
from @task t

--Fill the variables.
insert into #prog_object_item_parmtr (
prog_object_id
,prog_object_item_id
,parmtr_id
,parmtr_value
,order_no
,no_line_when_empty)
select concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'comma' as task_parmtr_id
,case when x.min_abs_order_no = tp.abs_order_no then ' ' else ',' end as parmtr_value
,tp.abs_order_no * 10
,0 as no_line_when_empty
from @task t
join task_parmtr tp
on tp.model_id = @model_id
and tp.branch_id = @branch_id
and tp.task_id = t.task_id
cross apply (select min(abs_order_no) as min_abs_order_no
from task_parmtr tpm
where tpm.model_id = tp.model_id
and tpm.branch_id = tp.branch_id
and tpm.task_id = tp.task_id
and tpm.task_id <> @xml_variable
) x
where tp.task_parmtr_id <> @xml_variable

union all

select concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'task_parmtr_id' as task_parmtr_id
,tp.task_parmtr_id as parmtr_value
,tp.abs_order_no * 10
,0 as no_line_when_empty
from @task t
join task_parmtr tp
on tp.model_id = @model_id
and tp.branch_id = @branch_id
and tp.task_id = t.task_id
where tp.task_parmtr_id <> @xml_variable

union all

select concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'dom_id' as task_parmtr_id
,tp.dom_id as parmtr_value
,tp.abs_order_no * 10
,0 as no_line_when_empty
from @task t
join task_parmtr tp
on tp.model_id = @model_id
and tp.branch_id = @branch_id
and tp.task_id = t.task_id
where tp.task_parmtr_id <> @xml_variable

union all

select concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'xml_variable' as task_parmtr_id
,@xml_variable as parmtr_value
,10
,0 as no_line_when_empty
from @task t

 

Userlevel 2
Badge +12

Hi Henri,

I think we need to fix this in our runtime components, so can you please submit a ticket for it?

Please note that it is generally not recommended to use time fields in a key, due to possible rounding differences when converting between different formats and/or programming language datatypes. We strongly recommend using a surrogate key (identity) in combination with a unique index on the semantic key instead.


You're right, but this is how I get things from the supplier and I want to keep the “keys” similar, to avoid duplicate processing of data… We get the information delivered as a CSV file from the German MAUT instance..

However… For the date part I got it fixed (i think) by supplying datetimeoffset in the xml query:


Thanks so far.. When the time part is fixed I certainly will add that as well. For now the date is “unique enough” ;-)

I will supply a ticket for this issue ( especially the time format is something I'd like to see in a normal format ) 

Userlevel 7
Badge +11

Hi Henri,

I think we need to fix this in our runtime components, so can you please submit a ticket for it?

Please note that it is generally not recommended to use time fields in a key, due to possible rounding differences when converting between different formats and/or programming language datatypes. We strongly recommend using a surrogate key (identity) in combination with a unique index on the semantic key instead.

Userlevel 2
Badge +12

@Jasper 

I am using multirow task execution in a task where parameters are type date and time. The XML is filled with what looks like UNIX date format and the trimeformat is even more excotic…

        <maut_date>2022-11-03T00:00:00+01:00</maut_date>
        <maut_time>PT18H2M</maut_time>

The above values in the XML  are selected in the GUI, but for starters, the date in XML will become 2022-11-02 using the following SQL statement

 

            select 
row.value('./maut_period [1]' , 'reference_number') as maut_period
, row.value('./maut_booking_number [1]' , 'reference') as maut_booking_number
, row.value('./maut_serial_number [1]' , 'sequence') as maut_serial_number
, row.value('./maut_vehicle_registration_no [1]' , 'vehicle_registration_no') as maut_vehicle_registration_no
, row.value('./maut_date [1]' , 'datetime') as maut_date
-- , row.value('./maut_time [1]' , 'time') as maut_time
, row.value('./maut_description [1]' , 'reference') as maut_description
from @input_set.nodes('/rows/row') as t1(row)

How do I get the date and time back to what is selected in the GUI when selecting multiple rows? The date and time are keys in the table that needs to be updated…

 

 

Userlevel 7
Badge +11

Of course, sorry about that!

@Jasper I believe you meant to tag @Marijn Metzlar😄

Userlevel 7
Badge +11

Hi @Marijn van de Bree,

Would this suggestion from Robbert and Mark be an option?
https://community.thinkwisesoftware.com/questions-conversations-78/multirow-task-execution-159?postid=7862#post7862

Hi @Jasper & @Anne Buit, we recently added the xml formatting in our task to make multiselect work. Before, we made use of a table which we filled and emptied via the use of a process flow. This is not needed anymore thanks to your xml tip! 

However, we did have default & layout logic which were based on the above stated table. Since this table is not needed anymore, I wanted to base the logic on the xml field regarding defaults and layouts. 

You clearly stated the following:

“The parameter is only filled when the task is executed. The XML is therefore not visible in the popup or available in the default and layout logic.”

Is there anyway to work around this? How can we track the selected rows in the default/layout using this method?

Userlevel 5
Badge +20

Mark, 

Thank you for the clear explanation of your proposed solution.

This is a workable alternative, which I will try. I expect this will work fine.

 

...Still I hope you will consider to add the xml of row values in the default procedure, because this will save a lot of development en maintenance effort. Just having 1 task, instead of 2 plus a process flow is much more complicated, think about all additional role settings.

 

Userlevel 5
Badge +20

Ok, I think your proposed solution is clear to me and I think this 2 step solution will work. Thanks for the clear explanation. At least I will give this a try.

I hope you will still consider the idea of providing the XML data in the default procedure for multirow tasks. Setting up a scenario like this with this alternative solution will cost much more time to develop and maintain, think about authorisations settings, etc… Little changes in the model can affect 2 tasks and the processflow and cause unexpected results or errors.

Userlevel 7
Badge +23

No, this is not what I want. Using a process flow is not desirable, because it will not give the desired result.


I need to now row data of all selected rows to set my task parameters. This to inform the end user better before he/she executes the task.

 

Hi Harm, What Robbert means it that the initial task execution only fills the XML parameter for you to use as input for the real task. By Starting a process flow with a dummy task that only obtains the selected row data in XML format, that way you can use that as input for the real task. The real task will still show a pop-up if there are any editable or read only fields.

Example

In the Table task you will obtain the selected row data in XML format. That will be outputted to a process variable. That process variable can then be used as input for the real task. Would that suffice or what key element are we not considering in this solution?

Userlevel 5
Badge +20

I already found out, that this is not possible.

Do you want me to post this as a new Idea?

Userlevel 5

I don't think this is possible with the current implementation.

For now the only solution I see is to use a procesflow which could give you what you want, informing the user before the execution.

@Mark Jongeling do you see any other options?

Userlevel 5
Badge +20

No, this is not what I want. Using a process flow is not desirable, because it will not give the desired result.


I need to now row data of all selected rows to set my task parameters. This to inform the end user better before he/she executes the task.

 

Userlevel 5

I am working on a advanced multi row update task.
 

The default values I wish to set in my task depend on field values of all selected rows and I also like to inform the user about the affected rows before he/she executes the task.

Setting the default value would be as easy as in the execution of the task right? Because the default value is not visible for the user anyway there.

And I think you could use a procesflow for this.
Execute multi row task → show message → yes → execute task
                                                                  → no → abort

Does this help?

Userlevel 5
Badge +20

I am working on a advanced multi row update task.
 

The default values I wish to set in my task depend on field values of all selected rows and I also like to inform the user about the affected rows before he/she executes the task.

Userlevel 5

Very unfortunate that the multiselect parameter remains empty in default procedures. Is there a specific reason for that? And can we expect this to become available in the future?

I understand it can be complex, but just having the initial values of table parameters available in the multiselect parameter of the default procedure would help a lot to make multiselect tasks even more powerful.

 

 

Hi Harm,

I would imagine that it remains empty because the default is executed per row and not per set.

What is the usecase in which you need the xml parameter in the default?

Userlevel 5
Badge +20

Very unfortunate that the multiselect parameter remains empty in default procedures. Is there a specific reason for that? And can we expect this to become available in the future?

I understand it can be complex, but just having the initial values of table parameters available in the multiselect parameter of the default procedure would help a lot to make multiselect tasks even more powerful.

 

 

Userlevel 5

I have created a dynamic control_procedure which automatically generates a @xml_table for each task that has a parameter which is the same as the property_value of MultiselectParameterID.

The goal of this procedure is that we don't have to retrieve the XML manually for all the XML variables and we have the same xml_table for each task procedure, also when we add a new parameter to the table task, this will be added automatically. This way you can use the table @xml_table in the template code for the task, instead of reading out the XML parameter every time you need some data.


Code for the template:

declare @xml_table table (
[comma][task_parmtr_id] [dom_id]
)
insert into @xml_table (
[comma][task_parmtr_id]
)
select
[comma]row.value('./[task_parmtr_id][1]', '[dom_id]')
from @[xml_variable].nodes('/rows/row') as c(row)

Code for the control_procedure (Assignment "SQL”, code group "TASKS”):

--Definition of the MultiSelectParameterID
declare @extended_property_id extended_property_id = 'MultiselectParameterID'

--Variable to save the value of the MultiSelectParameterID
declare @xml_variable property_value

--Table to save the task_id's which have a MultiSelectParameterID
declare @task table (task_id task_id)

--Retreive the tasks which have a task parameter, which have a MultiSelectParameterID connected.
insert into @task (task_id)
select t.task_id
from task t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and exists (select 1
from task_parmtr tp
where tp.project_id = t.project_id
and tp.project_vrs_id = t.project_vrs_id
and tp.task_id = t.task_id
and exists (select 1
from runtime_extended_property ep
join runtime_configuration rc
on rc.project_id = ep.project_id
and rc.project_vrs_id = ep.project_vrs_id
and rc.runtime_configuration_id = ep.runtime_configuration_id
and rc.default_configuration = 1
where ep.project_id = tp.project_id
and ep.project_vrs_id = tp.project_vrs_id
and ep.extended_property_id = @extended_property_id
and ep.property_value = tp.task_parmtr_id
)
)

--Retreive the value of the MultiSelectParameterID
select @xml_variable = ep.property_value
from runtime_extended_property ep
join runtime_configuration rc
on rc.project_id = ep.project_id
and rc.project_vrs_id = ep.project_vrs_id
and rc.runtime_configuration_id = ep.runtime_configuration_id
and rc.default_configuration = 1
where ep.project_id = @project_id
and ep.project_vrs_id = @project_vrs_id
and ep.extended_property_id = @extended_property_id
group by ep.property_value

--Connect the task to the template
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
,concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,5
,@control_proc_id
,@control_proc_id
from @task t

--Fill the variables.
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
,concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'comma' as task_parmtr_id
,case when x.min_abs_order_no = tp.abs_order_no then ' ' else ',' end as parmtr_value
,tp.abs_order_no * 10
,0 as no_line_when_empty
from @task t
join task_parmtr tp
on tp.project_id = @project_id
and tp.project_vrs_id = @project_vrs_id
and tp.task_id = t.task_id
cross apply (select min(abs_order_no) as min_abs_order_no
from task_parmtr tpm
where tpm.project_id = tp.project_id
and tpm.project_vrs_id = tp.project_vrs_id
and tpm.task_id = tp.task_id
and tpm.task_id <> @xml_variable
) x
where tp.task_parmtr_id <> @xml_variable

union all

select @project_id
,@project_vrs_id
,concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'task_parmtr_id' as task_parmtr_id
,tp.task_parmtr_id as parmtr_value
,tp.abs_order_no * 10
,0 as no_line_when_empty
from @task t
join task_parmtr tp
on tp.project_id = @project_id
and tp.project_vrs_id = @project_vrs_id
and tp.task_id = t.task_id
where tp.task_parmtr_id <> @xml_variable

union all

select @project_id
,@project_vrs_id
,concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'dom_id' as task_parmtr_id
,tp.dom_id as parmtr_value
,tp.abs_order_no * 10
,0 as no_line_when_empty
from @task t
join task_parmtr tp
on tp.project_id = @project_id
and tp.project_vrs_id = @project_vrs_id
and tp.task_id = t.task_id
where tp.task_parmtr_id <> @xml_variable

union all

select @project_id
,@project_vrs_id
,concat_ws('_','task',t.task_id)
,concat_ws('_','task',t.task_id,'xml_table')
,'xml_variable' as task_parmtr_id
,@xml_variable as parmtr_value
,10
,0 as no_line_when_empty
from @task t

 

Result of a task which has XML as task parameter ID and has “order_line_id” and "shipping_order_id” as parameter in task parameter.

declare @xml_table table (
order_line_id id
,shipping_order_id id
)
insert into @xml_table (
order_line_id
,shipping_order_id
)
select
row.value('./order_line_id[1]', 'id')
,row.value('./shipping_order_id[1]', 'id')
from @xml.nodes('/rows/row') as c(row)

 

Userlevel 7
Badge +11

Hi pvleeuwen, 

Can you please file a ticket in TCP for this? Thanks.

Userlevel 1
Badge +2

Hi @Jasper ,

The multirow variable isn’t filled for me.

I followed the steps you described.

When I try to view the content of the variable with tsf_send_message it’s just empty for me.

Also the task still seems to be executed multiple times.

 

Can you help me with this?

 

Regards,

Peter

Userlevel 5
Badge +15

I've created a custom validation to prevent having “Popup for each row” checked while using the multi row feature (which will result in a null value). For those who are interested;

insert into validation_msg 
(
project_id,
project_vrs_id,
validation_id,
pk_col_1,
pk_col_2,
pk_col_3,
pk_col_4,
pk_col_5,
pk_col_6,
pk_col_7,
generated,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select @project_id
, @project_vrs_id
, @validation_id
, @project_id as pk_col_1
, @project_vrs_id as pk_col_2
, t.task_id as pk_col_3
, null as pk_col_4
, null as pk_col_5
, null as pk_col_6
, null as pk_col_7
, 1
, dbo.tsf_user()
, sysdatetime()
, dbo.tsf_user()
, sysdatetime()
from task t
where t.project_id = @project_id
and t.project_vrs_id = @project_vrs_id
and t.popup_for_each_row = 1
and exists (
select 1
from task_parmtr tp
where tp.project_id = t.project_id
and tp.project_vrs_id = t.project_vrs_id
and tp.task_id = t.task_id
and tp.task_parmtr_id = (
select top 1 epo.property_value
from extended_property_overview epo
where epo.project_id = t.project_id
and epo.project_vrs_id = t.project_vrs_id
and epo.extended_property_id = 'MultiselectParameterID'
and epo.runtime_configuration_id = ( -- default
select rc.runtime_configuration_id
from runtime_configuration rc
where rc.project_id = t.project_id
and rc.project_vrs_id = t.project_vrs_id
and rc.default_configuration = 1
)
)
)

Use at own risk 😉.

 

Userlevel 5
Badge +15

@Ricky This is on our backlog, but we have not yet planned a date for this.

It is, however, trivial to locate and rewrite the select (and delete) from the XML to use a table valued parameter. And from a functional point of view, it doesn't make any difference.

Hi @Jasper , can you tell a bit more about the table-valued parameters in general what the plans are? And in what period of time? As I can't find it in the ‘ideas’ backlog.

I assume the multi row solution will be replaced by that, but I think I have another case for which table valued parameters could be useful. But I'm not sure it's part of the same, or something new.

It could be useful when you could design them as ‘Domain’, so it can be used in tasks, subroutines and process flows. We currently use a lot of XML passing data in process flows, but handling XML is pretty heavy for SQL Server. It also could decrease our code base as it's no longer necessary to apply transformations from a table variable, to XML, and back from XML to a table variable.

 

Userlevel 7
Badge +11

Hi Ricky,

I hope for us that by doing so we only need to remove the code above and can use the @records table.

That is indeed how it will need to be modified. 

 

And how to deal with unit tests on multi rows?

We will automatically convert these input strings to the required data structure.

Reply