9 Replies Latest reply on May 13, 2011 12:32 AM by NaturSalus

    Report from related tables

    NaturSalus

      Title

      Report from related tables

      Post

      Hello,

      I have the following tables:

      DocP

      FormDocP

      DocS

      FormDocS

      Their relationships are:

      DocP -> FormDocP (Parent -> Child1)

      DocP -> DocS -> FormDocS (Parent -> Child 2 -> GrandChild)

      DocP::__kpDocPID = FormDocP::_fkDocPID

      DocP::__kpDocPID = DocS::_fkDocPID

      DocS::__kpDocSID = FormDocS::_fkDocSID

      I have a layout based on the FormDocP table where I can pull out the fields from the DocP & FormDocP tables and generate a report.

      I have  a layout based on the FormDocS table where I can pull out the fields from the DocS & ForDocS tables and generate a report.

      My goal is to have a report with the following fields from the 4 tables:

      DocP::Name

      FormDocP::Name

      DocS::Name

      FormDocS::Name


      A layout based on  the FormDocS table pulls out the Name field from the tables: DocP, DocS and FormDocS, but NOT from the FormDocP table.

      The Parent -> Child linear relationship is true for DocP -> DocS -> FormDocS table, but the branch DocP -> FormDocP  is not resolved when the layout report is created from the FormDocS context.

      My question is: how to setup layout report for the following fields:

      DocP::Name

      FormDocP::Name

      DocS::Name

      FormDocS::Name

      Is there a way to merge the following two reports:

      Report from the  FormDocP table

      Report from the FormDocS table

      Thanks in advance for any guidance.

        • 1. Re: Report from related tables
          philmodjunk

          Is DocP -> FormDocP A one to many relationship? (Is more than one FormDocP record is linked to the same DocP record?)

          Are you using the same table occurrence for DocP to link to FormDocP as you are using to link to FormDocS?

          • 2. Re: Report from related tables
            NaturSalus

            Hello

            Is DocP -> FormDocP A one to many relationship? 

            Yes

            Are you using the same table occurrence for DocP to link to FormDocP as you are using to link to FormDocS?

            DocP TO links directly to FormDocP TO

            DocP TO links directly to DocS TO

            DocS TO links directly to FormDocS

            Maybe the following information could be of help to you:

            Real world meaning of the tables.


            This is part of a Document Management project.

            All documents have a herarchical relationship.

            Manual (not relevant in this case) -> Standard Operating Procedure -> Work Instruction

            Standard Operating Procedure -> Standard Operating Procedure Forms

            Work Instruction -> Work Instruction Forms


            Scenarios


            Always there are Standard Operating Procedures

            Most of the time (but not always) there are Standard Operating Procedure Forms

            Sometimes there are Work Instructions

            When there is a Work Instruction it always has Work Instruction Forms

            Nomenclature Correspondences

            DocP (Parent) = Standard Operating Procedures (SOP)

            FormDocP (Child) = Forms associated to each SOP

            Relationship: one to many: DocP (Parent) -> FormDocP (Child)

            It is possible that some SOP could not have any form associated to it.


            DocS (Child) = Work Instruction

            FormDocS (GrandChild) = Forms associated to each Work Instruction

            Relationship: one to many: DocS (Child) -> FormDocP (GrandChild)

            Every Work Instruction has a form associated to it.

            Table TOs

            DocP (Standard Operating Procedure) TO related to FormDocP TO

            DocP (Standard Operating Procedure) TO related to DocS (Work Instruction)

            The same DocP TO relates to FormDocP To and to Doc S TO

            DocS (Work Instruction) TO related to FormDocS TO

            Thanks for looking into my question.

            • 3. Re: Report from related tables
              philmodjunk

              I've worked with ISO document control so the the terms and hierarchy are familiar to me.

              Due to the one to many relationship between DocP (SOP) and FormDocP (SOPForms) there's no way from just these relationships to determine which record in FormDocP should return the name you want on your FormDocS (WIForms) layout. There could be 20 forms with 20 different names all linked to the same SOP as the one to which your current FormDocS record is linked.

              The relationships will "evaluate" by tracing back through the relationships from Table Occurrence to Table Occurrence in your relationships graph.

              Moving up the chain from many to one as you move from FormDocS to DocS to DocP works as there is only one record to which they can match at each step. But once you reach DocS there is no way to tell which record in FormDocP related to that one DocP record is the one you need in order to display the names.

              • 4. Re: Report from related tables
                NaturSalus

                But once you reach DocS there is no way to tell which record in FormDocP related to that one DocP record is the one you need in order to display the names

                Okay that's the reason why I am having problem with my report created from the FormDocS (GrandChild) layout.

                Since I can display on a report the Management System Name, the Procedure (DocP or Parent) Name, the Work Instruction (DocS or Child) name, the Work Instruction Forms (FormDoc or GrandCHild), why can't I display all the Procedure Forms (Form DocP or Item)

                I think that as long as I can display the Procedure (DocP or Parent) Name (__kpProcedureID)  there is no ambiguity concerning which Items, Childs opr GrandChildren to display.

                Am I missing something?

                Is there a way to merge into one two perfectly working reports?

                Working reports:

                Procedures (DocP) report

                Management System

                Procedure Name

                Procedure Form (FormDocP) Name

                Work Instructions (DocS) report

                Management System

                Procedure (DocP) Name

                Work Instruction (DocS) Name

                Work Instruction Form (FormDocS) Name

                Desired report:

                Management System Documents

                Management System

                Procedure (DocP) Name

                Procedure Form (FormDocP) Name

                Work Instruction (DocS) Name

                Work Instruction Form (FormDocS) Name

                Thanks for you feedback

                • 5. Re: Report from related tables
                  philmodjunk

                  What method did you try in order to include the Procedure Forms in your report?

                  Since this represents multiple records, you can't just add the name field from FormDocP to your FormDocS layout like you can the name fields from the other layout.

                  If you place a portal to FormDocP on your layout and place the name field from FormDocP inside this portal, do you see the names you would expect in this portal?

                  You can make the portal many rows tall and use Sliding & Visibility in the Inspector's tab to set the portal and all objects below it in the same layout part to "slide up"; "Also resize enclosing part".

                  Key facts about sliding:

                  1. It's only visible in preview mode and when you print/save as PDF...
                  2. All layout objects below a slide/resize field need to also be set to slide up and resize.
                  3. Objects in headers and footers will not slide.
                  4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                  5. Consistent side borders are difficult to achieve with sliding fields.

                  • 6. Re: Report from related tables
                    NaturSalus

                    What method did you try in order to include the Procedure Forms in your report?

                    Starting from the Work Instruction Forms I added a sub-summary where I put the Procedure::Name and a portal to the Procedure Forms.

                    If you place a portal to FormDocP on your layout and place the name field from FormDocP inside this portal, do you see the names you would expect in this portal?

                    No.

                    I can only get the values of the Procedure::Name field.

                    So,or it can't be done this way:

                    Management System Documents

                    Management System

                    Procedure (DocP) Name

                    Procedure Form (FormDocP) Name

                    Work Instruction (DocS) Name

                    Work Instruction Form (FormDocS) Name

                    Thanks for you feedback

                    or I am missing something.

                    Regards

                    • 7. Re: Report from related tables
                      philmodjunk

                      Your relationship has to trace back through quite a few table occurrences to get to the portal's records. There may be some limit you are hitting as to how "far" FileMaker can "tunnel", or one of the intervening tables does not have the required related record and thus breaks the chain of relationships.

                      I'll see if I can replicate this structure here and run a few tests.

                      Last time I set up a document control database for ISO documents, I used a different table structure. The company also didn't allow me to use FileMaker so I had to use MS Access, but the table structure could serve with FileMaker and might avoid the problem you are having here.

                      The approach I took, was that all documents, from all levels of the ISO hierarchy are just documents. The hierarchy wasn't nearly as important as the fact that revision control, management of hard copy distribution locations, review/revision operations are much the same for each document. Thus, I had one documents table, but labeled them with a document type field. With that structure, I could list all document titles in the body, but use sub summary parts and sorting to correctly group the records organised to reproduce the ISO Hierarchy.

                      • 8. Re: Report from related tables
                        philmodjunk

                        I was able to replicate your table structure and get the report to preview correctly.

                        Please compare your file to this sample file and see if you can spot what's different:  http://www.4shared.com/file/h2ziED6o/ISODocHierarchy.html

                        • 9. Re: Report from related tables
                          NaturSalus

                          Thank you for your demo file.

                          Definitely there is something wrong with the structure of my project, since I did as in your demo and it is still not showing the data from the Items table.


                          Anyhow the troubleshooting is my task.

                          Thank you for your patience.