Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Suggested answer

Database link between job ledger entry and purch inv line

Posted on by 5

Hello,

I created a report in SQL server with data from navision.

Now i see that some data is missing. So my query is not 100 correct.

I want to solve that problem ..

In the table job ledger entry is the missing record. There i have for example 4 different records. I want to join that with the purch inv line tabel. Now i join on job no and document no but ofcourse i don't get 4 rows back but a lot more because on the table purch inv line he finds multiple matches on those 2 values.

So now is my question. How do i create a link between these 2 tables so that i will get the 4 rows back with in every row all the data from job ledger entry and purch inv line.

Thanks a lot !

  • Bodhi Profile Picture
    Bodhi 554 on at
    RE: Database link between job ledger entry and purch inv line

    Just add more field link:

    2 of the 4 records have the same 'No_' in both tables. Both D003: find a different value filed for same No. records and add that field link

    select *

    from [COMPANY$Job Ledger Entry Cmfrt]

    inner join  [COMPANY$Purch_ Inv_ Line]

    on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]

    and [COMPANY$Job Ledger Entry Cmfrt].[No_] = [COMPANY$Purch_ Inv_ Line].[No_] //try to add this line?

    and [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = [COMPANY$Purch_ Inv_ Line].[Job No_] //try to add this line?

    and [COMPANY$Job Ledger Entry Cmfrt].[Job Task No.] = [COMPANY$Purch_ Inv_ Line].[Job Task No.] //example filed link?

    where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'

  • Jens1238 Profile Picture
    Jens1238 5 on at
    RE: Database link between job ledger entry and purch inv line

    Thanks it looks already a lot better.

    Just 1 little issue. 2 of the 4 records have the same 'No_' in both tables. Both D003.

    So now i have 6 results but there should be only 4 records.

  • Bodhi Profile Picture
    Bodhi 554 on at
    RE: Database link between job ledger entry and purch inv line

    select *

    from [COMPANY$Job Ledger Entry Cmfrt]

    inner join  [COMPANY$Purch_ Inv_ Line]

    on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]

    and [COMPANY$Job Ledger Entry Cmfrt].[No_] = [COMPANY$Purch_ Inv_ Line].[No_] //try to add this line?

    and [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = [COMPANY$Purch_ Inv_ Line].[Job No_] //try to add this line?

    where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'

  • Suggested answer
    Gearoid Profile Picture
    Gearoid on at
    RE: Database link between job ledger entry and purch inv line

    Hi Jens,

    Thanks for providing a copy of the query. It looks like the JOIN clauses need some tweaking to get the results you are looking for based on the correct relationships between these tables. If you have a Page or existing Report in NAV which already includes these same tables you could use SQL Profiler to trace how the  NAV queries are generated when you access the relevant Page or report via the NAV client. You could then copy the way the JOINs are working on the queries from the trace file.  

    If that does not help you could open an Advisory ticket with Microsoft via your Solution Provider or Dynamics NAV Partner. 

    Hope that helps.

  • Jens1238 Profile Picture
    Jens1238 5 on at
    RE: Database link between job ledger entry and purch inv line

    Yes no problem,

    declare @JobNo_ as nvarchar(max)

    set @JobNo_ = 'P0002333'

    SELECT

     [COMPANY$Purchase Line].[Document No_] AS [COMPANY$Purchase Line Document No_] --- Inkoop order lijn, document nummer

     ,[COMPANY$Purchase Line].Quantity AS [COMPANY$Purchase Line Quantity] --- Inkoop order lijn, aantal

     ,[COMPANY$Purchase Line].[Unit Cost (LCY)] --- Inkoop order lijn , prijs per stuk

     ,[COMPANY$Purchase Line].[Job No_] --- Inkoop order lijn, projectnummer

     ,[COMPANY$Purchase Line].[Quantity Invoiced] --- Inkoop order lijn, gefactureerd aantal

     ,[COMPANY$Purchase Line].[Blanket Order No_] --- Inkoop order lijn, Externe referentie ???

     ,[COMPANY$Purchase Line].[Budget Account Code] --- Inkoop order lijn, account code NVT

     ,[COMPANY$Purchase Line].[Subcontract_ Contract No_] --- Inkoop order lijn, subcontract nr NVT

     ,[COMPANY$Purch_ Rcpt_ Line].[Document No_] AS [COMPANY$Purch_ Rcpt_ Line Document No_] --- Inkoop ontvanst lijn, document nr

     ,[COMPANY$Purch_ Rcpt_ Line].[Line No_] --- Inkoop ontvangst lijn, lijn nr

     ,[COMPANY$Purchase Line].[Buy-from Vendor No_] --- Inkoop order lijn, verkoper nr

     ,[COMPANY$Purchase Line].[Quantity Received] --- Inkoop order lijn, aantal ontvangen

     ,[COMPANY$Purch_ Rcpt_ Line].Quantity AS [COMPANY$Purch_ Rcpt_ Line Quantity] --- inkoop ontvangst lijn, aantal

     ,[COMPANY$Purch_ Inv_ Line].[Document No_] AS [COMPANY$Purch_ Inv_ Line Document No_] --- inkoop factuur lijn, documentnr

     ,[COMPANY$Purch_ Inv_ Line].Amount --- inkoop factuur lijn, bedrag

     ,[COMPANY$Purch_ Inv_ Header].[Posting Date] --- inkoop factuur header, datum

     ,[COMPANY$Purch_ Inv_ Header].[Vendor Invoice No_] --- inkoop factuur header, leveranciers nummer

     ,[COMPANY$Vendor].Name --- Leveranciers, naam leverancier

    FROM

     [COMPANY$Purchase Line] --- inkoop order lijn

     LEFT OUTER JOIN [COMPANY$Purch_ Rcpt_ Line] --- left join met inkoop ontvangst lijn

    --- documentnr van inkoop order lijn moet matchen met het order nr van de inkoop ontvangst lijn AND lijnnr van inkoop order lijn moet matchen met inkoop ontvangst lijn order lijn nr

       ON [COMPANY$Purchase Line].[Document No_] = [COMPANY$Purch_ Rcpt_ Line].[Order No_] AND [COMPANY$Purchase Line].[Line No_] = [COMPANY$Purch_ Rcpt_ Line].[Order Line No_]

     LEFT OUTER JOIN [COMPANY$Purch_ Inv_ Line] --- left join met inkoop factuur lijn

    --- documentnr van inkoop ontvangst lijn matchen met inkoop factuur lijn nr AND lijn nr van inkoop ontvangst lijn matchen met inkoop ontvangst lijn nr

       ON [COMPANY$Purch_ Rcpt_ Line].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Receipt No_] AND [COMPANY$Purch_ Rcpt_ Line].[Line No_] = [COMPANY$Purch_ Inv_ Line].[Receipt Line No_]

     LEFT OUTER JOIN [COMPANY$Purch_ Inv_ Header] --- left join met inkoop factuur header

       --- document nr van inkoop factuur lijn matchen met nr van inkoop factuur header

       ON [COMPANY$Purch_ Inv_ Line].[Document No_] = [COMPANY$Purch_ Inv_ Header].No_

     INNER JOIN [COMPANY$Vendor] --- inner join met alle leveranciers

    --- inkoop order lijn leveranciers nr matchen met leveranciers nr

       ON [COMPANY$Purchase Line].[Buy-from Vendor No_] = [COMPANY$Vendor].No_

    WHERE

     [COMPANY$Purchase Line].[Document No_] LIKE N'BB%'

     AND [COMPANY$Purchase Line].[Job No_] LIKE @JobNo_

    above is the query now without the job ledger entry table. But on the job ledger entry table there is the one record that should also be included in my query.

    Now i started my new query:

    select *

    from [COMPANY$Job Ledger Entry Cmfrt]

    inner join  [COMPANY$Purch_ Inv_ Line]

    on [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = [COMPANY$Purch_ Inv_ Line].[Document No_]

    where [COMPANY$Job Ledger Entry Cmfrt].[Job No_] = 'P0002333' and [COMPANY$Job Ledger Entry Cmfrt].[Document No_] = 'IFG1903-00273'

    I started my query to do thing the other way around. First get job ledger entry , then join it with purch inv line then purch inv header , purch rcpt line and at last the purchase line table.

    But like you can see the other way around isn't working either. What is normal because on the 2 tables i match on things dat excist in every record. So there is no unique link between them

  • Gearoid Profile Picture
    Gearoid on at
    RE: Database link between job ledger entry and purch inv line

    Hi Jens,

    Can you include a copy of your current query to help with the analysis?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,554 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,588 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans