Forum Discussion

Nathan_H_'s avatar
Nathan_H_
Ideator I
5 years ago

AML = SQL Where (IN)

Good day all.  I have a JavaScript Method that uses the following line to create a list.

tdpList.loadAML('<Item type="s_Document" orderBy="id" action="get" where="([s_DOCUMENT].id IN(SELECT RELATED_ID FROM innovator.s_TDP_DOCUMENT WHERE (SOURCE_ID = \'' + this.getID() + '\')))"><generation condition="gt">0</generation></Item>');
tdpList = tdpList.apply();

I need to reuse this Method for another ItemType.  I have discovered that using Where and In causes an error because of a security update in 11 SP9.  Aras 11.0 - AML Security Settings 2017-04-28 mentions that you can create an exception to allow specific statements, but recommends changing the code to something else.  I remember reading that this exception is supposed to be phased out in 12.  How can I change this statement to correctly return the list I need?  

I can get the information by using a Get and Relationships, but it will not create the proper list.  I think this is do to the information returned since it includes the ItemType and Relationship ItemType information.  Thank you for any help you can provide.

AML

10 Replies

  • AngelaIp's avatar
    AngelaIp
    Ideator I
    5 years ago

    I assume it´s possible to rewrite this one, but I assume most people are not very good in reading SQL from others.

    Can you describe how your s_Document and s_TDP_Document ItemTypes are linked together?
    Are both of them independent ItemTypes or is a s_TDP_Document a relationship of your s_Document?
    Are additional ItemTypes involved?

    Which one of the two document itemTypes is linked to your "this.getID()"?

    Maybe you can provide a simple structure like

    Part       <--- I want to get this one
    --> Part AML 
    -----> Manufacturer Part <--- I start from here. this.getID() contains the id of this Item

    • Nathan_H_'s avatar
      Nathan_H_
      Ideator I
      5 years ago

      Angela,

      Good day.  I guess a bit more information would be helpful.  [emoticon:c28b2e4cc20f4ba28d1befdba6bed29c]  s_TDP_Document is a Relationship tab to s_TDP (Technical Data Package).  s_Document is a Poly Source of six different document ItemTypes (previous caretakers didn't use any of the OOTB ItemTypes).  The TDP allows the gathering of documents and then the Method will search for the most current version of Released and download them into a zip file. 

      • AngelaIp's avatar
        AngelaIp
        Ideator I
        5 years ago

        So your structure is the following:

        s_TDP

        --- s_TDP_Document 

        ------ s_Document / Polysources

        And you have the id of s_TDP? Than it´s comparable to a regular Part/Part AML/Manufacturer Part ("let´s look downwards") query:

        <AML>
          <Item type='Part' action='get' select='item_number' id='" + myID + "'>
            <Relationships>
             <Item type='Part AML' action='get' select='related_id(item_number)'/>
               <related_id>
                  <Item type='Manufacturer Part' select='item_number'>
                  </Item>
               </related_id>
             </Relationships>
           </Item>
        </AML>;

        Not sure about the PolyItem. For testing you can also start with the upper levels and then add the additional levels downwards until the query works.

        I personally wouldn´t use loadAML. It´s possible to rewrite the Method so it basically does the same, but without having one large string. Check out the structure shown in these blog articles:

        https://community.aras.com/b/english/posts/server-side-best-practices-part-3 

        https://community.aras.com/b/english/posts/server-side-best-practices-part-2