Report to Find Items w/ Null Relationship

Offline Nathan over 3 years ago

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.

Top Replies

Offline Nathan over 3 years ago +1 verified

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…

Parents

+1 Offline Nathan over 3 years ago

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>

.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>

<tr>

</tr>

<td style="font-family:helvetica;font-size:15pt;color:#DA1943;padding:2px;" align="left" uniqueID="ms_id79">Manuals Without Attachments</td>

</tr>

Generated on:

</td>

</tr>

<tr>

<td class="cellHeader" style="border-left:1px #000000 solid;">Document</td>

<td class="cellHeader">State</td>

<td class="cellHeader">Revision</td>

</tr>

<xsl:apply-templates mode="ORPH" select="//Item"> </xsl:apply-templates>

</table>

</body>

</html>

</xsl:template>

<xsl:template mode="ORPH" match="//Item">

<tr>

<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>

<xsl:value-of select="cage"></xsl:value-of>

<xsl:if test="cage='' or not(cage)">

<xsl:text>Â </xsl:text>

</xsl:if>

</td>

<xsl:value-of select="state"></xsl:value-of>

<xsl:if test="state='' or not(state)">

<xsl:text>Â </xsl:text>

</xsl:if>

</td>

<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>

Reply

+1 Offline Nathan over 3 years ago

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>

.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>

<tr>

</tr>

<td style="font-family:helvetica;font-size:15pt;color:#DA1943;padding:2px;" align="left" uniqueID="ms_id79">Manuals Without Attachments</td>

</tr>

Generated on:

</td>

</tr>

<tr>

<td class="cellHeader" style="border-left:1px #000000 solid;">Document</td>

<td class="cellHeader">State</td>

<td class="cellHeader">Revision</td>

</tr>

<xsl:apply-templates mode="ORPH" select="//Item"> </xsl:apply-templates>

</table>

</body>

</html>

</xsl:template>

<xsl:template mode="ORPH" match="//Item">

<tr>

<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>

<xsl:value-of select="cage"></xsl:value-of>

<xsl:if test="cage='' or not(cage)">

<xsl:text>Â </xsl:text>

</xsl:if>

</td>

<xsl:value-of select="state"></xsl:value-of>

<xsl:if test="state='' or not(state)">

<xsl:text>Â </xsl:text>

</xsl:if>

</td>

<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>

Children

No Data