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 19 days ago
    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
    ------------------------------
    Conference-AXUG_200x200


  • 2.  RE: AX 2012 locating products by item

    SILVER CONTRIBUTOR
    Posted 19 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 3.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted 19 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 4.  RE: AX 2012 locating products by item

    Posted 18 days ago
    Edited by Ernesto Cortez 18 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 5.  RE: AX 2012 locating products by item

    TOP CONTRIBUTOR
    Posted 18 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 6.  RE: AX 2012 locating products by item

    Posted 18 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 7.  RE: AX 2012 locating products by item

    GOLD CONTRIBUTOR
    Posted 18 days ago
    ​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
    ------------------------------

    Conference-AXUG_200x200


  • 8.  RE: AX 2012 locating products by item

    GOLD CONTRIBUTOR
    Posted 18 days ago
    ​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
    ------------------------------

    Conference-AXUG_200x200


  • 9.  RE: AX 2012 locating products by item

    Posted 17 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 10.  RE: AX 2012 locating products by item

    SILVER CONTRIBUTOR
    Posted 17 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 11.  RE: AX 2012 locating products by item

    Posted 14 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 12.  RE: AX 2012 locating products by item

    GOLD CONTRIBUTOR
    Posted 17 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 13.  RE: AX 2012 locating products by item

    Posted 14 days ago
    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
    ------------------------------

    Conference-AXUG_200x200


  • 14.  RE: AX 2012 locating products by item

    SILVER CONTRIBUTOR
    Posted 14 days ago
    Glad to hear you found something that works for you and thanks for sharing.

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

    Conference-AXUG_200x200


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