AML where condition based on keyed name

Hi community,

I am trying to transform a where condition based on id into a where condition based on the keyed name or item_number

Outside a where condition it would be

< source _id>

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

<keyed_name>PartB</keyed_name>

</Item>

</ source _id >

I tried something like :

where="[Part BOM]. source_id='qsdmlsdkfsd51651681q3s5dq3s5d1'"

into

where="[Part BOM]. source_id.item_number='PartB'"

Thanks for the help !

Best regards,

Hadrien

 

  • Hi Hadrien,

    could you elaborate as to why you want to do this? I believe this would require something like

    where="PART_BOM.SOURCE_ID in (SELECT id FROM innovator.PART WHERE ITEM_NUMBER = 'PartB')"

    but you cannot simply include a "SELECT" clause in the where clause, at least if you are using 11SP9 or later (see here or here for details on how to incorporate that).
    It would be advisable to keep the query in AML form if possible, since that way we make sure all Aras security and permission features don't get circumvented (using SQL does exactly that).

    Hope this helps,

    C

  • Hi,

    First of all thanks for your reply I will try it.

    Some explaination :

    We are doing a connector that save the state of items managed in an external tool into Aras and run in batch every hour. Every change creates a new generation of the Aras objects.

    We want to merge a set of item let say part and there relationship partbom.

    With the merge on the item part, no problem, if the item do not exist it is created, if not a new generation is created.

    We would like to do the same on the relationship : if a relationship between PartA and PartB already exist apply changes (example quantity) if it does not exist create it...

    ==> Idealy we want to apply all changes to all items and there partbom in 1 AML for several items and there relationships.

    One workaround we are thinking is apply a uniq key on both related_id and source_id and apply all the relationship separeted with a loop... 

    Or to erase all relationship to every new generation before importing the relationships...

    I hope it is clearer.

    Hadrien

  • Hi,

    thanks for the clarification, I think I understand your issue. You want to "merge" relationships, ideally inside the AML of a Part you are merging. Personally, I try to avoid using the "merge" action as much as possible. I like to have control over what happens when and how, and a "create the item, but if it exists, edit it instead, but you won't really know either way" goes against that completely. Hence, I try to explicitly get the item and then either edit it (if it existed) or create a new one (if it didn't). That way I can also easily attach different logic to the two different events.
    In any case, if you want to use "merge", I would still strongly suggest to have at least two AML queries. One to edit or create the Parts and another one to edit or create the Part BOM relationships. If you do it this way, you will have the Aras IDs for PartA and PartB from your first AML query, so in your second one you can simply have a merge with a where clause like

    where="PART_BOM.SOURCE_ID='idOfPartA' AND PART_BOM.RELATED_ID='idOfPartB'"

    How do you run your import into Aras? If you are using C#, you might want to use the IOM.dll instead of AML, for more flexibility.
    If you absolutely must have everything in one AML, you need to look into the two links I posted in my previous reply to make a where clause like

    where="PART_BOM.SOURCE_ID in (SELECT id FROM innovator.PART WHERE ITEM_NUMBER = 'PartB')"

    work.

    Hope this helps,

    C

  • Thanks this helps,

    The connector is outside Aras for CI/CD easyness and apply AML to Aras by SOAP or Odata.

    Exchanging with you and my dev, we are thinking stop doing it in only one AML.

    Thus I think we will try this 

    Option A

    1) apply a merge to the items Part whithout knowing the ids

    ==> we get the ids in the answer

    2) we send a get in order to retrieve the child of the relationships ids

    3) we merge the relationships with the where="PART_BOM.SOURCE_ID='idOfPartA' AND PART_BOM.RELATED_ID='idOfPartB'"

    If it's not satisfying, option B to avoid merge everywhere...

    I let you know if it's works

    Hadrien

  • Hi, it seems to work fine with the option A... I will validate that with my dev ;)

    Thanks again

  • Awesome! Glad I could help.

    Cheers,

    C

  • I asked the support and get another possibility more simpler that I will implement when we will have time.

    It's more efficient and exaclty what we needed to do.

    If you apply it several time it won't add a new relationship each time :

    <AML>

        <Item action='edit' type='Part' where="item_number='PartTest'">

              <Relationships>

                    <Item type = 'Part BOM' action='create'>

                         <related_id>

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

                                   <item_number>TestGraphProject_A</item_number>

                              </Item>

                         </related_id>

                    </Item>

                    <Item type = 'Part BOM' action='create'>

                         <related_id>

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

                                   <item_number>TestGraphProject_B</item_number>

                              </Item>

                         </related_id>

                    </Item>

              </Relationships>

        </Item>

    </AML>

    Thanks @jeanpierreSolorzano from support :)