web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Purchase Order Table v...
Microsoft Dynamics NAV (Archived)

Purchase Order Table vs. Purchase Order Archive tables - Microsoft NAV 2009 Question

(1) ShareShare
ReportReport
Posted on by Community Member

Was looking to create a query that combines all Purchase Orders from both the Purchase Line and Purchase Line Archive tables in NAV 2009.  Wanted to make sure that i had the particulars on which fields to use in my WHERE conditions - 

For example - in the US$ Purchase Line table and the US$ Purchase Order Line Archive tables - do i only include those PO's such as follows:

Purchase Line by 'Qty to Receive' >0

Purchase Line Archive by 'Quantity Received' >0

Just need to understand the particular criteria for which PO's qualify for Archive so that i can avoid duplicates.  I've got the Version No_ problem solved.  Any insight would be greatly appreciated.

Thanks

*This post is locked for comments

I have the same question (0)
  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    The uniqueness is based on the below keys:

    Document Type, Document No., Doc. No. Occurrence, Version No., Line No.

    It's not based on Qty. to Receive or Quantity Received.

  • Community Member Profile Picture
    Community Member on at

    So if i were to write a query to combine both PO's out of the Purchase Line and Purchase Line Archive tables - what would the criteria be for each table in order to pull PO's from both tables into one table without duplication?  THANK!!

  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    Do you mean that your query must return only one Purchase Line for the respective Purchase Line in the Purchase Line table?

  • Community Member Profile Picture
    Community Member on at

    It means that i want to return purchase order lines from each table without duplication to create a single table with all purchase order lines - Closed, Open, etc....  Sorry for not being clear...

  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    You may use the UNION join (something like the below):-

    SELECT ArchLine.[Document Type],

    ArchLine.[Line No_],

         ArchLine.[Document No_],

         ArchLine.[Quantity],

         ArchLine.[Outstanding Quantity],

         ArchLine.[Amount]

     FROM [dbo].[CRONUS LS 80003 W1 Demo v20$Purchase Line Archive] ArchLine

     union

    SELECT PurchLine.[Document Type],

    PurchLine.[Line No_],

         PurchLine.[Document No_],

         PurchLine.[Quantity],

         PurchLine.[Outstanding Quantity],

         PurchLine.[Amount]

     FROM [dbo].[CRONUS LS 80003 W1 Demo v20$Purchase Line] PurchLine

  • Community Member Profile Picture
    Community Member on at

    A union query is good - i'm going to populate another table outside of NAV with the PO's from both the Purchase Line and Purchase Line Archive tables.  So what do you think the criteria would be to pull from each table without duplication?

  • mmv Profile Picture
    mmv 11,471 on at

    Hi,

    UNION query will give you unique records.

  • Community Member Profile Picture
    Community Member on at

    So are you saying that if i do a union query between my 2 tables that it will take care of the duplicates and remove them?

  • mmv Profile Picture
    mmv 11,471 on at

    Yes, it will remove the duplicates.  The duplicates are been assessed based on the values in the Query.  You may check and let me know.

  • Community Member Profile Picture
    Community Member on at

    Mahesh,

    Maybe i'm not doing this correctly but i'm still getting duplicates with the UNION query.  The query itself works but still seeing duplicates... might be missing a step.

    Regards,

    /Jack

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans