Is there a way to pull vendor creation date in Dynamics NAV 2016?

Question

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_]

Answer 1

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.

Answer 2

If you don't have the change log active, then the options are

A. pull an old backup and compare.

B. Use something else to infer it.. e.g. find the first Vendor Ledger Entry for each vendor; you'll get a few false positives but might be enough to satisfy the auditors, if you reword their request and delivery "all vendors first used in October 2016 onwards".
Or use some info from your accounts dept. (they must have some record outside of NAV?) If you use sequential number series then you only need to identify the first in your date range.

Longer term, the you could talk to your Dynamics NAV partner and ask them to modify the vendor table to stamp the creation date/time. Although I would normally recommend running the change log on the Vendor Table anyway, as it's something auditors often want to see. I'd also always include the bank accounts table for the same reason.