DEVELOPERS FORUM - Query Where Used within Item Type Relationship

jsnosal - Thursday, August 29, 2013 2:43 PM:

Our MCO process requires that we show all the affected items based on the parts that are affected. I'm wondering if the query I have below is possible? I would like to use this as a reverse grid look up within a tab. With the testing I have done so far I can't get it to work the way I want it to. Is there a different way I go about doing this? The main thing is I need to be able to export this as a report. Thank you for your help its greatly appreciated. 

 <Item type="Simple MCO" action="get" id="{@id}">

         <Relationships>

              <Item action="get" type="Simple MCO Part">

                     <related_id>

                             <Item type="Part" action="getItemWhereUsed"></Item>

                     </related_id>

               </Item>

           </Relationships>

</Item>

Eric Domke - Thursday, September 26, 2013 8:32 AM:

Would something like the following work?

<Item type="Part" action="get">

  <id condition="in">

    (SELECT pb.SOURCE_ID

    from innovator.PART_BOM pb

    INNER join innovator.SIMPLE_MCO_PART smp

    on smp.RELATED_ID = pb.RELATED_ID

    where smp.SOURCE_ID = {@id})

  </id>

</Item>

zahar - Thursday, September 26, 2013 8:41 AM:

you need to move you sql to "where" atribute of Item

something like this:

<Item type="Part" action="get" where="[Part].id in  (SELECT pb.SOURCE_ID

    from [PART_BOM]  INNER join [SIMPLE_MCO_PART]

    on [SIMPLE_MCO_PART].RELATED_ID = [PART_BOM] .RELATED_ID

    where  [SIMPLE_MCO_PART].SOURCE_ID = {@id})" />

Eric Domke - Sunday, September 29, 2013 10:58 PM:

I have used both AML structures (SQL in the where attribute and SQL as an "in" condition) with equal success.  In fact, of the two, I have probably used SQL as an "in" condition more frequently, but for no good reason.

AbhishekSrivastava - Friday, March 18, 2016 2:41 AM:

hello friends,

I want to populate value from sql db to Aras. So the scenario shown below:

I have a vendor Item Type and having 3 property,

1. Vendor Category, 2. Vendor Name , 3. NDA Status

I have Test Item Type and having again 3 property:

1. Vendor Category, 2. Vendor Name , 3. NDA Status

but when i select Vendor Category then Vendor name Sort and i need to select as per this NDA Status Shown and Auto selected.

So Is that Possible if yes then please share you experts solution.

Thanks In Advance

Abhishek Srivastava