Forum Discussion

AngelaIp's avatar
AngelaIp
Ideator I
3 years ago
Solved

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

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

Methods
  • Gopikrishnan's avatar
    Gopikrishnan
    3 years ago

    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.

8 Replies

  • AngelaIp's avatar
    AngelaIp
    Ideator I
    3 years ago

    Hi community,

    I maybe found a dumb solution to get the missing duplicate data. I didn´t found an elegant way to reference from the duplicates to the original items.

    But if we fetch the relationships after executing the "GetItemRepeatConfig" query, we seem to get all the data:

    .....
    partItem = partItem.apply();
    partItem.fetchRelationships("Part BOM","keyed_name,sort_order,related_id,source_id");

    It´s not the best solution regarding performance, but at least I now should be able to build a proper tree.

    Anyway, if anyone knows a better solution, let me know!

    Angela

    • Harald_Korneliussen's avatar
      Harald_Korneliussen
      Ideator I
      6 months ago

      Hello Angela,

      Did you ever find a better way to do this? I'm also making trees, which can't be built incrementally (we need to sum up things from the leaves). Fetching relationships on every level is really slow :/

      • AngelaIp's avatar
        AngelaIp
        Ideator I
        6 months ago

        Good question! I don´t even use my own fetch solution. 

        Now that I think about it, I believe I have a much better idea: Why don’t we just repair the structure?

        We use the regular getItemRepeatQuery as-is and than call a function that search for identical nodes and rebuilds the sub-data.

    • Gopikrishnan's avatar
      Gopikrishnan
      Ideator I
      3 years ago

      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.

      • AngelaIp's avatar
        AngelaIp
        Ideator I
        3 years ago

        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!