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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Sales / Purchase Order...
Microsoft Dynamics AX (Archived)

Sales / Purchase Order for last 3 years

(0) ShareShare
ReportReport
Posted on by AdnDalhi 336

Hello, i have a technical question about getting the historical data of last 3 years of Sales Orders and Purchase Orders,

I need to get all the sales order and their lines for the last  3 years by using a SQL Query.

Thanks

*This post is locked for comments

I have the same question (0)
  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Adax,

    For what purpose do you need those data?

    Would be great if you could let us know.

    Many thanks and best regards,

    Ludwig

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi Ludwig,

    I need them for a global data extraction, i cannot used the DMF(DMFSalesTableEntity/ SalesLine), because of restricted access to AX Prod environement, so i'm obliged to make a global SQL query and get data from Prod SQL database

    Database.. the goal here is to get all SO of last 3 years starting from today.

    Thank

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi Ludwig,

    I'm actually using this SQL Query. to get the Header data

    USE AXPRODDB

    SELECT

    SALESTABLE.SALESID AS [Sales Order],

    SALESTABLE.SALESSTATUS AS [Sales Status],

    SALESTABLE.CREATEDDATETIME AS [Sales Order Creation date],

    SALESTABLE.CUSTACCOUNT AS [Customer Account],

    SALESTABLE.INVENTLOCATIONID AS [Location Code / Warehouse],

    SALESTABLE.SALESGROUP AS [Sales Group Rep],

    SALESTABLE.DATAAREAID AS [Company Id]

    FROM SALESTABLE

    WHERE SALESTABLE.CREATEDDATETIME > DATEADD(year,-3,GETDATE())

    ORDER BY SALESTABLE.CREATEDDATETIME

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 304,038 Super User 2026 Season 1 on at

    Hi Adax,

    It would be possible to use the Data Import Export Framework and possibly the Entity Store to get the data. What do you intend to do with this information? If it is about reporting, the possibly the entity store would be a good option; then you can use the data in e.g. PowerBI.

    You did share an SQL statement. Dows this work for you? Or do you have questions here?

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hello André, 

    Thanks for you suggestion, the goal was really to make a data extract of all PO/SO headers and lines (last 3years) divided, that's what i did, by creating a SQL Query, cause i cannot use DMF in my case,(business restrictions), so i was obliged to create a specific SQL Query and attack directly the prod database. SalesTable and SalesLines ( for SO headers and lines), PurchTable and PurchLine (for PO headers and lines).

    I liked your suggestion, i will try to suggest this idea to the business team. :-)

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 304,038 Super User 2026 Season 1 on at

    Hi Adnane,

    Do you still have an open question here?

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi André,

    My problem resolved thanks :)

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Basit Profile Picture

Basit 1

#1
GL-01081504-0 Profile Picture

GL-01081504-0 1

#1
Roya Profile Picture

Roya 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans