Building a complicated tree from criteria on its leaves
We have a product structure, fairly common I think (much of is it based on standard packages) where we have deliveries/vessels on top, one or more layers of product assemblies in between, and purchased parts (with linked manufacturer parts) at the bottom. Vessels are a separate item type, as are manufacturer parts, everything else is Part.
Now, the service department would like a view which lets them search for a specific manufacturer product - maybe one which needs replacement due to a recall, or one which requires a software update - and find all the places it's used.
The "where used" isn't good enough, because it has far too many references which aren't relevant for Service (such as all the ECOs that's ever touched the part), also it's bottom up, whereas they would like to work top-down - naturally, when servicing a part, you want to do all parts on one vessel before the next, all parts in one cabinet before the next etc.
So I'm trying to build a tree view from a search, and I find it pretty challenging. AML's recursive search with GetItemRepeatConfig doesn't work well, because if I put a condition, it demands all intermediate Parts satisfy that condition, not just the leaves. I also have to deal with a different Relationship type on the top level and on the bottom level. Finally, it requires a single item (by ID) as the root.
I imagine I'm not the only one having this sort of problem. How have you solved similar problem? Do you have a go-to method for returning (multi)trees based on conditions on their leaves?
Hi Harald,
In our company, we have several similar use cases where we need custom trees (or a specific information from a tree).
I am not sure if this use case comparable to yours, but it´s maybe a good example: We have certain products that are UL certified. If we add alternative Manufacturer Parts to components, we need to inform UL about the change. However, it’s difficult for users to see whether a specific part is used in an UL-certified product. Some parts, such as a simple resistor, are used virtually everywhere. People would have to click through many BOM levels and Part references, just to find the few that are relevantFor this use case, we only need information about the top-level parts that represent the certified products. We solved this use case with a recursive SQL CTE query that traverses the levels and filters the necessary data ("=which non-EOL and UL certified products use this resistor?"). Bonus: The query is connected to the MCO/ECO, so the required additional step to inform UL is automatically added to the MCO if we have affected products.
The example represents a bottom-up approach. Of course, it can also be used the other way around.
Your question is a bit similar to this one: link
You might notice that also other users recommend SQL as solution for custom tree related queries.
