Which SQL Table hold historic year Sales Transactions

Hi

I am trying to write a SQL Query for a report from navision data.

I know that

[dbo].[3397 - xxxxxxxx$Cust_ Ledger Entry] holds current financial year sales data, at least it does not hold anything before this year.

Which table would hold previous years data?

Thanks in advance

Mike

Hi,

All tables hold all data …

dbo.[company name$table name] is the structure.

customer ledger entty is tzhe one above

there is also Sales Invoice Header and Sales Invoice Line

and

Sales Cr.Memo Header and Sales Cr.Memo Line

gabor

The same table will have all data…please check whether you have filer applied on the table.

Or that they have been using NAV for more than a year [:D]

Some general rules:

Do NOT make reports from document tables, use ONLY Ledger tables, e.g Customer Ledger, not SI Header & SI Line.

When accessing Cust \ Vend \ Item ledgers directly with T-SQL, don’t be surprised they lack many fields - these all are FlowFields. You need to mimicrize these FlowFields as subselect in SQL, summarizing data from respective “Detailed xxxxx Ledger Entries” table fields.

updated: To be precise - in case with Item Ledger details are in Value Entry table…

Hi Ivan ,

Why not to make report from SIH and SIL ?

G.

[Y]

Modris, you would be surprised just how many NAV developers, even those that have been doing Navision for years, still make this fundamental mistake (just after they increased the Item No. Field to 30 characters). When I go into a new customer site to help them recover a disaster, its one of the first thing I look for to see if the developers have gone in and screwed things up.

The problem really is that developers are generally lazy, so rather than think of the system performance and future stability, they take the easy way out.

Sorry I just made a type with your Name !

Sorry Modris !

G.

Gabor - never mind, I’m used to the fact that my surname in English spelling (without diacritical marks) is often treated to be the most known Russian first name [:)]

Returning to topic - why Ledger and NOT Doc tables:

read this http://dynamicsuser.net/forums/p/44291/224072.aspx#224072

I can add another reason, which I forgot in that post - Docs may simply not exist anymore [;)]
Have you ever looked what sits under Administration \ IT Administration \ Data deletion? Many users don’t care about it and never delete anything, but large companies with huge amount of docs do clean their databases, often as soon as FY is closed.

because of performance.

Another not unimportant reason is that you can delete posted invoices from the system, and when that happens, your totals do not add up to the actual financial information.