Hi everyone!

Applications built with the Thinkwise platform are usually quite large. Whether that means the application has a lot of screens, tables, users, or anything else, there typically are processes in the application that take a while. To inform users of the progress, you can use the procedure tsf_send_progress. 

Challenge

The tricky part when having 10, 20, or even more statements inside the process, is to determine how far the process is in its execution. You would rather not manually place the send progress function before or after every statement and assign a progress value. When new statements are added, or removed, all the progress should be recalculated. However, you do want to inform the user and keep the user up-to-date. How to solve this?

This exact scenario occurred in the Software Factory application development. The export model task in the Software Factory is a lengthy process that involves all tables that are part of the model; 295 to be exact at the time of writing. 

Solution

To inform users about the progression of a given process, we have introduced a table-valued function called calculate_progress that you can use to assign the progress parameter of the tsf_send_progress procedure dynamically. Let's dive in:

The calculate_progress function takes four input parameters, namely:

This function is intended to be used with SQL-typed control procedures and the Staged strategy.

With the challenge mentioned earlier, let's look at how the Export model task gives developers smooth progress. The task includes the following template:

-- Send progress
exec tsf_send_progress 'export_branch_progress_tab_id', '<tab tabid=" TAB]"/>', dPROGRESS]

-- Export >TAB]
insert into @data (data_tab_name, json_data)
select 'tTAB]',
  (select 
      lCOL] as /COL_NAME]OCOMMA]
   from MTAB] t1
   where t1.model_id = @model_id
     and t1.branch_id = @branch_id
     for json path)

In this template, nTAB] is replaced by the table name, COL] by its columns, COL_NAME] by the designated alias, and nCOMMA] by a comma if it isn’t the last column. The data is transformed into a JSON and captured in a declared temporary table for later use, which is irrelevant to this blog.

The tsf_send_progress function has three input parameters: the message as defined in the model, the translation of the given table name, and the progress value that we will dynamically assign.

Looking at the control procedure code, the program object item assignments are done like this:

insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select
    'task_export_model',                        -- Task name
    'export_' + t.tab_id,                       -- Unique program object item
    100 + row_number() over(order by t.tab_id), -- Order no
    'export_tab_id'                             -- Template name
from @tabs t -- Collection of tables that need to be exported

This will weave the template export_tab_id for every table included in the @tabs declared table; so all tables that should be exported. The order number is calculated based on alphabetical order.

Now we added another statement that replaces of the tPROGRESS] parameter inside the template:

-- Replace nPROGRESS] with the corresponding progress value items created in this control procedure
declare @total int = (select count(*) from @tabs)

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    p.prog_object_id,
    p.prog_object_item_id,
    'PROGRESS',
    dbo.calculate_progress(1, 98, row_number() over(order by p.order_no), @total),
    1000 + (p.order_no * 10),
    0
from #prog_object_item p
where p.prog_object_id = 'task_export_model'  -- Task name
  and p.prog_object_item_id like 'exportc_]%' -- The desired prefix we used earlier

This will replace the >PROGRESS] parameter inside our code with the calculated progress value. The returned progress value will be between 1 and 98, and is calculated per row in the #prog_object_item temporary table. Row 1 has progress 1, Row 148 has progress 49 and Row 295 has progress 98.

After generating the control procedure, the result will look like this:

Summary

Sending smooth progress updates doesn't have to be a coding conundrum. Using calculate_progress, you can easily determine the progress per record and have the flexibility to start and end the progress at any point. Simply indicate at what number to start, at what number to end, adding a row number, and lastly, give the total amount to records. Your users will be kept in the loop and see a smooth progression. For what process will you introduce this?