D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Extract vendor master data

  • 1.  Extract vendor master data

    TOP CONTRIBUTOR
    Posted Apr 30, 2019 08:45 PM
    Any suggestion for extracting Vendor master data in one Excel work sheet which includes everything which is in vendor form: like Vendor #, Name & Address, phone and email, Tax ID number, etc etc

    Thanks and regards​

    ------------------------------
    Nazim Lalji
    Dynamics AX Analyst

    ------------------------------


  • 2.  RE: Extract vendor master data

    MICROSOFT MVP
    Posted May 01, 2019 12:04 AM
    Hi Nazim,

    What exact version of Microsoft Dynamics are you using? As of version AX2012 R2 with CU7 and Dynamics 365 FO, you can use data management to export vendors using Data Import Export Framework/Data Management. In Dynamics 365 for Operations, you can also use the feature 'Open in Excel' and add more columns to get the desired list.

    ------------------------------
    kind regards,

    André Arnaud de Calavon
    Solution Architect, Microsoft MVP - Microsoft Dynamics Business Solutions
    ------------------------------



  • 3.  RE: Extract vendor master data

    TOP CONTRIBUTOR
    Posted May 01, 2019 12:29 AM
    HI Andre

    its AX 2012 R3 CU12

    Require to export Master data from AX to Excel

    thanks and regards

    ------------------------------
    Nazim Lalji
    Dynamics AX Analyst
    Nazim Lalji
    Los Angeles CA
    ------------------------------



  • 4.  RE: Extract vendor master data

    TOP CONTRIBUTOR
    Posted May 01, 2019 08:38 AM
    Edited by Colby Gallagher May 01, 2019 08:51 AM
    Hi Nazim,

    You can expose the AOT query "VendTableCube" to the Excel add-in at Org admin -> Setup -> Document Mgmt -> Document Data souces.

    When you connect to this query in Excel via Dynamics AX tab in ribbon ->  Add-Data -> Add-Data, right mouse click on the top level table -> Properties -> Show calculated fields, and you will also get all display methods for the tables in the query, one of which is Telephone.  The rest of your fields should be there.



    ------------------------------
    Colby Gallagher
    Manufacturing Systems Consultant
    Agility Business Solutions
    Brecksville OH
    ------------------------------



  • 5.  RE: Extract vendor master data

    TOP CONTRIBUTOR
    Posted May 01, 2019 09:11 AM
    Either using DIXF or a straight SQL query would probably be easiest, both have the ability to be exported in an Excel format.

    ------------------------------
    Alex Meyer
    Director of Dynamics AX/365 for Finance & Operations Development
    Fastpath
    Des Moines, IA
    ------------------------------



  • 6.  RE: Extract vendor master data

    Posted May 01, 2019 12:15 PM
    What do you mean by "everything"?  An SQL "Select * from VendTable" works in AX2009, It doesn't pick up contacts or  secondary addresses, or multiple state tax IDs, but it pulls all data that applies one-to-one with any vendor.

    ------------------------------
    Robert Menschel
    Lead Systems Engineer
    HD Supply Repair & Remodel
    Roseville CA
    ------------------------------



  • 7.  RE: Extract vendor master data

    TOP CONTRIBUTOR
    Posted May 02, 2019 11:44 AM
    This really depends on the version you are using, and how often you need to extract the data.
    You can open vendor details, Select all (Ctrl-A), Copy (Ctrl-C) and Paste into Excel. You will get duplicate columns of what is contained in the overview, plus a lot of stuff you are not interested in.
    In AX2012 or D365, look for a view, which will have pre-built links (joins) to related tables. This would be my first choice.
    If you want to write a sql query yourself, you will​ learn a lot about table structures - I've found this handy in navigating: https://alexdmeyer.com/ax2012erd/

    ------------------------------
    Mark Prouty
    Programmer / Analyst
    ANGI Energy Systems
    Janesville WI
    ------------------------------



  • 8.  RE: Extract vendor master data

    GOLD CONTRIBUTOR
    Posted May 02, 2019 04:55 PM
    Do you have any custom fields that could be a little more tricky?

    ------------------------------
    Crystal Ahrens
    Solution Architect
    Kodak Alaris Inc.
    Albion NY
    ------------------------------



  • 9.  RE: Extract vendor master data

    Posted May 03, 2019 03:31 PM
    Hi Nazim,

    if you give me all the fields you need, I can pull together an Atlas Query and upload it for you to use.
    Other thing is like someone else said. SQL select * on vendtable for most of the content

    rgds

    Carsten

    ------------------------------
    Carsten Schoenberg
    FITPRO SOLUTIONS
    Hamburg
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role