Get Workflow state changes using Change History¶
This example show how to get the Workflow States from an object
All examples use the Sample Vault as a bas
Get and review tables used in the example
SELECT * FROM dbo.MFClass AS mc
EXEC spmfcreatetable 'purchase invoice'
EXEC spmfupdatetable 'MFPurchaseInvoice',1
SELECT * FROM dbo.MFPurchaseInvoice AS mpi
prepare table : mark the costomers for which the comment history is required
UPDATE dbo.MFPurchaseInvoice
SET Process_ID = 5
get Workflow States This is the initial pull
DECLARE @ProcessBatch_id INT;
EXEC dbo.spMFGetHistory
@MFTableName = 'MFPurchaseInvoice',
@Process_id = 5,
@ColumnNames = 'Workflow_State_id' ,
@IsFullHistory = 1,
@ProcessBatch_id = @ProcessBatch_id OUTPUT,
@Debug = 0
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_id
setup to include change history in daily updates
-- check current settings
SELECT * FROM dbo.MFObjectChangeHistoryUpdateControl
-- insert new setting
INSERT INTO MFObjectChangeHistoryUpdateControl
(MFTableName,ColumnNames)
VALUES
('MFPurchaseInvoice','Workflow_State_id')
setup to ensure that the history will be updated every time the table is updated
DECLARE @MFLastUpdateDate SMALLDATETIME
EXEC dbo.spMFUpdateMFilesToMFSQL @MFTableName = N'MFPurchaseInvoice',
@MFLastUpdateDate = @MFLastUpdateDate OUTPUT,
@UpdateTypeID = 1,
@WithObjectHistory = 1
use the following procedure to update history regularly without updating the class table, especially if individual objects need to be updated
DECLARE @ProcessBatch_ID INT;
EXEC dbo.spMFUpdateObjectChangeHistory @MFTableName = N'MFPurchaseInvoice',
@WithClassTableUpdate = 0,
@Objids = null,
@IsFullHistory = 0,
@ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
@Debug = 0
review results in history table
SELECT * FROM dbo.MFObjectChangeHistory AS moch2
-- use a join to show related information for reporting purposes
SELECT mpi.Class_ID,
mpi.ObjID,
moch.MFVersion,
mua.LoginName,
mpi.Name_Or_Title,
moch.Property_Value,
mws.Name
FROM dbo.MFPurchaseInvoice mpi
INNER JOIN dbo.MFObjectChangeHistory AS moch
ON moch.Class_ID = mpi.Class_ID
AND moch.ObjID = mpi.ObjID
INNER JOIN dbo.MFUserAccount AS mua
ON mua.UserID = moch.MFLastModifiedBy_ID
INNER JOIN dbo.MFWorkflowState AS mws
ON moch.Property_Value = mws.MFID
WHERE mpi.ObjID = 361
-- who caused the workflow state change for the current object
SELECT mpi.Class_ID,
mpi.ObjID,
moch.MFVersion,
mua.LoginName,
mpi.Name_Or_Title,
moch.Property_Value,
mws.Name
FROM dbo.MFPurchaseInvoice mpi
INNER JOIN dbo.MFObjectChangeHistory AS moch
ON moch.Class_ID = mpi.Class_ID
AND moch.ObjID = mpi.ObjID
AND moch.MFVersion = mpi.MFVersion
INNER JOIN dbo.MFUserAccount AS mua
ON mua.UserID = moch.MFLastModifiedBy_ID
INNER JOIN dbo.MFWorkflowState AS mws
ON moch.Property_Value = mws.MFID
WHERE mpi.ObjID = 361