Multirow task execution

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.

Of course, sorry about that!

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

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.

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.

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?

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?

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?

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?

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)

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

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.

What we've currently have done is having everywhere the same structure of creating a temporary table, which should be replaceable with the actual solution. We’re everywhere using @multi_row as input and @record as actual table used.

On each template we apply it (almost all tasks), we do something like this:

declare @record table (project_id id not null
 , part_id id not null)

insert into @record (project_id 
 , part_id) 
select x.value('(project_id)[1]', 'id')
 , x.value('(part_id)[1]', 'id')
from @multi_row.nodes('rows/row') as t(x)

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

And how to deal with unit tests on multi rows? Currently I have a long input string (<rows><row><item>1</item></row><row><item>2</item></row></rows>), but that should be converted to some kind of other data structure allowing multiple rows at once? That is less trivial than find and replace?