Need help with AML query to connect two independent ItemTypes via referencing item

Hi community,

I am working on an AML query but cannot find a proper solution. It contains a lot of custom ItemTypes, but technically the query would work similar like this:

START--------> Part 
---------------------> Part BOM
--------------------------> Part
<------------- MyCustomItemType (part_id) <-------- GOAL is to get properties of this referencing item

We start from Part and go downwards to get the complete BOM. Easy.

Now I have a second independent ItemType "MyCustomItemType" that also uses Part inside an item property. I want to include this custom ItemType to the query to fetch some additional data. In my case the end result shall combine BOM information with warehouse specific data. 

Problem: "MyCustomItemType" is not included in the "natural downward path" of Parts. And I want to avoid to include it at all costs. The data is federated and shall only be fetched on demand. So we have only a true reference from "MyCustomItemType" to Part, but no real reference form "Part" to "MyCustomItemType". 

Question: How can I get the data of MyCustomItemType when we start from the top part? Is this possible at all?

Interesting detail: When we use a Query Definition these kind of "inverted" queries would be possible by "using referencing item". Is something similar possible with plain AML?

Thanks for any hint!

Angela

  • Hi Angela,

    AML itself doesn’t really have the joining capability you’d need here. A query definition would probably be the simplest approach to this type of query.

    If you can’t use a QD in this scenario, I’d try creating a custom server method to use as an AML action. 

    <Item type=“Part” action=“GetBomWarehouseData” id=“...” />

    That approach would enable you to include any additional you need in the AML response.

    Out of curiosity - do you have any constraints preventing you from using a Query Definition in this scenario? 

  • Hi Eli,

    thanks for the ideas! I made some progress and at the same time you opened Pandora's box.Joy

    The original idea was that I reuse my custom "all-purpose-all-in-one view". It´s a single Method that renders the result of an AML query to a simple table in a pop-up window. See following example. It uses a AML query to collect all documents hidden in the multiple BOM levels:

    That´s why I asked for the plain AML query - cause I wanted to use the same building technique to "save time" (worked well so farSmile). 

    As this kind of rendering is basically a simplified QD/TGV clone, there is of course nothing wrong with using the original QD/TGV combo. The resulting view is basically the same and QDs can deal with referencing items. Perfect!

    Quote: Out of curiosity - do you have any constraints preventing you from using a Query Definition in this scenario? 

    There were none - before I tested it. In my environment it doesn't work, but it´s not the fault of Innovator. It´s my federated data that I pull via an onGet query.

    My current federation solution is optimized to display federated data in the client (including grid relevant functionality like filter options, paging, etc.). See: Best way to highlight (fully) federated data in grids and views? 

    Now I learned that my federated data isn't perfect enough for AML queries. My "crafted" item properties mainly consist of id and keyed_name. With id and keyed_name they work as clickable links in grid and Forms, but they doesn't work in QDs.
    I think we can tell the reason when we look a the data structure of similar ItemTypes (like Manufacturer Part with the Manufacturer item property). Typically item properties use a bit more data:

    I am right now a bit unsure of what to do. 

    - Use an additional server Method (as you suggested)
    - Rework my federation solution so I can use QD?
    - Make an additional federated view that already contains all the data?
    - I also got some ideas from another community member that I don´t need to over engineer everything and there is nothing wrong in using more than one AML query to get the data.

    I think I'll let the problem sit for a while and hope it resolves itself.  It´s a fun challenge, but it´s just there because my users are spoiled. In the past people would have been totally fine with an SSRS report for this kind of view.Joy

    Edit: In QD I right now get following error message when testing the query:  <faultstring>Item attribute 'itemTypeName' not set !</faultstring> . In only appear when my custom federated ItemType is included. For testing purposes I added various additional attributes, but couldn´t get rid of the error message.

  • I made some progress. I solved the original use case by making an additional SQL view. Not my favorite solution, but this way I was able to read the data with a much simpler AML query.

    But this topic is still a rabbit hole regarding the query definition. 

    I so far didn´t succeed in using my federated ItemType inside a QD. Even if I create a completely new QD with just the Federated ItemType I get the "<faultstring>Item attribute 'itemTypeName' not set !</faultstring>" error message.

    And I think I know the reason. QDs work different than the Grid. Right know my federation onGet query is optimized for the classic "Grid - Relationship - Form" trinity. But QDs work different, as they support more additional functionality like levels and reverse properties. My Federated ItemType cannot work, cause it doesn't provide the data that a QD needs. I right now just return the regular item structure but QDs works a lot with references.

     Do you know if there is any information of the basic data structure a Query Definition uses when pulling data? It´s not an important topic right now, but I see a lot of fun use cases for these kind of queries. 

  • I can check with the PM and architect for QD to see if we have any info to share. Using federated data in QD’s certainly sounds like a helpful use case.

    PS: “We have an item property at home” gave me a good laugh Laughing