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

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Purchase Archive filte...
Microsoft Dynamics NAV (Archived)

Purchase Archive filter to not still open

(0) ShareShare
ReportReport
Posted on by J_Tucker 220

I need to report on all Purchase Orders issued in the last week, whether they are still open or not.  I'm using Jet Reports and getting the Purchase Line records for open PO's is easy.  The Purchase Line Archive records are tricky because there are data lines that are duplicate of those from open PO's (meaning the PO lines had been posted as received but not posted to invoice yet, therefore the data still shows up in the "Purchase Line" table).

What field can I filter by in the "Purchase Line Archive" table to exclude data that would also still be in the open PO's data?  Maybe "Version No."?

Or would it be better to filter the open PO's ("Purchase Line" table) to only not yet received orders (Quantity Received = 0) , then show all archived data?

Thanks,

Jeff

NAV 2009 SP1 (6.0.29626.0)

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Why use the archive table?  Why no use Purch. Inv. Header & Purch. Inv. Line - tables 122 & 123?

    or even better the Item Ledger Entry Table

  • J_Tucker Profile Picture
    J_Tucker 220 on at

    Thanks for the response.  I need to show purchases issued by the order date (the earliest point in the process that is documented), and this is for lot tracked inventory items that might not be received for weeks.  Isn't Purch. Inv. Line/Header based on when it's posted to invoice?  Order Date is not in those tables.

    I decided to filter this way to show all purchases for the week but without duplication:

    Purchase Line by 'Qty to Receive' >0

    Purchase Line Archive by 'Quantity Received' >0

    Now I just need to figure out how to show only the most recent Version No. in Purchase Line Archive (to again eliminate duplicates, because we post to receive when the truck arrives, then post to invoice later when the invoice arrives).

  • Community Member Profile Picture
    Community Member on at

    [quote user="J_Tucker"]

     Isn't Purch. Inv. Line/Header based on when it's posted to invoice?  Order Date is not in those tables.

    [/quote]

    Did you look?
    Purch inv header has:
    Order Date (Field19)
    Posting Date (Field20)
    Order No. (Field44)

    If you look at the table using"Design" you can see all the fields that those tables offer.

    If you would like to base your report on anything received then you could use the Purch Rcpt. Header

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

News and Announcements

Season of Giving Solutions is Here!

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)

#1
HoangNam Profile Picture

HoangNam 7

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans