3 Replies Latest reply on Feb 7, 2012 9:00 AM by philmodjunk

    Report based on a Self Join



      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.






        • 1. Re: Report based on a Self Join

          Ah yes, the joys of ISO 9000 document control systems. Been there done that in a previous job.

          Seems like you need a different relationship as the X doesn't really give you a parent child relationship of the structure that you need here. It can be made to work, but you are limiting your self with the need to add filtered portals at every point.

          Relationships that are structured like this:

          Manual----<SOP----<Form     (----< means one to many)

          Allow you to set up a report based on the Form table occurrence with records drawn from the preceding occurrences now available to put in sub summary and other layout parts to complete the report.

          • 2. Re: Report based on a Self Join

            Hello Phil,

            As a matter of fact this is a try to simplify things.

            Many posts back I got your help to set up a structure as the one you are mentioning: Manual --< SOP --< Work Instruction --< Form --< Record

            Although I managed to make it work, with your help, now it's time to improve the project. Since I have quite a number of tables, table occurrences, and relationships, any improvement means a lot of work. So I wanted to know if there was a clever way of setting the whole structure.

            I remember that back then, you mentioned something like why not using just the Document table to add all the different document types and then do some tricks at the report level to simulate the recurrent Parent --< Child relationship.

            Anyhow, if you think that I am better off with the: Manual --< SOP --< Work Instruction --< Form --< Record setting I'll keep it that way. 







            • 3. Re: Report based on a Self Join

              In the example I gave, Manual----<SOP----<Form

              could all be different occurrences of the documents table. You can also use different tables for these documentation levels, all with one to one relationships to different occurrences of the document table.