This is an Archived topic. The solution is available in the Thinkstore inside the Software Factory.
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 modelmeta_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 procedureview_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.
Templateview_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.
Page 1 / 1
@Robbert van Tongeren@Robert Jan de Nie (or someone else from Thinkwise) Can you make your latest version available in the Thinkstore? The version there is still the one with the 'function_input’-error that already has been fixed in this topic, and also the ‘Step 19b’ is missing.
@Robert Jan de Nie@Robbert van Tongeren
After a few hours of debugging yesterday I found that it’s not possible to have an expression field as your display column. That was giving me an error when inserting in the ref table.
This makes sense, since the expression columns aren’t in the DB. But I think it should take the PK as a fallback then. That would be a solution for this problem.
If you could make those adjustments, that would be great.
Regards,
Peter
I have made 2 small adjustments, in the current code the conditional layout is based on the grid_order_no, since there is no guarantee that these are unique you can get in a weird situation with the conditional layout in the view.
In the dynamic model I have changed the value from c.grid_order_no to c.abs_order_no * 10
insert into conditional_layout_condition (project_id ,project_vrs_id ,tab_id ,conditional_layout_id ,col_id ,condition ,type_of_value ,value ,generated_by_control_proc_id ) select c.project_id ,c.project_vrs_id ,c.tab_id ,'flag_' + c.col_id ,@conditional_layout_code ,8 ,0 ,'(' + case when c.col_id = @valid_to then 'x' else cast(c.abs_order_no * 10 as varchar(10)) end + ')' ,@control_proc_id from #desired_col as c where c.col_id not in (@ongeldig_gemaakt_door)
In the control procedure SQL code I have changed how the parameter is filled, it was c.grid_order_no and now it is c.abs_order_no * 10
select @project_id as project_id ,@project_vrs_id as project_vrs_id ,'view_view_' + c.tab_id + '_history' as prog_object_id ,@control_proc_id as prog_object_item_id ,'COL_ORDER_NO' as parmtr_id ,c.abs_order_no * 10 as parmtr_value ,c.abs_order_no as order_no ,0 as no_line_when_empty
For the ones that are interested in this. I have made some extra code which will set the roles the same as the base table. This way the user will never see more column in the logging that he should. Be aware, this will not enable the reference, but only the table with columns. So to be able to see the detail tab you should enable this manually.
declare @setup_roles bit set @setup_roles = 1 --Defines if you want to setup roles based on the base tables.
-- 19B. Assign roles to the history tables when setup. if @setup_roles = 1 begin --Delete the rights to the history tables to be sure the right rights are set. delete rt from role_tab rt where rt.project_id = @project_id and rt.project_vrs_id = @project_vrs_id and rt.tab_id like 'view!_%!_history' escape '!'
delete rc from role_col rc where rc.project_id = @project_id and rc.project_vrs_id = @project_vrs_id and rc.tab_id like 'view!_%!_history' escape '!'
--Add role_tab select rights for the history table according to the existing rights on the base tables. insert into role_tab (project_id ,project_vrs_id ,role_id ,tab_id ,select_granted ,insert_granted ,copy_granted ,update_granted ,delete_granted ,add_grid_granted ,grid_edit_mode_granted ,search_granted ,filter_granted ,sort_granted ,grp_granted ,import_granted ,export_granted ,mass_update_granted ,copy_to_clipboard_granted ,print_preview_granted ,dragging_fields_granted ,insert_user ,insert_date_time ,update_user ,update_date_time ,generated_by_control_proc_id) select rt.project_id ,rt.project_vrs_id ,rt.role_id ,t.tab_id ,1 as select_granted ,0 as insert_granted ,0 as copy_granted ,0 as update_granted ,0 as delete_granted ,0 as add_grid_granted ,0 as grid_edit_mode_granted ,rt.search_granted ,rt.filter_granted ,rt.sort_granted ,rt.grp_granted ,0 as import_granted ,0 as export_granted ,0 as mass_update_granted ,rt.copy_to_clipboard_granted ,rt.print_preview_granted ,rt.dragging_fields_granted ,dbo.tsf_user() as insert_user ,sysdatetime() as insert_date_time ,dbo.tsf_user() as update_user ,sysdatetime() as update_date_time ,@control_proc_id as generated_by_control_proc_id from role_tab rt join tab t on t.project_id = rt.project_id and t.project_vrs_id = rt.project_vrs_id and t.tab_id = concat_ws('_','view',rt.tab_id,'history') where rt.project_id = @project_id and rt.project_vrs_id = @project_vrs_id and rt.select_granted = 1 and not exists (select 1 from role_tab rt2 where rt2.project_id = rt.project_id and rt2.project_vrs_id = rt.project_vrs_id and rt2.role_id = rt.role_id and rt2.tab_id = t.tab_id)
--Add col rights according to the existing col rights on the base table. insert into role_col (project_id ,project_vrs_id ,role_id ,tab_id ,col_id ,col_access_type ,insert_user ,insert_date_time ,update_user ,update_date_time ,generated_by_control_proc_id) select c.project_id ,c.project_vrs_id ,rc.role_id ,c.tab_id ,c.col_id ,1 as col_access_type --read_only ,dbo.tsf_user() as insert_user ,sysdatetime() as insert_date_time ,dbo.tsf_user() as update_user ,sysdatetime() as update_date_time ,@control_proc_id as generated_by_control_proc_id from role_col rc join col c on c.project_id = rc.project_id and c.project_vrs_id = rc.project_vrs_id and c.tab_id = concat_ws('_','view',rc.tab_id,'history') and c.col_id = rc.col_id where rc.project_id = @project_id and rc.project_vrs_id = @project_vrs_id and not exists (select 1 from role_col r2 where r2.project_id = c.project_id and r2.project_vrs_id = c.project_vrs_id and r2.role_id = rc.role_id and r2.tab_id = c.tab_id and r2.col_id = c.col_id)
union all
--When having rights on the base table, automatically add rights to the valid cols. select rt.project_id ,rt.project_vrs_id ,rt.role_id ,c.tab_id ,c.col_id ,1 as col_access_type --read_only ,dbo.tsf_user() as insert_user ,sysdatetime() as insert_date_time ,dbo.tsf_user() as update_user ,sysdatetime() as update_date_time ,@control_proc_id as generated_by_control_proc_id from role_tab rt join tab t on t.project_id = rt.project_id and t.project_vrs_id = rt.project_vrs_id and t.tab_id = concat_ws('_','view',rt.tab_id,'history') join col c on c.project_id = t.project_id and c.project_vrs_id = t.project_vrs_id and c.tab_id = t.tab_id and c.col_id in (@valid_from,@valid_to) where rt.project_id = @project_id and rt.project_vrs_id = @project_vrs_id and rt.select_granted = 1 and not exists (select 1 from role_col r2 where r2.project_id = rt.project_id and r2.project_vrs_id = rt.project_vrs_id and r2.role_id = rt.role_id and r2.tab_id = c.tab_id and r2.col_id = c.col_id) end
@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!
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.
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.
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.
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.
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.
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 :)
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.
@Roy van Uffelen Did you create the domains as stated on line 42 and 43 on the Dynamic_model procedure?
We're getting closer. Currently we've come to the following messages:
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.
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:
Hi Roy,
Looks to me like the dynamic model template contains a small error:
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.
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.