With change detection, you can ensure that a document is not refreshed if no changes have been made to the relevant section. It’s a very useful tool for showing users near real-time updated data while they are on the corresponding screen.

We usually determine whether a refresh is needed based on the trace fields in combination with the foreign keys of any references that the relevant screen displays as details.

This topic explains how to dynamically build this code based solely on the 'change detection' checkbox in the model.

Steps

• Create a control procedure with SQL assignment
• Add generic code to the template of this control procedure

  This code is based on UTC times in trace fields. If your trace fields are stored as converted into local time you should also convert the @last_refresh_utc into that local time. However, it is recommended to always use UTC times in your data and convert those times into any local time in your domains:

  

   

   

  -- Refresh data if there is an update or insert after the last refresh
  if @last_refresh_utc is null
  or exists (
            select 1
              from ftable_name]
             where (
                     update_date_time > @last_refresh_utc
                  or insert_date_time > @last_refresh_utc
                   )
               and (afk] = @]fk] or @ fk] is null)
            )
  begin
      set @refresh_data = 1
  end

• Add dynamic code in the (SQL assigned) code tab

  -- Dynamically assign the change detection to all tables that use change detection
  drop table if exists #desired_tab
  
  -- Get all tables that use change detection
  select t.model_id
        ,t.branch_id
        ,t.tab_id
        ,rc.target_col_id
        ,concat('chg_', t.tab_id) as prog_object_id
    into #desired_tab
    from tab          t
    left join ref     r
      on r.model_id = @model_id
     and r.branch_id = @branch_id
     and r.target_tab_id = t.tab_id
     and r.is_detail = 1
    left join ref_col rc
      on rc.model_id = @model_id
     and rc.branch_id = @branch_id
     and rc.ref_id = r.ref_id
   where t.model_id = @model_id
     and t.branch_id = @branch_id
     and t.use_change_detection = 1
     and not exists (
                    select 1
                      from tab_tag tt
                     where t.model_id = tt.model_id
                       and t.branch_id = tt.branch_id
                       and t.tab_id = tt.tab_id
                       and tt.tag_id = 'exclude_change_detection'
                    )
   group by t.model_id
           ,t.branch_id
           ,t.tab_id
           ,rc.target_col_id
  
  -- Connect the template to the prog object item
  insert into #prog_object_item
      (
      prog_object_id
     ,prog_object_item_id
     ,order_no
     ,template_id
      )
  select t.prog_object_id
        ,@control_proc_id
        ,10
        ,@control_proc_id
    from #desired_tab t
   group by t.model_id
           ,t.branch_id
           ,t.tab_id
           ,t.prog_object_id
  
  -- Connect the parameters to the prog object parameters
  insert into #prog_object_item_parmtr
      (
      prog_object_id
     ,prog_object_item_id
     ,parmtr_id
     ,parmtr_value
     ,order_no
     ,no_line_when_empty
      )
  select t.prog_object_id
        ,@control_proc_id
        ,'table_name'
        ,t.tab_id
        ,10
        ,0
    from #desired_tab t
   group by t.branch_id
           ,t.model_id
           ,t.prog_object_id
           ,t.tab_id
  union all
  select t.prog_object_id
        ,@control_proc_id
        ,'fk'
        ,isnull(t.target_col_id, '')
        ,10
        ,iif(t.target_col_id is null, 1, 0)
    from #desired_tab t

   

• Turn on ‘change detection’ for the desired tables
   

  The dynamic code will automatically assign the generic change detection to all tables where change detection is turned on. To exclude tables with change detection you can add the table tag ‘exclude_change_detection’

   

• Generate and execute all program objects

  

All tables with change detection (without the exclude tag) will now automatically have the generic change detection code assigned with its corresponding parameters.