AnsweredAssumed Answered

Report based on a Self Join

Question asked by NaturSalus on Feb 7, 2012
Latest reply on Feb 7, 2012 by philmodjunk


Report based on a Self Join



I am testing whether a self join can be used in a report to list records as per their Parent - Child relationship.

For example, let's have a database for documents. We define 3 "types" of documents:

    • Manual
    • SOP
    • Form


A Manual is a document that consists of several of SOPs.

An SOP is a document that consists of several of Forms.

So from the database point of view:

    • a Manual record is related to a SOP record as a Parent record is to a Child record.
    • an SOP record is related to a Form record as a Parent record is to a Child record.


A self join of the Document table (Document X AllDocument) is used to establish the "Parent" to "Child" relationships between the Document records.


Document table fields:

    • __KP_Document
    • DocumentType
    • DocumentName
    • ParentDocumentName


The relationship used to establish the "Parent" to "Child" relationship between the Document records is the following:


DOCUMENT::__kp_Document X allDOCUMENT::__kp_Document


My goal is to be able to generate a Report that list all the Document records as per their Parent-Child relationship from the higher "Parent" to the lowest "Child". Something like the following:

Document Report List

 Document Type:  Manual               Document Name: Environment Manual

 Document Type: SOP                     Document Name:  SOP1

 Document Type: Form                    Document Name

                                                       Form 1

                                                       Form 2

                                                       Form 3  

Document Type: SOP                     Document Name: SOP2                                                       

Document Type: Form                    Document Name

                                                       Form 4

                                                       Form 5

                                                       Form 6  


Document Type: Manual               Document Name: Quality Manual

Document Type: SOP                     Document Name: SOP3

Document Type: Form                    Document Name

                                                       Form 7

                                                       Form 8

                                                       Form 9  

Document Type: SOP                     Document Name: SOP4

Document Type: Form                    Document Name

                                                       Form 10

                                                       Form 11

                                                       Form 12 


In the real world there are 5 levels down from the higher "Parent" to the lowest "Child", but I have simplified it in my question.

I guess that I need to establish additional self join relationships and put on subsequent Subsummary parts Portals based on those self join relationships, but I haven't managed to make it work yet.

So my question is: what are the self joint relationships, subsummary parts and portals needed?


If showing 3 document levels on the same report is not feasible I could handle having several 2 level reports, instead.