Structure of Navision SQL tables

Get our Free Newletter

Don't you have the time to be online here at DUG every day? So how do you follow what is going on in the Dynamics industry and community?
If you subscribe to the DUG Newsletter then you can relax! We will make sure that you don't miss the big news!

Check out our
Newsletter Subscription Center
page a complete list of our different newsletters.

This post has 2 Replies | 0 Followers

Male
2 Posts
16 Points
Joined: 2004-10-5
Last Online:
2007-3-13 9:43
Location: United Kingdom
SkySailer Posted: 2004-10-5 16:33 | Locked
Is there any documentation available on how Navision SQL tables relate to each other?

I'm a professional developer working in Visual FoxPro and SQL Server. My clients have asked me to display data from their Navision purchase invoices in my FoxPro application. I can see how the SQL tables
"Purchase Header" and "Purchase Line" relate to each other, but the problem is how to relate these to a user-defined dimension called
"Job/Project". Without this, there is no meaningful route to the invoice data using the FoxPro system as a starting point.

After some random searching I thought the answer was in a table called "Document Dimension" but that appears to be an incomplete solution. It relates purchase orders to some Job/Projects but not others. Any suggestions welcome.
Male
99 Posts
587 Points
Joined: 2001-4-7
Last Online:
2007-3-13 4:34
Location: Manchester United Kingdom
Edward Peggs replied on 2004-10-5 17:01 | Locked
Hi,

sorry Im not entirely familiar with the tables in Navision SQL, but if they are anything like normal Navision, Purchase Invoices relate using the following criteria:

Purchase Invoice Header -> Purchase Invoice Line
Via
"Document No." = "No."
--------------------------------
Purchase Invoice Header -> Posted Document Dimension
Via
"Table ID" = 122
"Document No." = "No."
"Line No." = 0
---------------------------------
Purchase Invoice Line -> Posted Document Dimension
Via
"Table ID" = 123
"Document No." = "Document No."
"Line No." = "Line No."
----------------------------------
Purchase Header (Purchase Order) -> Purchase Line
Via
"Document No." = "No."
----------------------------------
Purchase Header (Purchase Order) -> Document Dimension
Via
"Table ID" = 38
"Document Type" = 1
"Document No." = "No."
"Line No." = 0
----------------------------------
Purchase Line -> Document Dimension
Via
"Table ID" = 39
"Document Type" = 1
"Document No." = "Document No."
"Line No." = "Line No."

Hope this makes sense. Basically, both headers and lines have dimensions against them. Posted documents such as invoices are stored in "Posted Document Dimensions". Unposted in "Document Dimensions".

Do let me know if this does not make sense, or if you need further info.

Regards,
edd

Male
2 Posts
16 Points
Joined: 2004-10-5
Last Online:
2007-3-13 9:43
Location: United Kingdom
SkySailer replied on 2004-10-6 8:38 | Locked
Hi Edward

Thanks a lot, I'm sure the "Posted Document Dimension" table is the missing link in my problem. Not being an accountant myself, it hadn't occurred to me that "Document Dimension" would only contain cross-references to un-posted invoices, and the users hadn't twigged this fact either. I guess I would have found it myself eventually, as I had started a laborious DIY data dictionary, but there are so many tables in there, it would have taken me a long time. Thanks for your help.

Regards from Phil Hawkins.
Page 1 of 1 (3 items) | Get this RSS feed | Bookmark and Share