• State Verified Answer
  • Replies 5 replies
  • Subscribers 12 subscribers
  • Views 8788 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. 

Parents
  • 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);
Reply
  • 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);
Children
No Data