jayessj - Tuesday, August 9, 2016 10:56 AM:
Could someone help me out with the required syntax for an email notification query
I have a current query within an email notification that runs at a particular workflow event. This sends out an email just giving someone key information about the particular item (in this case it is an ECN and I think the notification is an out the box notification as specified below)
<Item type="Activity" id="${Item/ActivityId}" action="get" select="name,message"/>
<Item type="${Item/@type}" id="${Item/@id}" action="get" select="item_number,title,description"/>
<Item type="ItemType" id="${Item/@typeId}" action="get" select="label"/>
This works ok but what I need is to add the affected items to the email and therefore I need to query them first before I try and call then via html in the email body.
What would the correct syntax be for this?
I have another query that runs in a grid to give me a summary for the ECN and thought I could lift something from that but I am not familiar with AML and therefore am struggling to know what to add to the notification query string
The other query that runs in a grid is as follows which does pull out the required affected items via the query below. I think there is also a report (ECN Report - most likely in the standard install) that also calls the affected items
<Item type="ECN Affected Item" action="get" select="id,related_id">
<source_id>{id}</source_id>
<related_id>
<Item type="Affected Item" action="get" select="id,affected_id,new_item_id,action,is_condition,was_condition">
<affected_id>
<Item type="Part" action="get" select="id,item_number,name,major_rev"/>
</affected_id>
<new_item_id>
<Item type="Part" action="get" select="id,item_number,name,major_rev"/>
</new_item_id>
<Relationships>
<Item type="Affected Item Relationship" action="get" select="id,related_id">
<related_id>
<Item type="Affected Relationship" action="get" select="id,affected_rel_id,new_rel_id,rel_action">
<affected_rel_id>
<Item type="Part BOM" action="get" select="id,keyed_name"/>
</affected_rel_id>
<new_rel_id>
<Item type="Part BOM" action="get" select="id,keyed_name"/>
</new_rel_id>
</Item>
</related_id>
</Item>
</Relationships>
</Item>
</related_id>
</Item>
edonahue - Tuesday, September 27, 2016 12:59 PM:
Hi Jayessj,
Since you are trying to retrieve a related item deep in the data structure, I recommend using a SQL query in a "where" clause. The following sample AML queries for Parts that are related to an ECN via an ECN Affected Item > Affected Item > affected_id/new_item_id property.
<AML>
<Item type="Part" action="get" select="item_number,name,classification" where="[Part].id in (SELECT p.ITEM_NUMBER FROM innovator.[ECN_AFFECTED_ITEM] eai INNER JOIN innovator.[AFFECTED_ITEM] ai ON eai.RELATED_ID=ai.ID INNER JOIN innovator.[PART] p ON p.ID=ai.AFFECTED_ID OR p.ID=ai.NEW_ITEM_ID WHERE eai.SOURCE_ID='${Item/@id}')" />
</AML>
Here is the SQL query in the where clause, formatted for readability:
SELECT p.ITEM_NUMBER
FROM innovator.[ECN_AFFECTED_ITEM] eai
INNER JOIN innovator.[AFFECTED_ITEM] ai
ON eai.RELATED_ID=ai.ID
INNER JOIN innovator.[PART] p
ON p.ID=ai.AFFECTED_ID OR p.ID=ai.NEW_ITEM_ID
WHERE eai.SOURCE_ID='${Item/@id}'
dylan.klima - Tuesday, September 27, 2016 4:12 PM:
To use the AML to get the Part, you have to change SELECT p.ITEM_NUMBER to SELECT p.ID .
<AML>
<Item type="Part" action="get" select="item_number,name,classification" where="[Part].id in (SELECT p.ID FROM innovator.[ECN_AFFECTED_ITEM] eai INNER JOIN innovator.[AFFECTED_ITEM] ai ON eai.RELATED_ID=ai.ID INNER JOIN innovator.[PART] p ON p.ID=ai.AFFECTED_ID OR p.ID=ai.NEW_ITEM_ID WHERE eai.SOURCE_ID='${Item/@id}')" />
</AML>
edonahue - Tuesday, September 27, 2016 6:00 PM:
Hi dylan.klima,
The select clause can be customized to include as many or as few properties as needed to display in the email notification. AML queries always return a minimum of an item's id and type.
dylan.klima - Tuesday, September 27, 2016 6:07 PM:
I mean in the SQL. You just made an error with the wrong field, it will never return anything unless you happened to put an ID in the item_number field.
edonahue - Tuesday, September 27, 2016 6:13 PM:
Ah, I see what you mean now. I was looking at your corrected SQL, not mine, trying to find the mistake you were referencing. :)