How to get items with all of its revisions with maximum generation

オフライン

Hi,

I am trying to get part all revision with maximum generation using IOM:

below are the entries:

Item Number            Generation     Revision

Testnumber1                 1                  00
Testnumber1                 2                  00
Testnumber1                 3                  00
Testnumber1                 4                  00
Testnumber1                 5                  00
Testnumber1                 6                  00
Testnumber1                 7                  00
Testnumber1                 8                  01
Testnumber1                 9                  01
Testnumber1                 10                02

so from above entries I want:

Item Number            Generation     Revision

Testnumber1                 7                 00
Testnumber1                 9                 01
Testnumber1                10                02

I got this result with below SQL:

select item_number, MAX(generation) as generation, major_rev
from innovator.PART
group by item_number, major_rev order by generation

but how to do this with IOM?

Regards,

Maddy.

  • Hi Maddy

    I don't find a easier way like SQL but if you still want to implement using IOM, you can write your own custom logic to get this result. I used below method and able to get the expected result. 

    Innovator inn = this.getInnovator();
    var itemNums = "Testnumber1,Testnumber2";
    HashSet<string> revisionSet = new HashSet<string>();
    var partInfo = new Dictionary<string, Tuple<string, string, string>>();
    string[] itemNum = itemNums.Split(',');
    for(int i = 0; i < itemNum.Length; i++)
    {
    revisionSet.Clear();
    Item partRevision = inn.newItem("Part", "get");
    partRevision.setAttribute("select", "major_rev");
    partRevision.setProperty("item_number", itemNum[i]);
    partRevision.setProperty("generation", "*");
    partRevision.setPropertyCondition("generation", "like");
    partRevision = partRevision.apply();
    if (!partRevision.isError())
    {
    for (int j = 0; j < partRevision.getItemCount(); j++)
    {
    var partMajorRev = partRevision.getItemByIndex(j).getProperty("major_rev");
    revisionSet.Add(partMajorRev);
    }
    foreach(var revs in revisionSet)
    {
    Item partLatestRevision = inn.newItem("Part", "get");
    partLatestRevision.setAttribute("select", "item_number,major_rev,id,generation");
    partLatestRevision.setAttribute("orderBy", "generation DESC");
    partLatestRevision.setAttribute("maxRecords", "1");
    partLatestRevision.setProperty("item_number", itemNum[i]);
    partLatestRevision.setProperty("major_rev",revs);
    partLatestRevision.setProperty("generation", "*");
    partLatestRevision.setPropertyCondition("generation", "like");
    partLatestRevision = partLatestRevision.apply();
    if (!partLatestRevision.isError())
    {
    var itemNumber = partLatestRevision.getItemByIndex(0).getProperty("item_number");
    var itemId = partLatestRevision.getItemByIndex(0).getProperty("id");
    var itemMajorRev = partLatestRevision.getItemByIndex(0).getProperty("major_rev");
    var itemGeneration = partLatestRevision.getItemByIndex(0).getProperty("generation");
    partInfo.Add(itemId, new Tuple<string,string, string>(itemNumber, itemMajorRev, itemGeneration));
    }
    }
    }
    }
    return this;
    Thank You
    Gopikrishnan R
  • Hi Maddy,

    Gopikrishnan is correct that this sort of grouping isn't available OOTB. In order to get this exact functionality, you would need to write some custom logic. However, I want to propose an alternative query that is possible through AML alone.

    Using the standard Part item as an example, the major_rev is typically incremented when a Released Part is modified. Following this logic, the item with the greatest generation per major_rev will be in the Released state. By querying on either the state or the is_released property, we can effectively accomplish the query you're looking for.

    <AML>
      <Item type="Part" action="get" select="item_number, generation, major_rev">
        <is_released>1</is_released>
        <!-- By default, non-current items aren't returned by an AML query. We can get them to appear by including generation in the criteria like this -->
        <generation condition="gt">0</generation>
      </Item>
    </AML>

    What you might notice from the query above is that it may not return the most recent Part if that part isn't also in the Released state. We can account for this by chaining another query immediately after this one like so:

    <AML>
      <Item type="Part" action="get" select="item_number,generation,major_rev">
        <generation condition="gt">1</generation>
        <is_released>1</is_released>
      </Item>
      <!-- Because this query doesn't include the generation in it's criteria, it will return only the most recent Part -->
      <Item type="Part" action="get" select="item_number,generation,major_rev">
        <!-- Including this criteria will prevent the same item from appearing twice if the most recent Part is also Released -->
        <is_released>0</is_released>
      </Item>
    </AML>

    This chained query should return all of the items you're expecting. This exact approach assumes that you're using the Part ItemType; however, you can use a similar approach as long as your versioning logic includes some condition for when a new major_rev is created. For example, you may need to use something like <state>Released State</state> instead of <is_released>1</is_released> if you're using a custom ItemType that doesn't have the is_relased property.

    Chris

    Christopher Gillis

    Aras Labs Software Engineer

  • Thanks Christopher. This looks to be a simple. However if the latest generation is released, then the combined query will fail as latest revision is_released = '1'. so, second query might return no records.

    <AML>
      <Item type="Part" action="get" select="item_number,generation,major_rev">
      <item_number>TESTPART1</item_number>
      <generation condition="gt">1</generation>
      <is_released>1</is_released>
      </Item>
      <Item type="Part" action="get" select="item_number,generation,major_rev">
      <item_number>TESTPART1</item_number>
      <is_released>0</is_released>
      </Item>
    </AML>

  • オフライン in reply to Gopikrishnan

    Thanks Gopikrishnan and Chris for helping on this, I will try with this approach,