Multirow task execution

  • 4 February 2019
  • 34 replies
  • 1968 views
Multirow task execution
Jasper
Rank
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.
 

34 replies

Harm Horstman
Rank
Userlevel 5
Badge +19

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.

Harm Horstman
Rank
Userlevel 5
Badge +19

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.

 

M
Rank

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?

Jasper
Rank
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

M
Rank

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

Jasper
Rank
Userlevel 7
Badge +11

Of course, sorry about that!

htimmermans
Rank
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…

 

 

Regards, Henri Timmermans Software Engineer @BAS Holding B.V.
Jasper
Rank
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.

htimmermans
Rank
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 ) 

Regards, Henri Timmermans Software Engineer @BAS Holding B.V.

Reply


Terms & ConditionsCookie settings