Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Sales/Purchase Order H...
Microsoft Dynamics AX (Archived)

Sales/Purchase Order Historical Data

(0) ShareShare
ReportReport
Posted on by AdnDalhi 316

Hi all,

I have a business requirement, i need to get 4 years of customer sales order history, and 2 years of vendor purchase orders history as well from AX2012.

Also to retrieve all the items, Vendors, Invent Locations related informations, of more than 10 entities.

My " Technico-Functional " question here is how can i procede to get the Data, what is the best or the most effective way to get data,

Is creating SQL Views (sql queries), and attack directly the database a good choice ?  or use DMF ? or there is another scenarios ( which one the best, rapide and effective).

I will be grateful for any instruction or information.

Thanks a lot.

*This post is locked for comments

  • Suggested answer
    Ajit Profile Picture
    Ajit 8,755 on at
    RE: Sales/Purchase Order Historical Data

    If you need just basic details of sales orders and purchase orders, you can go with header tables which Sukrut mentioned and along with that salesLine and PurchLine.  But if you need like all single details then you would have join multiple tables like DirPartyTable and logisticsPostalAddress to get the address and Dimension tables to get the dimensions.

  • Suggested answer
    Sukrut Parab Profile Picture
    Sukrut Parab 71,647 Moderator on at
    RE: Sales/Purchase Order Historical Data

    Main core tables are salesTable and PurchTable along with salesLine and Purchline . However there are number of tables involved when you talk about sales orders and purchase orders. You can take a look at  datasources of the form and find out all the related tables you need.  

  • AdnDalhi Profile Picture
    AdnDalhi 316 on at
    RE: Sales/Purchase Order Historical Data

    I would like to know which AX Tables i can query to get historial data( 3 years) of Purchase Order and Sales Order, and which field i must make the criteria of date.

  • Suggested answer
    Ajit Profile Picture
    Ajit 8,755 on at
    RE: Sales/Purchase Order Historical Data

    Looks like you have one time request so I would say, first verify the count and then finalize the approach. Since it is one time thing you can get data directly from SQL and if they might have same kind of requirement in future then think of setting up the entity store and all.

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Sales/Purchase Order Historical Data

    You can use entity store blogs.msdn.microsoft.com/.../introducing-entity-store-for-dynamics-ax-2012-r3 or build something similar. It runs over DMF entities because they use SSIS under the hood that is quite efficient and allow you to add business logic using x++ only without going deep into SQL. Another option could be SSAS cubes docs.microsoft.com/.../configure-analysis-services or direct access to DB. It depends what technology you prefer and familiar with.

  • Sukrut Parab Profile Picture
    Sukrut Parab 71,647 Moderator on at
    RE: Sales/Purchase Order Historical Data

    Its up to you, You can either DMF  and create entities to export data,  if it is needed  on ongoing basis and make it as a part of application using DMF . As you have 2012 and access to SQL , creating SQL queries   is also an option and going to be quicker.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans