Chapter 16. LastModified and UserName

In this chapter, we will add columns that show times and user names of the latest updates.

This helps team members to understand who and when changed data.

You can use the following solution in a friendly environment. Otherwise, you have to use database triggers.

Creating New Columns

Let's select the companies worksheet, the Companies table, and launch Publish Wizard.

Follow the steps. At the design tab, add the following columns:

Launch Publish Wizard and add new columns

You can change column names and data types in your solutions.

Specify the same table name at the following step and execute the SQL script to recreate the table.

Select the same table to change its structure

Do not click Finish to skip the creating a new table in the workbook. Click Cancel at this step.

Check the script result and click Cancel to skip inserting the table into Excel

Let's reload data and configuration on the existing worksheet. You will see new fields in the Companies table:

Reload an existing table to see added fields

Let's add the following handlers for the Companies table in the EventHandlers table:

Sample of the FormulaValue handlers

Complete table:

A complete sample of the FormulaValue handlers

The FormulaValue handlers update cell values on row changes using the specified formulas.

=NOW() is an Excel formula. You can use this technique for other formulas too.

=UserName() is a SaveToDB add-in formula. You can use =DomainUserName() also.

The DoNotChange handlers prevent changes in the LastModified and UserName columns.

Let's switch to the companies worksheet and change a couple of cells.

Sample of update LastModified and UserName columns in Microsoft Excel

As you can see, the add-in updates the values in the LastModified and UserName columns.

This technique is very simple. You can add this feature to your tables quickly.

However, note that such columns can be updated with any values directly in a database using SQL.