Reverse Lookup

I'm using "../Solutions/PLM/scripts/ReverseItemsGrid.html" on a version ItemType I have a itemType "A" that is in Rev-A Gen-1. If I open ItemType "B" and add relationship from (ItemType-A, Rev-A, Gen-1) the "ReverseItemsGrid.html" in itemType "A" works fine, but if I make changes to ItemType "A" and change its Rev to "B", I lose the "ReverseItemsGrid.html" because it still linked to "Rev-A,Gen-1". How can I get ReverseItemsGrid.html to point to "Is_current". I think now it's pointing to the ID.
  • Hi Dominic, There are a few different ways to accomplish this.
    1. Change the behavior of the relationship to "Float" rather than "Fixed"
      • This will force the relationships to always point to the newest version of an item
      • NOTE: this will also change the data you are storing which may not be desired
    2. Manually edit the reverse items grid
      • You can find this under \Innovator\Client\Solutions\PLM\scripts\ReverseItemsGrid.html in your codetree
      • You would want to update the query near line 218 to use the config_id rather than the id
      • NOTE: Rather than editing the existing ReverseItemsGrid.html, I would recommend creating a copy of the file called "CurrentReverseItemsGrid.html" that you can make your changes to
    3. If you are using 11.0 SP10 or greater, use the new Query Builder/Tree Grid View functionality
    Chris
    Christopher Gillis Aras Labs Software Engineer
  • This may not be an exact answer to your questions, but I came across this question while attempting to solve the following issue (11.0): in ReverseItemsGrid (and PE_GetRelatedParts) items are being returned after a relationship has been deleted from the most recent generation of the part. This seems to be because the default SQL in PE_GetRelatedParts doesn't return only the most recent generation.  In order to return relationship ONLY on the most recent generation you can add another inner join to the sql ~line 158.  The reason is that the existing SQL only returns the most recent generation of MATCHING PARTS:

     String sqlQuery = @"
    SELECT item.id FROM {1} item
    INNER JOIN
    (
    SELECT item.CONFIG_ID, MAX(item.GENERATION) GENERATION FROM {1} item
    INNER JOIN {2} relationshipItem ON item.ID = relationshipItem.SOURCE_ID AND relationshipItem.RELATED_ID ='{0}'
    GROUP BY item.CONFIG_ID
    ) groupeditem ON groupeditem.CONFIG_ID = item.CONFIG_ID AND groupeditem.GENERATION = item.GENERATION
    /*NEW CLAUSE*/

    INNER JOIN (SELECT item.CONFIG_ID, MAX(item.GENERATION) GENERATION FROM {1} item 

    GROUP BY item.CONFIG_ID
    ) maxgen ON maxgen.CONFIG_ID = item.CONFIG_ID AND maxgen.GENERATION = item.GENERATION
    ";