Change Data Capture Logic for Navision

Hi All

First of all I am extermely sorry if I am posting in wrong place or asking too basic question.

Actually, I came to know about NAVISION, because we are building a BI solution and NAVISION is one of the source system for me. It is enterily a new system for me. I don’t know anything about it.

Now problem is we have to pull the data from NAVISION system and dump into Datawarehouse.

But, I observed that in NAVISION tables there is no “TIMESTAMP” column, which talks about recent changes like “LAST UPDATED DATE” or “LAST MODIFIED DATE”.

So, every time I have to read the complete NAVSION table and compare with the TARGET table to find out the changes happened after previous load.

Can you please tell me any way to find the recent changes happened in NAVAISON table. In terms of DataWarehouse, I want to apply CDC (Change Data Capture) logic on navision tables.

So that, no need for me to read complete table every time.

Thanks in advance


“Card” tables in their majority have LastModified field, but for you those are of little interst.

“Ledger” type tables don’t have such field for one simple reason - these records DO NOT CHANGE. They can’t even be deleted, only new ones added. Every Ledger table has EntryNo field, which is unique and consecutive (no gaps exist). For data analysis purpose these are your source.

Tables which MAY have changes over time in some fields (e.g. Journals) are of no interest for you, too - look at them as “drafts”, not usable in DW - when these will be posted, corresponding Ledger tables will contain the data you can use for analysis.

So, there is NO NEED to examine data for possible changes - Navision simply doesn’t allow changes by design in tables, that makes sense to be dumped to some external DW.

you can activate the Change Log, and configure the Change Log Setup (Table) List, whether you need to identify on Insertion or Modification or Deletion. then you set for tables that you regularly transfer.

your effort to compare all database can be saved by checking to Change Log table first. Find what was changed since your last sync., then get the record by GET(key-field-1[,key-field-2,…]) to NAV tables.


Thanks for quick reply. As I mentioned NAVISION is completely new for me. So, I am not aware of these “CARD”, “LEDGERE” and “DRAFTS” type of tables. What I have in my hand is list of tables from navision sql server database and these tables we need to consider for our DataWarehosue.

List of table.

SalesLine, SalesHeader, SalesInvoiceLine, SalesInvoiceHeader, Item, Customer, SalesCr_MemoLine, SalesCr_MemoHeader, Location, SalesPerson_Purchaser.

Out of these Item, Customer, Location, SalesPerson_Purchaser are master tables.

SalesLine, SalesHeader, SalesInvoiceLine, SalesInvoiceHeader, SalesCr_MemoLine, SalesCr_MemoHeader. and there are transction tables.

So, atleast I need to capture the changes in the transction table.

Hi Joe

Thanks to you also for quick reply. But, I don’t have any control on NAVSION source system as such.

So, I can not actiavte the change log and do the log setup.

Probably it is already activated,

Can you please tell me how find out wether it is activated or not. If it is activated what tables we need to check.

if you want to see what were changed since certain date, see table 405 change log entry.
if you want to configure what are to be logged, open Administration - Application Setup - General: Change Log Setup.
Change Log Activated - put check mark on it. Then ‘Setup’ - Tables …

Well, these are what I referred to as CARD tables… These have Date Last modified field.

NO and again NO - these Document tables CAN NOT be used for reporting, as many essential figures in them are never updated to actual values - for example, Cost -very important field- contains approximate value, taken from ItemCard at the moment of creating invoice.

When you post the documents, corresponding Ledger tables are filled - Customer Ledger Entry, Vendor Ledger Entry, Item Ledger Entry and of course G/L Ledger entry which is the main one. Some of them have corresponding Detail Ledger Entry tables, some -Item- have Value Entries, and THESE must be used as source for reporting. For example, precise COGS can be fetched from Item LE + Value entry tables - only there FULL & PRECISE info is recorded by means of adding records to Value Entry to reflect all direct and indirect costs. Document tables, as I said already, doesn’t reflect these figures correctly, and can be used later ONLY if you need to reprint the Invoice.

Besides, Document tables can be cleaned up when Acc.period ends - old Docs only take up DB space, and there are special BatchJobs in Navision which delete these unneded records from Docs tables.

It will be difficult to explain to you how Navision data structure is built in one short posting here - if you don’t know anything about it, you should rather get some Navision specialist near you for assistance, otherwise you’ll end up grabbing wrong data from wrong sources and your work will be in vain, in worst case you’ll get wrong figures and data analysis would give you unaccurate results…

Thanks a lot modris

Modris, sorry If I am asking to basic question here.

Actually my client has refered these “Document” table for Revenue Calculation.
But, according to your information, Now we are in big problem.

My reporting need is like getting Revenue, Standered Cost, Profit Margin.

Can you please suggest what are tables we need for these calculation?

Also, as per my knowledge of ERP system, all setting in ERP systems are specific to the business.

So, whatever information you provided are genric or Will it be changing from business to business?

Thanks again

as for satisfying Revenue, Cost and Margin, Item Ledger Entry is sufficiently providing data, and you can start from it.
you need spend more time to understand the table - as what advised by Modris Ivans - then you will see how this table can be linked to other tables, at the time you want to expand your reporting.

keep posting …

Hi Joe

Thanks for reply.

Actually, I am not having proper resource to investigate these things right now :slight_smile:

You can say this place is the only source of information for me. Though I am trying hard to understand all these things.

Can you please give me list of LEDGER tables? Like modris has already sepcify few of them

Customer Ledger Entry, Vendor Ledger Entry, Item Ledger Entry and G/L Ledger.

Thanks again both of you for helping me.

you can open NAV classic client, press Shift+12(this is = Tools - Object Designer). select / click on Tables.
filter on Name, by pressing F7, and write : Entry|Entry|Entry

it will give you all tables containing entries. tho not all will be useful, but you can select what do you need.


But I don’t have access on Navision client. What I can access is Navision SQL Server Database.

Also I want to know how they are related to CARD table (Master Table)

One small question. Document No is the Invoice No.

Also, Can you help me to undesrstand following information with example.

All functional areas don’t have documents, but the more heavily used certainly do. That is why it is important to mention these here.

A document is a simple interface for a user to understand. In general, a document consists of a header record and many line records. There is usually a form that allows you to edit the header and lines together. Together, these two elements are called a document. Basically, a document is a way of entering numerous journal lines in many different functional areas all at once.

Behind the scenes, documents are actually very complicated. They combine the functionality of a master table and a journal table into one. Posting one document can create a dozen or a hundred ledger entries in various functional areas.

Navision Attain uses this interface to simplify the task of the typical user.



Hi Modris

One thing I observed that ITEM and CUSTOMER are having LAST MODIFIED DATE,

But, LOCATION and SALESPERSON_PURCHASER not having this field.

i’m getting confuse now. you don’t have access not NAV client. also, you do not familiar with NAV processes.
but you are working in NAV’s sql database to establish data warehouse, cubes, olap, BI or such as - anchoring to NAV sql database.

for me, this is very strange.

i believe you must have peers who are NAV consultants, where you can distribute the load of designing the tools. but if not, you’d better get one, because you really need to team up with a person who know NAV.

Yes I am totaly aggree with you.

What I thought, by that time I will get some information from discussion with you people.



of course folks here will share things they know voluntarily. but it’s not the case.

you are engaged in a project, and your clock is ticking. you are jeopardizing your time-line and commitment to client.

now, this what you are looking for. if you are working with sql, this should be familiar to you:

the red box is item table (a.k.a as item master)
the blue boxes are all tables containing entries.
the green thick box is Item Ledger Entry table.

table name normally come as [company name][underscore][dollar sign][table name]