Automatically refresh activated documents

  • 9 February 2021
  • 2 replies
  • 88 views

Userlevel 3
Badge +10

Goal

After opening a screen the dataset is loaded within this screen. When you open a different screen and afterwards reactivate the screen, which you had opened previously, the dataset is still the same. You have to manually click on refresh to activate a refresh action within this screen. You could also set an automatic refresh based on a specified schedule, but this is often not the most suitable option.

Image being able to automatically refresh data after reactivating a screen, that is exactly what this topic allows you to configure.

Solution

This topic will guide you through setting up a dynamic Software Factory model template. It will create the following objects:

  • Processflow
    • Per table/variant, determined by which table/variant is accessible through a menu type or table/variant tag
  • Processactions
    • Start
    • Stop
    • Activate document
    • Refresh table
  • Processteps in this order
    • Start process
    • Activate document
    • Refresh table

Implementing it requires you to set a control procedure in the “Dynamic Model” section of the Software Factory. Create a new dynamic model template and set it up with the one of the two following scripts:

1. Using a menu as source

-------------------------------------
-- insert processflow
-------------------------------------
insert into process_flow
(
project_id
,project_vrs_id
,process_flow_id
,api
,deep_link_allowed
,generated
)
select
lbi.project_id -- project_id
,lbi.project_vrs_id -- project_vrs_id
,'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '') -- process_flow_id
,0 -- api
,0 -- deep_link_allowed
,1 -- generated
from list_bar_item lbi
where lbi.project_id = @project_id
and lbi.project_vrs_id = @project_vrs_id
and lbi.tab_id is not null
and not exists(
select
1
from process_flow pf
where pf.project_id = lbi.project_id
and pf.project_vrs_id = lbi.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '')
)
group by
lbi.project_id
,lbi.project_vrs_id
,lbi.tab_id
,lbi.tab_variant_id
;

-------------------------------------
-- insert proces action
-------------------------------------
insert into process_action
(
project_id
,project_vrs_id
,process_flow_id
,process_action_id
,process_action_type
,tab_id
,tab_variant_id
)
----------------------------------------
-- start
----------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'start' -- process_action_id
,98 -- process_action_type (start)
,null -- tab_id
,null -- tab_variant_id
from list_bar_item lbi
join process_flow pf
on pf.project_id = lbi.project_id
and pf.project_vrs_id = lbi.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '')
where lbi.project_id = @project_id
and lbi.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
group by
pf.project_id
,pf.project_vrs_id
,pf.process_flow_id

union all

----------------------------------------
-- stop
----------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'stop' -- process_action_id
,99 -- process_action_type (stop)
,null -- tab_id
,null -- tab_variant_id
from list_bar_item lbi
join process_flow pf
on pf.project_id = lbi.project_id
and pf.project_vrs_id = lbi.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '')
where lbi.project_id = @project_id
and lbi.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
group by
pf.project_id
,pf.project_vrs_id
,pf.process_flow_id

union all

----------------------------------------
-- activate document
----------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'activate_document_' + lbi.tab_id -- process_action_id
,300 -- process_action_type (activate document)
,lbi.tab_id -- tab_id
,lbi.tab_variant_id -- tab_variant_id
from list_bar_item lbi
join process_flow pf
on pf.project_id = lbi.project_id
and pf.project_vrs_id = lbi.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '')
where lbi.project_id = @project_id
and lbi.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
group by
pf.project_id
,pf.project_vrs_id
,pf.process_flow_id
,lbi.tab_id
,lbi.tab_variant_id

union all

----------------------------------------
-- refresh tab
----------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'refresh_' + lbi.tab_id -- process_action_id
,8 -- process_action_type (refresh)
,lbi.tab_id -- tab_id
,lbi.tab_variant_id -- tab_variant_id
from list_bar_item lbi
join process_flow pf
on pf.project_id = lbi.project_id
and pf.project_vrs_id = lbi.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '')
where lbi.project_id = @project_id
and lbi.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
group by
pf.project_id
,pf.project_vrs_id
,pf.process_flow_id
,lbi.tab_id
,lbi.tab_variant_id
;

----------------------------------------
-- process step
----------------------------------------
insert into process_step
(
project_id
,project_vrs_id
,process_flow_id
,process_step_id
,last_process_action_id
,last_process_action_successful
,order_no
,abs_order_no
,next_process_action_id
,process_order_input
,process_order_output
)
---------------------------------------------
-- start
---------------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,pa.process_action_id -- process_step_id
,pa.process_action_id -- last_process_action_id
,2 -- last_process_action_successful (always)
,10 -- order_no
,1 -- abs_order_no
,pa_n.process_action_id -- next_process_action_id
,1 -- process_order_input
,1 -- process_order_output
from process_flow pf
join process_action pa
on pa.project_id = pf.project_id
and pa.project_vrs_id = pf.project_vrs_id
and pa.process_flow_id = pf.process_flow_id
join process_action pa_n -- next step
on pa_n.project_id = pf.project_id
and pa_n.project_vrs_id = pf.project_vrs_id
and pa_n.process_flow_id = pf.process_flow_id
and pa_n.process_action_type = 300 -- activate document
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and pa.process_action_type = 98 -- start
and exists( -- was created by this script
select
1
from list_bar_item lbi
where lbi.project_id = pf.project_id
and lbi.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '') = pf.process_flow_id
)

union all

---------------------------------------------
-- activate document
---------------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,pa.process_action_id -- process_step_id
,pa.process_action_id -- last_process_action_id
,2 -- last_process_action_successful (always)
,20 -- order_no
,2 -- abs_order_no
,pa_n.process_action_id -- next_process_action_id
,1 -- process_order_input
,1 -- process_order_output
from process_flow pf
join process_action pa
on pa.project_id = pf.project_id
and pa.project_vrs_id = pf.project_vrs_id
and pa.process_flow_id = pf.process_flow_id
join process_action pa_n -- next step
on pa_n.project_id = pf.project_id
and pa_n.project_vrs_id = pf.project_vrs_id
and pa_n.process_flow_id = pf.process_flow_id
and pa_n.process_action_id = 'refresh_' + pa.tab_id
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and pa.process_action_type = 300 -- activate document
and exists( -- was created by this script
select
1
from list_bar_item lbi
where lbi.project_id = pf.project_id
and lbi.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '') = pf.process_flow_id
)

union all

---------------------------------------------
-- activate document
---------------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,pa.process_action_id -- process_step_id
,pa.process_action_id -- last_process_action_id
,2 -- last_process_action_successful (always)
,30 -- order_no
,3 -- abs_order_no
,'stop' -- next_process_action_id
,1 -- process_order_input
,1 -- process_order_output
from process_flow pf
join process_action pa
on pa.project_id = pf.project_id
and pa.project_vrs_id = pf.project_vrs_id
and pa.process_flow_id = pf.process_flow_id
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and pa.process_action_type = 8 -- refresh tab
and exists( -- was created by this script
select
1
from list_bar_item lbi
where lbi.project_id = pf.project_id
and lbi.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + lbi.tab_id + ISNULL('_' + lbi.tab_variant_id, '') = pf.process_flow_id
)
;

This script will use the menu types listbar and tree. Obviously you can also set it for the menu type Tile. If you would like to use the menu type Tile, use the table “tile” instead of “list_bar_item”.

2. Using a table/variant tag as source

-------------------------------------
-- insert processflow
-------------------------------------
insert into process_flow
(
project_id
,project_vrs_id
,process_flow_id
,api
,deep_link_allowed
,generated
)
--------------------------------------
-- tables
--------------------------------------
select
tt.project_id -- project_id
,tt.project_vrs_id -- project_vrs_id
,'process_refresh_' + tt.tab_id -- process_flow_id
,0 -- api
,0 -- deep_link_allowed
,1 -- generated
from tab_tag tt
where tt.project_id = @project_id
and tt.project_vrs_id = @project_vrs_id
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
and not exists(
select
1
from process_flow pf
where pf.project_id = tt.project_id
and pf.project_vrs_id = tt.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + tt.tab_id
)
group by
tt.project_id
,tt.project_vrs_id
,tt.tab_id

union all
--------------------------------------
-- table variants
--------------------------------------
select
tvt.project_id -- project_id
,tvt.project_vrs_id -- project_vrs_id
,'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '') -- process_flow_id
,0 -- api
,0 -- deep_link_allowed
,1 -- generated
from tab_variant_tag tvt
where tvt.project_id = @project_id
and tvt.project_vrs_id = @project_vrs_id
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
and not exists(
select
1
from process_flow pf
where pf.project_id = tvt.project_id
and pf.project_vrs_id = tvt.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '')
)
group by
tvt.project_id
,tvt.project_vrs_id
,tvt.tab_id
,tvt.tab_variant_id
;

-------------------------------------
-- insert proces action
-------------------------------------
insert into process_action
(
project_id
,project_vrs_id
,process_flow_id
,process_action_id
,process_action_type
,tab_id
,tab_variant_id
)
----------------------------------------
-- start
----------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'start' -- process_action_id
,98 -- process_action_type (start)
,null -- tab_id
,null -- tab_variant_id
from process_flow pf
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and ( exists( -- based upon table tag
select
1
from tab_tag tt
where tt.project_id = pf.project_id
and tt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tt.tab_id = pf.process_flow_id
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
or exists( -- based upon table variant tag
select
1
from tab_variant_tag tvt
where tvt.project_id = pf.project_id
and tvt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '') = pf.process_flow_id
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
)

union all

----------------------------------------
-- stop
----------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'stop' -- process_action_id
,99 -- process_action_type (stop)
,null -- tab_id
,null -- tab_variant_id
from process_flow pf
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and ( exists( -- based upon table tag
select
1
from tab_tag tt
where tt.project_id = pf.project_id
and tt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tt.tab_id = pf.process_flow_id
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
or exists( -- based upon table variant tag
select
1
from tab_variant_tag tvt
where tvt.project_id = pf.project_id
and tvt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '') = pf.process_flow_id
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
)

union all

----------------------------------------
-- activate document
----------------------------------------
--------------------------
-- table
--------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'activate_document_' + tt.tab_id -- process_action_id
,300 -- process_action_type (activate document)
,tt.tab_id -- tab_id
,null -- tab_variant_id
from tab_tag tt
join process_flow pf
on pf.project_id = tt.project_id
and pf.project_vrs_id = tt.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + tt.tab_id
where tt.project_id = @project_id
and tt.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'

union all

--------------------------
-- table variant
--------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'activate_document_' + tvt.tab_id -- process_action_id
,300 -- process_action_type (activate document)
,tvt.tab_id -- tab_id
,tvt.tab_variant_id -- tab_variant_id
from tab_variant_tag tvt
join process_flow pf
on pf.project_id = tvt.project_id
and pf.project_vrs_id = tvt.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '')
where tvt.project_id = @project_id
and tvt.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'

union all

----------------------------------------
-- refresh tab
----------------------------------------
-----------------------------
-- table
-----------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'refresh_' + tt.tab_id -- process_action_id
,8 -- process_action_type (refresh)
,tt.tab_id -- tab_id
,null -- tab_variant_id
from tab_tag tt
join process_flow pf
on pf.project_id = tt.project_id
and pf.project_vrs_id = tt.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + tt.tab_id
where tt.project_id = @project_id
and tt.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'

union all

-----------------------------
-- table variant
-----------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,'refresh_' + tvt.tab_id -- process_action_id
,8 -- process_action_type (refresh)
,tvt.tab_id -- tab_id
,tvt.tab_variant_id -- tab_variant_id
from tab_variant_tag tvt
join process_flow pf
on pf.project_id = tvt.project_id
and pf.project_vrs_id = tvt.project_vrs_id
and pf.process_flow_id = 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '')
where tvt.project_id = @project_id
and tvt.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
;

----------------------------------------
-- process step
----------------------------------------
insert into process_step
(
project_id
,project_vrs_id
,process_flow_id
,process_step_id
,last_process_action_id
,last_process_action_successful
,order_no
,abs_order_no
,next_process_action_id
,process_order_input
,process_order_output
)
---------------------------------------------
-- start
---------------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,pa.process_action_id -- process_step_id
,pa.process_action_id -- last_process_action_id
,2 -- last_process_action_successful (always)
,10 -- order_no
,1 -- abs_order_no
,pa_n.process_action_id -- next_process_action_id
,1 -- process_order_input
,1 -- process_order_output
from process_flow pf
join process_action pa
on pa.project_id = pf.project_id
and pa.project_vrs_id = pf.project_vrs_id
and pa.process_flow_id = pf.process_flow_id
join process_action pa_n -- next step
on pa_n.project_id = pf.project_id
and pa_n.project_vrs_id = pf.project_vrs_id
and pa_n.process_flow_id = pf.process_flow_id
and pa_n.process_action_type = 300 -- activate document
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and pa.process_action_type = 98 -- start
and ( exists( -- based upon table tag
select
1
from tab_tag tt
where tt.project_id = pf.project_id
and tt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tt.tab_id = pf.process_flow_id
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
or exists( -- based upon table variant tag
select
1
from tab_variant_tag tvt
where tvt.project_id = pf.project_id
and tvt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '') = pf.process_flow_id
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
)

union all

---------------------------------------------
-- activate document
---------------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,pa.process_action_id -- process_step_id
,pa.process_action_id -- last_process_action_id
,2 -- last_process_action_successful (always)
,20 -- order_no
,2 -- abs_order_no
,pa_n.process_action_id -- next_process_action_id
,1 -- process_order_input
,1 -- process_order_output
from process_flow pf
join process_action pa
on pa.project_id = pf.project_id
and pa.project_vrs_id = pf.project_vrs_id
and pa.process_flow_id = pf.process_flow_id
join process_action pa_n -- next step
on pa_n.project_id = pf.project_id
and pa_n.project_vrs_id = pf.project_vrs_id
and pa_n.process_flow_id = pf.process_flow_id
and pa_n.process_action_id = 'refresh_' + pa.tab_id
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and pa.process_action_type = 300 -- activate document
and ( exists( -- based upon table tag
select
1
from tab_tag tt
where tt.project_id = pf.project_id
and tt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tt.tab_id = pf.process_flow_id
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
or exists( -- based upon table variant tag
select
1
from tab_variant_tag tvt
where tvt.project_id = pf.project_id
and tvt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '') = pf.process_flow_id
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
)

union all

---------------------------------------------
-- activate document
---------------------------------------------
select
pf.project_id -- project_id
,pf.project_vrs_id -- project_vrs_id
,pf.process_flow_id -- process_flow_id
,pa.process_action_id -- process_step_id
,pa.process_action_id -- last_process_action_id
,2 -- last_process_action_successful (always)
,30 -- order_no
,3 -- abs_order_no
,'stop' -- next_process_action_id
,1 -- process_order_input
,1 -- process_order_output
from process_flow pf
join process_action pa
on pa.project_id = pf.project_id
and pa.project_vrs_id = pf.project_vrs_id
and pa.process_flow_id = pf.process_flow_id
where pf.project_id = @project_id
and pf.project_vrs_id = @project_vrs_id
and pf.generated = 1 -- only select generated
and pa.process_action_type = 8 -- refresh tab
and ( exists( -- based upon table tag
select
1
from tab_tag tt
where tt.project_id = pf.project_id
and tt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tt.tab_id = pf.process_flow_id
and tt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
or exists( -- based upon table variant tag
select
1
from tab_variant_tag tvt
where tvt.project_id = pf.project_id
and tvt.project_vrs_id = pf.project_vrs_id
and 'process_refresh_' + tvt.tab_id + ISNULL('_' + tvt.tab_variant_id, '') = pf.process_flow_id
and tvt.tag_id = 'REFRESH_AFTER_ACTIVATING_DOCUMENT'
)
)
;

This template requires you to set a tag called “REFRESH_AFTER_ACTIVATING_DOCUMENT”. It does not require a tag value. You can assign this tag to any table or table variant. This tag is used to mark the tables and/or table variants you would like automatically refresh upon activating.

Best practice

This powerful tool, to refresh your screen, is often used when the application hides the “Open documents” tab in Universal. Since a user cannot see that his/her document is already open, they otherwise would not understand that they have to manually refresh the screen after clicking on the menu item.

Naturally this tool can also be used to help users refresh their data, when they can see that they already had opened this tab.


2 replies

Userlevel 2
Badge +10

Arjan, I would prefer an option in the model (low code). 

Userlevel 3
Badge +10

@Andre te Raa  I have added a section script that will allow you to tag tables and table variants.

Reply