Report to Find Items w/ Null Relationship
Good day all. I am having a hard time trying to create a report to find Documents without files attached. This is the current config.
Aras v11 SP10
s_Document - Poly Source for the following ItemTypes.
s_Delivered Document
s_Drawing
s_Parts List
s_Manual
s_Specification
s_RefLibrary
All of there ItemTypes have RelationshipTypes with File. How do I create a report to return all of the Documents that do not have a file attached? I tried to use a variation of the Query for an item and return its configuration example from the Programmers guide. I changed all of the information from Part & BOM to s_Drawing and File. When I Run Server Method, it produces an error of Not a single item. Thank you for the help with this.
Well, I got this to work. I can spit out a report with the information that I am looking for. I have a small script in a Method & the Report uses the Stylesheet to make it look pretty. I'm sure there is a bit more that can be stripped out of the Stylesheet, I just haven't played with it.
My next step is to figure out how I can include the number of items searched through & the number returned so I can give a percentage.
Method
Innovator myInnovator = this.getInnovator();
Item results = myInnovator.applySQL(
"select item_number,cage,state,major_rev " +
"from [s_Delivered_Document]" + "where id not in (select source_id from s_Delivered_Document_File) and is_current=1" +
"order by item_number,cage");
return results;
Report Stylesheet
<xsl:stylesheet version="1.0" xmlns:xsl="">www.w3.org/.../Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:aras="">http://www.aras-corp.com">
<xsl:output method="html" omit-xml-declaration="yes" standalone="yes" indent="yes" cdata-section-elements="script msxsl:script"></xsl:output>
<xsl:template match="/">
<html>
<head></head>
<style type="text/css" userData="Global">
.cellSolidRightBottom {
border-right:1px #000000 solid;
border-bottom:1px #000000 solid;
}
.cellSolidRight {
border-right:1px #000000 solid;
}
.cellHeader {
background-color:#CCCCCC;
border-top:1px #000000 solid;
border-right:1px #000000 solid;
border-bottom:1px #000000 solid;
padding:2px;
text-align:center;
text-transform:capitalize;
text-align:center;
font-family:helvetica;
font-weight:bold;
font-size:8pt;
}
</style>
<body topmargin="50" leftmargin="50">
<table border="0" cellspacing="0" cellpadding="0" width="670">
<tr>
<td width="260"></td>
<td width="130"></td>
<td width="130"></td>
<td width="130"></td>
<tr valign="top">
<td align="left" uniqueID="ms_id77" colspan="9"> </td>
</tr>
</tr>
<tr valign="bottom">
<td style="font-family:helvetica;font-size:15pt;color:#DA1943;padding:2px;" align="left" uniqueID="ms_id79">Manuals Without Attachments</td>
</tr>
<tr valign="bottom">
<td colspan="0" style="font-family:helvetica;font-size:10pt;padding:2px;" align="left" uniqueID="ms_id80">
Generated on:
<base id="ms_id81"/>
<script> var d = new Date(); var months = ["January","February","March","April","May","June","July","August","September","October","November","December"]; document.getElementById("ms_id81").innerHTML = d.getDate()+" "+months[d.getMonth()]+" "+d.getFullYear(); </script>
</td>
</tr>
<tr>
<td class="cellHeader" style="border-left:1px #000000 solid;">Document</td>
<td class="cellHeader">CAGE</td>
<td class="cellHeader">State</td>
<td class="cellHeader">Revision</td>
</tr>
<xsl:apply-templates mode="ORPH" select="//Item"> </xsl:apply-templates>
</table>
</body>
<script src="../../javascript/PopupMenu.js"></script>
<script src="../../javascript/PopupMenu.js"></script>
</html>
</xsl:template>
<xsl:template mode="ORPH" match="//Item">
<tr>
<td class="cellSolidRightBottom" style="border-left:1px #000000 solid; font-family:helvetica; font-size:8pt; padding:2px;">
<xsl:value-of select="item_number"></xsl:value-of>
<xsl:if test="item_number='' or not(item_number)">
<xsl:text>Â </xsl:text>
</xsl:if>
</td>
<td class="cellSolidRightBottom" style="font-family:helvetica; font-size:8pt; padding:2px;">
<xsl:value-of select="cage"></xsl:value-of>
<xsl:if test="cage='' or not(cage)">
<xsl:text>Â </xsl:text>
</xsl:if>
</td>
<td class="cellSolidRightBottom" style="font-family:helvetica; font-size:8pt; padding:2px;">
<xsl:value-of select="state"></xsl:value-of>
<xsl:if test="state='' or not(state)">
<xsl:text>Â </xsl:text>
</xsl:if>
</td>
<td class="cellSolidRightBottom" style="font-family:helvetica; font-size:8pt; padding:2px;">
<xsl:value-of select="major_rev"></xsl:value-of>
<xsl:if test="major_rev='' or not(major_rev)">
<xsl:text>Â </xsl:text>
</xsl:if>
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
