Reporting on Field Changes

[Kieron Jones]

Hi Guys,

Since the sys_history_set and sys_history_line and sys_audit tables are far to large to realistically report from (and, in fact, I believe that they have been prevented from being reported on by Snow anyway), I was wondering if anyone out here has come up with a reasonable solution to collecting data on changes to fields.

So, for example, if the status field changes on a planned_task and we needed to report on when, who, what (from and to) and other project related detail, what would be the best way in absence of the audit and history tables?

My original (perhaps not so) suggestion was to collect this specific information to a user defined table, based on code in the form, or triggers of some kind upon change of the field. We then report of that table.

Issues are that this type of collection and reporting is very localised and what happens if other users in other departments want to start doing the same thing, how do we scale this up to meet that requirement?

Anyone come across this, or have any good suggestions - obviously we don't want to re-create another history or log table of inordinate size!

Thanks for any help

Kieron

[Norman Moore · Accepted solution]

You are on the right track. This user defined table would be used for a custom report. So on create, update, or delete you would insert into this new table. 

The benefit is that you are only tracking reporting changes and not every single change in the system as sys_audit does, which now will not timeout.

[Sebas Di Loreto]

I think there is a way using a metric following the STATE field.

I want to be clear that you mentioned the planned_task table and that is a broad statement. Use this URL in your instance to realize how many other tables extend from the planned_task table: 25 for me.

/nav_to.do?uri=%2Fsys_db_object_list.do%3Fsysparm_query%3DnameINjavascript:getTableExtensions(%2522planned_task%2522)%26sysparm_first_row%3D1%26sysparm_view%3D

Having said that, by far the ones that have the most records are Project (pm_project table) and Project Task (pm_project_task table). Then I will assume you are talking about those two table for this example.

You need to create a very simple metric definition that just follows the STATE field en each on those two tables (screen below). Go to Metrics/Definitions on the left nav.

Then every time the state changes the metric_instance table will show an entry for it. It will show who made the state change. To see these metrics I suggest you to temporarily add the Metric related list to the project and project task FORMS.

Having said that, the metric_instance table shows metrics for absolutely all task records so you DON'T want to report from it. This is when a database view comes into play and there are two out of the box ones that do this for you. Check "database views" on the left nav and look for name "pm_project_metric" and "pm_project_task_metric". You can use these "tables" (not really... they are DB views... a very specific combination of two tables to reduce the number of records you will query) to report. You should add metric.definition=project state duration for example and the columns should dotwalk the project fields you want to show.

Hope this helps.

[Kieron Jones]

Thank you @sdiloreto .

I was under the impression that the Metric data was always related to a duration (which I'm not actually interested in, in this case), but I see now, that irrespective of duration, there are also the "before" and "after" values.

I prefer the OOTB approach as it keep things "in house", however, the solution offered by @Norman Moore does offer that "fine tuning" and a way to more tightly control exactly what one requires, data captured and format of it for reporting etc., unfortunately, this means fighting with our developers for justification!! 🙂

Thanks both!

[Kieron Jones]

I've been misunderstanding this slightly - it's not the Metric table that will hold what I am after reporting on - since this table does NOT hold the "before" and "after" values, it only holds what the current value is after the "calculation" has completed - and it is generally related to a "Duration" of state. That said, it's not the STATE field that I am after reporting on, it is the STATUS field of the Project - this is held on the planned_task table.

I think that the only way to resolve this, is by use of a custom table to hold this change and possibly any other changes related to the Project.

A u_project_audut table would be the way to go...