Simple Audit for Microsoft Dynamics GP: SQL View for Reporting

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

Once the audit table and triggers have been deployed, any changes made through the audit will be recorded and available for reviewing later to see who has been making changes and, more significantly, what was changed.

The easiest way of making this available to the client was to create a SmartList for them using SmartList Designer to select data from the new custom audit table. SmartList Designer can see either Dexterity tables or SQL views, but not custom SQL tables, I created a SQL view on the custom audit table, joining it to the Users Master (SY01400) table in the DYNAMICS table to get the username:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE VIEW uv_AZRCRV_Audit AS SELECT ['Audit'].RecordType AS 'Record Type' ,['Audit'].RecordID AS 'Record ID' ,['Audit'].UpdateType AS 'Update Type' ,['Audit'].USERID AS 'User ID' ,['User Master'].Username AS 'Username' ,FORMAT(['Audit'].ChangeDateTime, 'yyyy-MM-dd') AS 'Change Date' ,FORMAT(['Audit'].ChangeDateTime, 'HH:ss') AS 'Change Time' ,['Audit'].OldData AS 'Old Data' ,['Audit'].NewData AS 'New Data' FROM ut_AZRCRV_Audit ['Audit'] LEFT JOIN DYNAMICS..SY01400 AS ['User Master'] ON ['User Master'].USERID = ['Audit'].USERID GO GRANT SELECT ON uv_AZRCRV_Audit TO DYNGRP GO

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *