• State Verified Answer
  • Replies 5 replies
  • Subscribers 12 subscribers
  • Views 8806 views
  • Users 0 members are here
Options
Related

Report to Find Items w/ Null Relationship

Nathan
Offline

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. 

  • 0 Offline

    Hi Nathan

    You can try something like below C# method.  itemswithoutFile  set has all items without file and itemswithFile  set has items with file.

    Verify the item type name highlighted below (s_Document is poly item and s_DocumentFile is relationship item type name)

    Innovator inn = this.getInnovator();
    HashSet<string> itemswithoutFile = new HashSet<string>();
    HashSet<string> itemswithFile = new HashSet<string>();
    Item allItemType = inn.newItem("s_Document", "get");
    allItemType.setAttribute("select", "id,item_number");
    allItemType = allItemType.apply();
    if (!allItemType.isError())
    {
    for (int i = 0; i < allItemType.getItemCount(); i++)
    {
    Item isFileExist = inn.newItem("s_DocumentFile", "get");
    isFileExist.setAttribute("select", "id");
    isFileExist.setProperty("source_id", allItemType.getItemByIndex(i).getID());
    isFileExist = isFileExist.apply();
    if (isFileExist.isError())
    {
    itemswithoutFile.Add(allItemType.getItemByIndex(i).getProperty("item_number"));
    }
    itemswithFile.Add(allItemType.getItemByIndex(i).getProperty("item_number"));
    }
    }
    return this;

  • 0 Offline in reply to Gopikrishnan

    Gopikrishnan,

    Thank you for this information.  Since I am still new to Aras, I know that I am missing something with this.  I created the Method with the script and created a Report to run it.  When I run the report, a new tab opens and nothing is displayed. 

    Report

    Type - Generic

    Location - Server

    Target - Window

    Method - sm_Document_Files

    I changed the Item to s_Manual and the isFileExist to s_Manual File.  The s_Document doesn't have any relationships. 

    Thank you again for your help with this.

  • 0 Offline

    I have not been able to create a report to accomplish this, but I was given this bit of SQL code to run from Nash.  It takes a little bit to make things pretty, but it works. 

    <SQL>

    select item_number,cage from S_DELIVERED_DOCUMENT where id not in (select source_id from S_DELIVERED_DOCUMENT_FILE) and is_current=1

    </SQL>

    My ItemType is called s_Delivered Document and the Relationship is called s_Delivered Document File.  The underscore replaces a space.  The names do not need to be capitalized, that is the way Wency (from Aras) sent it to me. 

    Now to figure out how to add it into a report.

  • 0 Offline

    I'm getting a little closer.  I was able to create a Method that will pull the information, but it does not display correctly.  I used the Need for Speed Apply SQL method from the v11 Programmers Guide as a base and modified the small bits I needed.  I was able to attach my condition for the SQL query, so it was only pulling the information that I wanted.  The output from this (and the I used the Need for Speed Apply SQL) does not create a table to display the data.  Any help on this would be appreciated.  Thank you.

    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");

    string content = "" +

    "<style type='text/css'>" +

    "table {background:#000000;}" +

    "th {font:bold 10pt Verdana; background:#0000FF; color:#FFFFFF;}" +

    "td {font:normal 10pt Verdana; background:#FFFFFF;}" +

    "caption {font:bold 14pt Verdana; text-align:left;}" +

    "</style>" +

    "<table id='tbl' border='0' cellspacing='1' cellpadding='2' datasrc='#itemData'>" +

    "<caption>Delivered Documents Without Attachments</caption>" +

    "<head>" +

    "<tr>" +

    "<th>Document</th>" +

    "<th>CAGE</th>" +

    "<th>State</th>" +

    "<th>Revision</th>" +

    "</tr>" +

    "</head>" +

    "<body>";

    int count = results.getItemCount();

    for (int i=0; i<count; i++) {

    Item user = results.getItemByIndex( i );

    content += "<tr><td>" + user.getProperty( "item_number", "" ) + "</td>";

    content += "<td>" + user.getProperty( "cage", "" ) + "</td>";

    content += "<td>" + user.getProperty( "state", "" ) + "</td>";

    content += "<td>" + user.getProperty( "major_rev", "" ) + "</td></tr>";

    }

    content += "" +

    "</body>" +

    "</table>";

    return myInnovator.newResult(content);
  • +1 Offline

    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>