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. 

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

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

Children
No Data