Simple Audit for Microsoft Dynamics GP: SQL View for Reporting

This 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 an IT Professional (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