The business has a request for auditing to pull a list of all newly created vendors in NAV 2016 for a given period. I was unable to locate an entry creation date or added date for the vendor table. Is this possible? I'm currently supplying the business with the [Last Modified Date] which includes when a vendor has been added OR updated, but they want to only pull newly added.
My current code:
SELECT CAST([Last Date Modified] AS DATE) AS 'Last Date Modified'
,[No_]
,[Name]
,[Search Name]
,[Address]
,[Address 2]
,[City]
,[Vendor Posting Group]
,[Country_Region Code]
,[Post Code]
,[County]
,[Vendor Type Code]
FROM [Company].[dbo].[Company$Vendor]
WHERE YEAR([Last Date Modified]) = '2016'
and MONTH([Last Date Modified]) IN ('10','11','12')
ORDER BY [Last Date Modified]
,[No_]
If you do have the change log active, the following is a basic query that will get you all insertions to the vendor table:
SELECT
cle.[Primary Key]AS Vendor
, cle.[New Value]
, ven.Name
, CAST(cle.[Date and Time] AS DATE) AS LogDate
, CAST(cle.Time AS TIME(0)) AS LogTime
, cle.[Field No_]
, cle.[Type of Change]
, cle.[User ID]
FROM dbo.[YourCompany$Change Log Entry] cle
left outer JOIN dbo.YourCompany$Vendor ven
ON cle.[Primary Key] = ven.No_
WHERE
cle.[Table No_] = 23
and cle.[Field No_] = 1
AND cle.[Type of Change] = 0
ORDER BY LogDate, LogTime, Vendor
I'm also preparing a blog post on the change log which should be out next week.