Multirow task execution

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.

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

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

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

Of course, sorry about that!

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

 

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 ) 

  @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

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?