Join (jump in to) customer table

I am trying to jump in to the customer table within nav… Here is my Query that i am running and the tables that i am currently in. With table structure diagrams how can a new user make the relationships between tables with so many?

SELECT

a.[Serial No_],
a.[Bit Size],
a.[Bit Type],
b.[Asset Location],
b.[District],
c.[Document Date]

FROM [Ulterra Drilling Technologies$Asset] AS a
JOIN [Ulterra Drilling Technologies$Asset Location Entry] AS b ON a.[Serial No_] = b.[Asset Serial No_]
JOIN [Ulterra Drilling Technologies$Job Ledger Entry] as c ON a.[Serial No_] = c.[Serial No_]

WHERE (b.[Current Location] = 1)
and (a.[Serial No_]=COALESCE(@serialNo,a.[Serial No_]))
and (a.[Bit Size]=COALESCE(@bitSize,a.[Bit Size]))
and (a.[Bit Type]=COALESCE(@bitType,a.[Bit Type]))
and (b.[Asset Location]=COALESCE(@assetLoc,b.[Asset Location]))
and (b.[District]=COALESCE(@dist,b.[District]))
and (c.[Document Date]=COALESCE(@docDate,c.[Document Date]))

As a NAV developer you don’t write any queries directly, so I doubt you will find many people who even understand what that means.

Also, we NEVER work directly in SQL Server to modify any table design, that is all done in the NAV table designer.

What are you trying to do, understand the relationships to/from the customer table? Where’s your link from the asset tables to the Customer table?

Yes where is the link from the asset table to the Customer table. I am writing a report in BIDS and need to join in the customer table…

The report is pretty in-depth. Since I am pretty green with nav reports I was planning on preparing the report with SSRS. Is this not the right way to do a report like this ?

Search Criteria:

  1. Dull Date Range

  2. Tool Size & Type

  3. Tool SN#

  4. District

  5. Location

  6. Customer

Output:

  1. Tool SN#

  2. Tool Size & Type

  3. Run #

  4. District

  5. Location

  6. Dull date

  7. Customer

  8. Revenue

  9. Run Cost

  10. Commission

  11. Run Margin

  12. Total Revenue

  13. Total Cost

  14. Margin to date

I don’t know what the link is. Both the “Asset” and “Asset Location Entry” tables are custom tables, they are not part of the standard database. If there is a link I would expect a field called “Customer No.”. There might be a conditional relationship where you have a “Source Type” field and a “Source No.” field that links to different tables depending on the value of the “Source Type” field. Those Type fields are stored as integers in SQL Server, so you would need to map those to the right values by looking at the table design in NAV.