Multirow task execution

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 @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

Of course, sorry about that!

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.

  @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