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

If there is no field then obviously no. But there is a possibility that you have change log enabled for this table. There you could find an entry about vendors being created. Table is `Change Log Entry`. — Mak Sim, Feb 04 '17 at 06:59
Other than restoring a backup of the database from before the time period and querying the difference between the two tables the only other way this can be accomplished moving forward without involving a developer is as Mak suggests. Enable change log of the vendor table's insert trigger. Of course if there is information they may want to track changes or deletions you may want to consider enabling those triggers as well. Unfortunately the only sure-fire way to identify this in your current circumstance is by restoring a backup. — Daniel, Feb 04 '17 at 12:06

score 1 · Accepted Answer · answered Feb 05 '17 at 00:19

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.

yes, that's right. The only way to find out is the Change Log — azatoth, Feb 17 '17 at 09:46

JeffUK · Answer 2 · 2017-02-24T09:22:14.667

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.

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

2 Answers2