Note: In order to be able to retrieve any data related to an event change, it is required to configure Ungerboeck Software to track the status change of events in the Audit Log. To do this, please see the Activate Audit Log Tracking article.
The Audit Log entries are stored in the MM999_AUDIT_LOG table. In this table, you will be able to find Event Status changes for the below example.
With the following SQL statement, you can obtain the date and the user ID of the user who made the status change for event status changes. In the below example, the event, old status and new status will be retrieved.
SELECT
MM999_CHG_STAMP,
MM999_CHG_USER_ID
FROM MM999_AUDIT_LOG
WHERE MM999_ORG_CODE = '10' AND
MM999_EVENT_ID = 12553 AND
MM999_TEXT_FROM = '29' AND
MM999_TEXT_TO = '30'
The SQL statement reads the following way:
Get the Date Stamp and the User ID when Event "12553" from Organization "10" changed status from "29" to "30".
Comments
4 comments
Could you do this for a range of events within a certain date range rather than just one specific event?
e.g. if you wanted to see on what date each event in quarter 4 of 2015 was changed to a "confirmed" status.
0 upvotes
I know this is probably a very basic question - but can this be added to the system itself in order to show a column on the event master
Allowing me to create a view to show value of bookings won with a set period of time
thanks
Jo
0 upvotes
Hi Jo,
It is not possible to add SQL generated columns to grids (although that would be awesome!)
But I'm not sure why this article exists. There is a standard field called "Firm" that tracks the date an event moves from a status less than Firm (weighting = 30) to 30 or above (but less than 80 = cancelled).
I'd suggest using this column.
0 upvotes
Hello Jo,
Having the ability to add columns on our standard grids based on a SQL statement sounds like a great idea, but I am sure you can imagine the security and performance risks that this would introduce to the system, which is why we don't offer options like this. Our formula fields would be the closest you can get, buy since they depend on the fields that are already available in the grid, they will likely not solve your problem.
The idea behind sharing this SQL statement is to provide a simple example so our customers can build an EZWriter or a Crystal report that you can make available on your Events window to complement the data you have available there.
It is my understanding that our Software Engineering team is working on some enhancements to our Audit Log module. If I hear of something that could help you get the information you need on this window, I'll post it on this thread.
Additionally, you are welcome to submit an enhancement suggestion (link is available on the main page of this Support Center site) so out Event Management team can get some visibility over what you and the rest of our customers need.
0 upvotes
Please sign in to leave a comment.