join two tables

How do i join the [Sales Line], [Sales Invoice Header] tables… I have attempted to use the No_ in both tables but this does not seem to be the correct join, also how would i find out this information on my own in the future

Well, they are not related. I’m guessing you mean Sales Invoice Line and Sales Invoice Header. You can look at the Primary Keys for both tables, as well as the TableRelation property of the PK fields in NAV to get a better idea.

Oo ok yes Sales Invoice Header and Sales line…

PK of sales line

Document Type

Document No_

Line No_

PK of Sales Invoice Header

No_

But sales line Document No_ and Sales Invoice Header No_ does not seem to be the correct link to the tables…

nevermind i need to take your suggestion of an earlier post and turn on the keys in a local install so i can see the relationship… i was trying to get the order date and i ended up getting it from

join [Sales Line] b on b.[Bill-to Customer No_]=a.[No_]

join [Sales Header] c on b.[Document No_]=c.[No_]

Or… you learn how to use the NAV object designer to look at table design there.

is there any whitepaper / blogs that will describe how to look at the table designs with the object designer. I am still pretty green to the nav erp system

I told you before… download the two development courses from customersource. If you don’t have customersource, ask your NAV partner for a login, you should have access if you’re up to date on your enhancement plan. If you are not up to date then I recommend David Studebaker’s book called “Programming Microsoft Dynamics NAV 2009”.

I am still attempting to get a customer source log in, my manager was let go 2 weeks after i was hired. I am a back end BIDS developer attempting to gather as much information as possible on the current ERP system. I have done most of my work with hard coded TSQL in SSRS. I agree learning the front end of NAV is beneficial to be successful in my business. Thanks for the recommendations!

I understand your situation is not ideal, and I’m hoping that you are finding useful information here. There are a bunch of books out on NAV, and most of them are published by a company called Packt Publishing. In addition to the one written by David Studebaker about programming, there’s also a programming cookbook written by Matt Traxinger that might be helpful.

Besides that, there is also online help: http://msdn.microsoft.com/en-us/library/aa306436.aspx

I’ll point out the same thing again: They are not related. You cannot correctly join Sales Invoice Header and Sales Line. You can join Sales Header with Sales Line, or Sales Invoice Header with Sales Invoice Line. Based on looking at the data and looking at the primary keys the join should be clear.