• Replies 4 replies
  • Subscribers 41 subscribers
  • Views 11905 views
  • Users 0 members are here
Options
Related

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

Maddy
Offline

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.

Parents
  • Offline

    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
Reply
  • Offline

    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
Children
No Data