spMFGetHistory¶

Returns

1 = Success
-1 = Error

Parameters

@MFTableName nvarchar(128)

Target class table name (e.g., ‘MFCustomer’)

@Process_id int

Use a non-reserved value (not 1–4), e.g., 5; the procedure processes rows in the class table where Process_ID = @Process_id

@ColumnNames nvarchar(4000)

Comma-separated list of class-table column names (mapped properties) to include in the history

@IsFullHistory bit (optional)

Default = 1; include all changes for the specified columns
Set to 0 to use date/age filters

@SearchString nvarchar(4000) (optional)

Optional string filter applied as a contains match

@NumberOFDays int (optional)

Limit to the last N days

@StartDate datetime (optional)

Only include changes from this UTC date/time onward

@Update_ID int (output)

Update run identifier

@ProcessBatch_id int (output)

ProcessBatch ID for logging

@Debug int (optional)

Default = 0
1 = Standard Debug Mode

Purpose¶

Update dbo.MFObjectChangeHistory with the change history of specified properties for selected objects, with optional filters.

Additional Info¶

Only versions where a filtered property changed are recorded. If a property didn’t change for a version, that version isn’t added.

Process_id is reset to 0 after completion of the processing.

Use cases

Show comments made on an object
Show when a state was entered/exited
Show when a property was changed
Discovery reports for changes to certain properties

Using a search criteria is not yet active.

Prerequisites¶

Set process_id in the class table to 5 for all the records to be included

Warnings¶

The columnname must match the property in the ColumnName column of MFProperty

Note that the same filter will apply to all the columns included in the run. Split the get procedure into different runs if different filters must be applied to different columns.

Producing the history for all objects in a large table can take considerable time. Use the filters to reduce the scope and improve performance.

Examples¶

This procedure can show all comments or the last N days’ comments for an object. It’s also handy to assess when a workflow state was changed.

UPDATE dbo.MFCustomer
SET Process_ID = 5
WHERE ID IN (9,10);

DECLARE @RC INT;
DECLARE @TableName NVARCHAR(128) = 'MFCustomer';
DECLARE @Process_id INT = 5;
DECLARE @ColumnNames NVARCHAR(4000) = 'Address_Line_1,Country';
DECLARE @IsFullHistory BIT = 1;
DECLARE @NumberOFDays INT = NULL;
DECLARE @StartDate DATETIME = NULL; -- e.g., DATEADD(DAY,-30,GETUTCDATE())
DECLARE @ProcessBatch_id INT;
DECLARE @Debug INT = 0;
DECLARE @Update_ID INT;

EXECUTE @RC = dbo.spMFGetHistory
   @MFTableName     = @TableName,
   @Process_id      = @Process_id,
   @ColumnNames     = @ColumnNames,
   @SearchString    = NULL,
   @IsFullHistory   = @IsFullHistory,
   @NumberOFDays    = @NumberOFDays,
   @StartDate       = @StartDate,
   @Update_ID       = @Update_ID OUTPUT,
   @ProcessBatch_id = @ProcessBatch_id OUTPUT,
   @Debug           = @Debug;

SELECT *
FROM dbo.MFProcessBatch AS mpb
WHERE mpb.ProcessBatch_ID = @ProcessBatch_id;

SELECT *
FROM dbo.MFProcessBatchDetail AS mpbd
WHERE mpbd.ProcessBatch_ID = @ProcessBatch_id;

Show the results of the table including the name of the property

SELECT toh.*,
     mp.Name AS PropertyName
FROM dbo.MFObjectChangeHistory AS toh
INNER JOIN dbo.MFProperty AS mp
  ON mp.MFID = toh.Property_ID
ORDER BY toh.Class_ID,
     toh.ObjID,
     toh.MFVersion,
     toh.Property_ID;

Show the results of the table for a state change

SELECT toh.*,
     mws.Name AS StateName,
     mp.Name AS PropertyName
FROM dbo.MFObjectChangeHistory AS toh
INNER JOIN dbo.MFProperty AS mp
  ON mp.MFID = toh.Property_ID
INNER JOIN dbo.MFWorkflowState AS mws
  ON toh.Property_Value = mws.MFID
WHERE toh.Property_ID = 39
ORDER BY toh.Class_ID,
     toh.ObjID,
     toh.MFVersion,
     toh.Property_ID;

Changelog¶

Date	Author	Description
2023-09-07	LC	Increase size of property value column to 4000
2021-03-12	LC	resolve bug to update multiple columns
2020-06-25	LC	added exception if invalid column is used
2020-03-12	LC	Revise datetime formatting
2019-09-25	LC	Include fnMFTextToDate to set datetime - dealing with localisation
2019-09-19	LC	Resolve dropping of temp table
2019-09-05	LC	Reset defaults
2019-09-05	LC	Add searchstring option
2019-08-30	JC	Added documentation
2019-08-02	LC	Set lastmodifiedUTC datetime conversion to 105
2019-06-02	LC	Fix bug with lastmodifiedUTC date
2019-01-02	LC	Add ability to show updates in MFUpdateHistory