D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

AX 2012 locating products by item

  • 1.  AX 2012 locating products by item

    Posted Feb 05, 2020 07:04 AM
    Hi,

    I'm trying to find a report or an advanced query that can help me locate all products that contain a specific item. The result I'm after is a list of item numbers where the BOM contains a specific item. It is crucial for me to get the match for the query with item no:s rather than BOM no:s. EG I need to locate all our produced items that contain a specific type of bottle by building a query based on the item no of the bottle.

    I'm using AX 2012 R3 CU8.

    Hoping somebody has some insight to share.

    Regards

    ------------------------------
    Ernesto Cortez
    Apotek Produktion & Laboratorier AB
    Stockholm
    ------------------------------
    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: AX 2012 locating products by item

    SILVER CONTRIBUTOR
    Posted Feb 05, 2020 10:17 AM
    Ernesto,

    Have you tried the Where-used report under Inventory management module?

    Inventory management/Reports/Bill of materials/Where-used.

    ------------------------------
    Trent Aschliman
    McWane, Inc.
    Birmingham AL
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted Feb 05, 2020 05:15 PM
    Trent nailed it. This is exactly what Where used will show you. There's the full report version as he mentioned and then you can run the query from a single item on the Released products form > Engineer tab in the action ribbon at the top of them item (may still be in Inventory in AX2012 rather than Engineer as in D365). Same place where the BOM and BOM versions buttons are for the Produced items, you should see a Where-used button that shows you all BOMs containing that product and if they are active or not. There's also tools to mass update those BOM lines and replace with a new item number based on this.

    ------------------------------
    Andrew Lencsak
    Solution Delivery Manager
    Arbela Technologies
    Irvine CA
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: AX 2012 locating products by item

    Posted Feb 06, 2020 02:18 AM
    Edited by Ernesto Cortez Feb 06, 2020 02:35 AM
    Thank you Andrew and Trent but this only helps me to a certain degree. Maybe I wasn't clear enough. I'm aware of both options of using Where used with a specific item to retrieve a list of BOM versions but what I'm really after is a similar functionality for item no:s rather than BOM versions. I've attached an image to try to demonstrate my issue. When using Where used with an item no I can't get the search hit to present me a list of item no:s containing the searched item. The same goes for the report in Inventory management, I can't get the report to present me with a list of item no:s. Maybe it's down to lack of know how but I just can't find a list that fits my purpose which is being able to get a visual overview of produced items containing a specifc raw material or packing material.
    List of BOM versions


    ------------------------------
    Ernesto Cortez
    Apotek Produktion & Laboratorier AB
    Stockholm
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted Feb 06, 2020 07:37 AM
    Hi Ernesto,
    I think first it's important to note that an Item belongs to a BOM, not a BOM version. What you show in your screenshot is the connection to the BOM the part belongs to, not necessarily the item number it goes into. This is because many items can have the same BOM. If you want a better view of this, you'll probably need to extract and do a join to present the information you want to see using both the BOM versions and BOM lines.
    For example, if I extracted all BOM lines from AX2012 or D365, I would know what BOM they belong to, but that BOM Id number and description could be arbitrary, so I'd still have to pull the BOM versions as well to see what it is linked to. You're not going to the this information from where-used since it breaks up the data into two tables, you'll have to most likely build a custom report using both tables. I've had to do vlookups to the BOM version table often when doing data migration for D365 to help users populate the BOM line template because of this same issue

    ------------------------------
    Andrew Lencsak
    Solution Delivery Manager
    Arbela Technologies
    Irvine CA
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 6.  RE: AX 2012 locating products by item

    Posted Feb 06, 2020 09:07 AM
    Thank you Andrew!
    I'll look into creating an Excel sheet where I can combine the information needed to compile my list.

    ------------------------------
    Ernesto Cortez
    Apotek Produktion & Laboratorier AB
    Stockholm
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 7.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted Feb 06, 2020 07:08 PM
    ​How about this:
    • Open advanced filter from the released products form
    • Add BOMVersion to the query, linked to the Items
    • Add BOMTable to the query, linked to BOMVersion
    • Add BOM to the query, linked to BOMTable
    • Add this line to the range: BOM Item number = your desired component ID

    The resulting list of released products should be items that included your desired component in their BOM.

    ------------------------------
    Kevin McLean
    Strategic Solutions NW, LLC
    Beaverton OR
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 8.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted Feb 06, 2020 07:09 PM
    ​As an afterthought: if this works, make sure to save it.  You won't want to build it more than once.

    ------------------------------
    Kevin McLean
    Strategic Solutions NW, LLC
    Beaverton OR
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 9.  RE: AX 2012 locating products by item

    Posted Feb 07, 2020 09:38 AM
    Thank you for the tip but I've already tried doing this exact advanced search with no luck, and many other for that matter. I get no search results when performing the query.

    ------------------------------
    Ernesto Cortez
    Apotek Produktion & Laboratorier AB
    Stockholm
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 10.  RE: AX 2012 locating products by item

    SILVER CONTRIBUTOR
    Posted Feb 07, 2020 01:16 PM
    Ernesto,

    Is your company using any BI software or are you relying on excel exports or base AX reports?

    I'm with Drew, I think you need a customized report for the data you are trying to put together.

    ------------------------------
    Trent Aschliman
    McWane, Inc.
    Birmingham AL
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 11.  RE: AX 2012 locating products by item

    Posted Feb 10, 2020 04:38 AM
    Yes we are using BI software. But we try to use AX reports as far as possible to fulfill our reporting needs. As far as I'm aware we mostly use BI for financial data and reports. I did however solve my issue using Excel which is sufficient for the task at hand.

    ------------------------------
    Ernesto Cortez
    Apotek Produktion & Laboratorier AB
    Stockholm
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 12.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted Feb 07, 2020 02:27 PM
    I tested in AX 2012 and was able to get the display limited to the correct items.  I could not get those results in D365.  My AX 2012 environment is running CU13.

    ------------------------------
    Kevin McLean
    Strategic Solutions NW, LLC
    Beaverton OR
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 13.  RE: AX 2012 locating products by item

    Posted Feb 10, 2020 04:49 AM
    We are running a CU several updates behind which probably might be one of the reasons I can't get it to work.
    I did solve my issue. By extracting active BOMs with where used in the released products table and adding all active BOMs from BOMVersion in the AOT I was able to consolidate the data in Excel using a VLOOKUP matching the BOM no's in both tables. My solution will suffice for now.

    Thank you for your helpful input however. We are looking in to maybe upgrading our CU version and your suggested solution would be the easiest way to get the result.

    ------------------------------
    Ernesto Cortez
    Apotek Produktion & Laboratorier AB
    Stockholm
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 14.  RE: AX 2012 locating products by item

    SILVER CONTRIBUTOR
    Posted Feb 10, 2020 09:12 AM
    Glad to hear you found something that works for you and thanks for sharing.

    ------------------------------
    Trent Aschliman
    McWane, Inc.
    Birmingham AL
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


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