Make your logging data available in the GUI


Goal

When you have system versioned tables in your database, SQL Server logs all changes made to the data in those tables. This solution makes this logging data available for the user. This can look like this:

 

Solution

The solution in this topic creates views on all system-versioned tables. These views give the user access to the logging data. Also, all of these views get conditional layouts in the Software Factory. These conditional layouts give fields with changed values a color, which makes it easier to analyze the data.

Dynamic model meta_view_history

This code create a view definition for every system versioned table, along with conditional layouts to point out changed data.

When you add this code to the dynamic model, you have to change a few values first:

@screen_type_id                
Your desired screen type: You can keep this null, or change the value.

@original_date_column          
Name of an existing date column with settings that can be copied.

@valid_from    
Name of columns that indicates start of the period.
                
@valid_to                      
Name of columns that indicates end of the period.

@conditional_layout_code  
Column used for condional formating. E.g. (4) means value of column 4 has changed.
     
@date_dom_id
Domain of columns valid_from and valid_to. Use an already existing domain.
                   
@conditional_layout_code_dom_id
Domain of column conditional_layout_code. Use an already existing domain.

Control procedure view_history

This control procedure adds the queries to all views on the system versioned tables. Also, the field conditional_layout_code is filled with the column numbers of the columns with altered values.

Template view_history

The template contains code for the view. COL_ID contains all columns of the table. COL_ID2 contains the columns to be compared for the conditional layout. PK contains the primary key columns in a row, separated by comma's.

In the attachment you will find the code of this solution.


12 replies

Userlevel 3
Badge +5

Great idea.

Unfortunately I get the following error while executing on SF2021.1

Followed by:

Any idea what I should do differently?

PS I tried it twice with @screen_type_id as NULL and as master_detail.

Userlevel 4
Badge +2

Hi Roy,

Looks to me like the dynamic model template contains a small error:
 

False detail screen type

On line 437 you can see that it try to set the detail screen type to ‘logging’, which probably doesn't exist in your project.

Userlevel 3
Badge +5

Hi Kevin,

Good point … unfortunately after changing this screentype, I still get the MERGE error.

After adding five lines for project_id and version and running it on SSMS, I got the following messages:

 

Userlevel 4
Badge +2

Hi Roy,

Looks like there is another ‘hard’ detail screen type used, this time on line 562:
 


The last three error messages are probably because the table hasn't been added during the first failed merge statement.

Userlevel 3
Badge +5

We're getting closer. Currently we've come to the following messages:

 

Badge

@Roy van Uffelen Did you create the domains as stated on line 42 and 43 on the Dynamic_model procedure?

Userlevel 3
Badge +5

Hi Patrick,
We (Kevin and I) also discovered that in the past hour.

The following actions were needed:

  • Create the two domains: datetime_all_use (datetime2) and conditional_layout_code (varchar(200));
  • Create two screen types: logging and logging_detail. It appears that these are in fact the same. This could easily be a grid-only screen type. Just be aware that prefilters are copied from the parent-table
  • The reference between parent-table and history-view needs to be created manually. It may be possible to automatically generate this as well.
    Strange thing is that after generating the definitions, the reference seems to have disappeared. At the same time, drawing it manually produces an error, saying that this reference already exists.
  • Conditional layout is based on grid_order_no. In my model two columns had the same value, so after changing any of these columns, this resulted in two columns that had its background color changed;
  • Sort-order of the history is currently ascending, meaning that the last change is at the bottom of the grid. Would be nice to also change that.
Badge

A workaround for the reference: modify the reference and disable ‘Show look-up’.
After this change the reference will not be deleted.

Or you can modify line 380 in the dynamic-model file but I haven't tested that :)

Userlevel 3
Badge +4

In the past we used log tables but recently we've changed it into temporal tables (system-versioned temporal tables). In addition to this, I would now like to create views on all system-versioned tables to give the users access to the logging data. Exactly as described in this topic. 

You have to change a few values first but I'm probably not doing something correctly yet. It is still not completely clear to me how the blue marked parameters should be filled. 

 

Dynamic model - meta_view_history - @parameters

 

Test control procedure code result

It looks like something small, maybe with a little extra explanation we'll get there. I couldn't find any further documentation on it unfortunately. 

Userlevel 6
Badge +18

In the past we used log tables but recently we've changed it into temporal tables (system-versioned temporal tables). In addition to this, I would now like to create views on all system-versioned tables to give the users access to the logging data. Exactly as described in this topic. 

You have to change a few values first but I'm probably not doing something correctly yet. It is still not completely clear to me how the blue marked parameters should be filled. 

 

Dynamic model - meta_view_history - @parameters

 

Test control procedure code result

It looks like something small, maybe with a little extra explanation we'll get there. I couldn't find any further documentation on it unfortunately. 

Hi Dennis,

This is due to a mandatory column in the table Col named function_input. At line 132, an insert into #desired_col is done, but function_input is not given in the insert statement, so SQL Server will raise an error. Adding the column function_input and giving it a value of 0 will suffice.

Hope this helps. If any other errors occur similar to this, it might be another column. I think this logging data Dynamic model script is created for a 2021.1 Software Factory although I'm not completely sure.

Userlevel 3
Badge +4

Now I get the following error:
 

Is it perhaps possible to renew the code for 2021.2? I certainly think it would be worthwhile as an item in the ThinkStore. 

Userlevel 4
Badge +3

@Dennis van Leeuwen, @Frank Wijnhout I have updated this version to work with 2021.2 version of the SF. I have sent this to Frank separately so he can review/update the current version.

Furthermore I have added and changed some stuff as well:

  • Made lookups work (based on current data)
  • Show all columns in the grid (not based on tab-setting)
  • Optionally show the log as a detail tab
  • Removed hard-coded dependencies and added some checks to make life easier

Also added it as an attachment to this post.

Enjoy!

Reply