• State Verified Answer
  • Replies 4 replies
  • Subscribers 12 subscribers
  • Views 8194 views
  • Users 0 members are here
Options
Related

GetItemRepeatConfig removes child items that are used multiple times. How to fix?

AngelaIp

Hi community,

I use a classical "GetItemRepeatConfig" query to get a Part BOM over multiple levels. 

id="xyz";
Item partItem = inn.newItem("Part","GetItemRepeatConfig");
partItem.setAttribute("select","id,classification,keyed_name,item_number");
partItem.setID(id); // <- target Part

Item bomItem = partItem.createRelationship("Part BOM","get");
bomItem.setAttribute("select","related_id(keyed_name),sort_order");
bomItem.setAttribute("repeatProp","related_id");
bomItem.setAttribute("repeatTimes","10");

partItem = partItem.apply();

When taking a look at the AML result, I discovered that Parts that are used on multiple levels inside a BOM (e.g. common items like screws) are not returned as complete items. The full related item is available only ONCE inside the AML result, but not for every BOM position that use that Part. 

Here is how the result looks like: 

The image shows three relationship BOM positions. Position 1 and 3 contain the full Part <Item> for the "related_id". Position 2 does not contain the "Item" cause it appeared already present in a previous position.

Problem: Without the date of the related_id/Item, I am not able to render the AML data. In my current use case I want to restructure the AML result in a way that it will also include depth/level of each BOM position. This basically works well, but as soon a duplicate entry appears, the data for the child item is missing:

Here is the current result that I get. I used a placeholder "Data missing" each time the related item is missing:

I discovered that this one is actually a know bug/behavior since over 10 years:

https://community.aras.com/f/archive/2097/developers-forum---getitemrepeatconfig-fix

The solution in the post is to not use GetItemRepeatConfig, but to manually create a query that includes all BOM levels.

Sure, this solution will work. But after 10 years, is this really the solution of choice? My AML query shall also contain other relationships, which would make the query even bigger when we hardcode any level manually. 

Does anyone now a better solution for this one or how to modify the GetItemRepeatConfig query so it returns all data?

Thanks for any help!

Angela

Parents Reply Children
  • +1 Offline in reply to AngelaIp

    I was also in a similar situation to expand the related items but couldn't able to make it using GetItemRepeatConfig when the item repeated in the BOM. I end up writing a SQL stored procedure to get the max level of a BOM and constructed an AML query to fetch the result. 

    Would like to hear from ARAS if there is any better solution for this issue or status of the bug.

    SQL Query

    CREATE PROCEDURE [getBOMLevels] @source_id NVARCHAR(MAX)
    AS
    BEGIN
    WITH RecursiveBOM(SOURCE_ID, RELATED_ID, indent_level) AS
    (
    SELECT pb.SOURCE_ID, pb.RELATED_ID, 0 FROM innovator.PART_BOM pb WHERE SOURCE_ID = @source_id
    UNION ALL
    SELECT pb1.SOURCE_ID, pb1.RELATED_ID, indent_level +1 FROM innovator.PART_BOM pb1
    INNER JOIN RecursiveBOM rb ON rb.related_id = pb1.SOURCE_ID
    )
    SELECT MAX(indent_level) AS BOM_Level FROM RecursiveBOM rb
    END

    Using this, I constructed the loop of BOM structure AML query.

  • 0 in reply to Gopikrishnan

    Hi Gopi,

    thanks for your answer! Your variant with SQL CTE is pretty much the same that I used in the past to solve this issue. But I always wanted to find a AML solution for this one.

    I discovered, that Aras itself sometimes renders level structures solely based on the result of an GetItemRepeatConfig query, e.g. Office Connector uses it. So I thought I finally found a solution for easy depth/level calculation. But in their use cases the items seemed to be unique most of the time..

    Aras definitely made a bug fix of the "GetItemRepeatConfig" function. But it seems that the current behavior is the result of the bug fix. I found this release note:

    https://www.aras.com/-/media/files/documentation/release-notes/en/9-3-0/aras-innovator-930-service-pack-7.ashx

    The document contains this image that shows the structure of the AML result before and after the patch:

    Full -> That´s what I want to have
    SP6 -> Shows the bug
    SP7 -> Shows the structure after bug fix. The full node is only returned once. Each other nodes just contain references. This is 1:1 the AML result that I currently get. 

    I wonder if there is any option for the function the get the "full" result.

    This is really strange. Somehow there has to be a solution of how to deal with the referenced items. I cannot believe that "manually made monster AML query" and SQL CTE are the only solutions. 

    If anyone knows more, please let use know!