Enhancement: PMPService – new builtin fields, new action type DELETED, new flags projectexists and islastchange

Each project change – Insert, Update, Delete – is now stored to projects_history table and PMPService processes changes from this table so no update is missed and also deletion of projects is tracked now with new type DELETED.

These Excel names are now added and updated automatically so no need to add these fields to row 2 of “Actions” sheet:

PM_ID, PM_NUMBER, PM_CREATIONDATE, PM_LASTCHGDATE, PM_LASTCHGUSER, PM_LASTQDVPATH, PM_PROJECTVER, PM_PROJECTEXISTS, PM_ISLASTCHANGE, PM_WHOOWN

PM_PROJECTEXISTS – a project can be changed during one PMPService interval several times and could be even deleted. If it is needed to send an email only for still existing projects, inserts and updates of already in the meantime deleted projects can be ignored, Send mail:

=IF(AND(PM_PROJECTEXISTS,PM_ISLASTCHANGE),1,0)

For DELETED action, PM_PROJECTEXISTS is always 0 and PM_ISLASTCHANGE always 1, so for this action those flags can be ignored.

PM_ISLASTCHANGE – a project can be changed during one PMPService interval several times. If it is needed to send an email only for last change, this flag can be tested for 1 and intermediate updates ignored.

Example:

clip0035

Projects 20 and 21 are created, both are then updated several times and then deleted. Project 22 is created and updated 2 times, it still exists (projectexists=1). Rows with islastchange=1 indicate last updates for projects 20, 21,22.

Set up of tracking of changes for PMPService:

•	Install new ProMo+ version on at least one computer

•	Run ProMo+ Administration tool and run ‘Create SQL file’ function

•	Open the new PMCreateDatabaseScript_SQLServer.sql in SQL Server Management Studio

•	To enable tracking of changes, search for this line: SET @enableProjectUpdatesTracking = 0 and change 0 to 1. To disable tracking of changes, search for this line: SET @enableProjectUpdatesTracking = 1 and change 1 to 0 (default value).

•	Run the sql script, in output you can verify:

Create table dbo.projects_history
Create Trigger InsertUpdateProjectTrigger

Table dbo.projects_history already exists
Create Trigger InsertUpdateProjectTrigger
Disable project updates tracking - Drop Trigger InsertUpdateProjectTrigger

3.48.1

3.48.1

Enhancement: PMPService – new builtin fields, new action type DELETED, new flags projectexists and islastchange

Each project change – Insert, Update, Delete – is now stored to projects_history table and PMPService processes changes from this table so no update is missed and also deletion of projects is tracked now with new type DELETED.