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.
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
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.