9 Replies Latest reply on Mar 15, 2011 10:58 AM by philmodjunk

    Data access through a portal from end to end of a relationship

    johnsmith_1

      Title

      Data access through a portal from end to end of a relationship

      Post

      Hello,

      I am trying to acess data from a table at one end of the relationship through a portal to a table in the other end of the relationship and I don't know how to do it.


      TABLES

      Action table:

      __kpActionID
      Action


      Staff table:

      __pkStaffID
      DepartmentStaff
      JobTitleStaff
      NameFullList
      ...

      Appraisal joint table:

      __pkAppraisalID
      _fkActionID
      _fkManualID
      _fkStaffID
      JobTitleStaff
      DepartmentStaff


      Manual table:

      __kpManualID
      _kfStaffID
      ...

      Procedure table:

      __pkProcedureID
      _fkManualID
      ...


      Relationships

      relationships

      In the Manual table layout I have a tab named Appraisal where I can put the Full Name as well as Job Title and the Department of the Staff that acted as: Originator, Reviewer (Reviewed by) and Approver (Approved by) of the document named Manual. 
      No problem here, using a portal I can choose who was the originator, the reviewers and the approvers of the Manual.
      With the established relationship even the same person can be reviewer and approver. Here is a picture:

      manual

      The problem surfaces when I am trying to do the same from a portal on the Procedures table. 

      My goal is to use the established relationship between:

      the Staff and Action tables with the Manual table through the Appraisal joint table;
      and
      the relationship between the Manual and the Procedure tables.

      To choose the names, job titles and departments of the staff acting as originator, reviewer and approver.

      I wrongly thought that since the table relationships are rightly established, I could use a portal on the Procedure table layout to get data from the Staff table. But as soon as I set up the portal in the Procedure table layout to do it, I get a snapshot of what currently is on the portal on the Manual layout.


      What is the right way to derive data from one end of a relationship (Staff table) into the opposite end (Procedures table)?

      Thanks

        • 1. Re: Data access through a portal from end to end of a relationship
          philmodjunk

          Can't see your screen shots.

          It apears you have this set of relationships:

          Action----<Appraisal>-----Staff
                              v
                              |
                           Manual----<Procedure

          Safest bet is to base your portal on the Join table (Appraisal), but add fields from the other tables (Staff and Action) to the portal row. Your many to one relationship from Procedure to Manual may not allow this to work correctly, however, as many procedure records can link to the same manual which then all link to the same set of appraisal records. I'm not sure that's what you want have happen here or not.

          Do different staff members get assigned to review different procedures within the manual or are they assigned with a given role jus to the entire manual?

          • 2. Re: Data access through a portal from end to end of a relationship
            johnsmith_1

            Can't see your screen shots.

            I have uploaded the images again.

            Safest bet is to base your portal on the Join table (Appraisal), but add fields from the other tables (Staff and Action) to the portal row. 

            I did that way and worked fine for the portal on the Manual table layout.

            Your many to one relationship from Procedure to Manual may not allow this to work correctly, however, as many procedure records can link to the same manual which then all link to the same set of appraisal records. I'm not sure that's what you want have happen here or not.

            I thought that through the right relationships I could use the information contained in the Staff table on the following different tables: Manual, Procedures and Forms. (As a matter of fact there are more document categories linked to those mentioned).

            Through the joint table Appraisal I managed to make it work for the Manual table but not for the next table along the relationship line: Procedures.

            What I tried and didn't work was:

            I created:

            one table occurrence for the Action table, called: Action 2

            one table occurrence for the StaffManual, called: StaffProcedure

            a new joint table called: AppraisalProcedure

            And I created relationships between them and the Procedures table as shown below:

            relationship2


            Then I tried to access to the information stored in StaffProcedure table through a portal on the Procedures table based on the AppraisalProcedure table but I could only pull out information from the StaffProcedure table.

            Portal settings on the Procedures table layout:

            • Show related records from: AppraisalProcedure
            • First field on the portal is Action:
              • Display data from: AppraisalProcedure::_fKActionID
              • Control Style: Dropdown list from Action value list.
            • Second field on the portal is Name:
              • Display data from: AppraisalProcedure::_fKStaffID
              • Control Style: Popup menu from Staff value list.
            • Third field on the portal Job Title:
              • Display data from: AppraisalProcedure::JobTitleStaff
              • Control Style: Edit Box
            • Fourth field on the portal Department:
              • Display data from: AppraisalProcedure::DepartmentStaff
              • Control Style: Edit Box

            In a word the field settings in the portal on the Procedure table are equivalent to those for the portal on the Manual table.

            But:

            • the portal on the Manual table works fine, I can pull out information from the StaffManual and Action tables.
            • the portal on the Procedure table DOES NOT WORK PROPERLY. Yes I can pull out information from the Action table and the Name from the StaffProceduretable but no information shows up on the JobTitle and Department fields. As you can see:

            menu2

            This makes no sense to me.

            No problems with the portal on Manual table but problems with the portal on Procedure table. Both relationships are equivalent and right (I would say). So what is wrong?

            If you have a better idea about how to establish the relationships I would like to know it.

            Do different staff members get assigned to review different procedures within the manual or are they assigned with a given role jus to the entire manual?

            The same or different staff can act as originator, reviewer and approver in all documents (manual -> procedure -> work instruction -> from).

            The purpose of the Staff and Action tables is to be the source of Actions and People and their affiliations for all the tables on the project. So. the same person can have different roles in different documents (manual -> procedure -> work instruction -> from).

            But I can't figure out the way to tell FM how to use the same information several times alon the relationship line.

            Hopefully you know how to do it.

            Thanks,

            jose

            • 3. Re: Data access through a portal from end to end of a relationship
              philmodjunk

              I thought that through the right relationships I could use the information contained in the Staff table on the following different tables: Manual, Procedures and Forms. (As a matter of fact there are more document categories linked to those mentioned).

              Yes, but I don't think you have the "right relationships" in order for this to work from a layout based on procedures.

              Are you trying to assign staff to specific procedures?

              Your original relationships did not support that. When you trace back through the relationships, you have many procedures records related to one record in Manual. That single record in Manual links to all related records in Appraisal and thus you get a list of staff records that link to a given manual, but there's nothing there to link a given staff record to a given procedures record. You've linked all specified staff as a group just to the related record in Manual.

              There's a difference between a "table occurrence" (a box on the relationship graph) and a "Data source table" (defined on the Tables tab of Manage database) even though they may have exactly the same name. If this is a new concept, you may want to check out this tutorial on table occurrences:  Tutorial: What are Table Occurrences?

              The approach you have tried next is a possibility, though it's a complex enough issue that I have to be careful to say that a full answer may require creating a test file for which I do not have the time and energy to create today. You might experiment with this relationship:

              Appraisal----<Procedures>-----Manual

              That way, any given procedure record can list the required staff specific to that procedure.

              • 4. Re: Data access through a portal from end to end of a relationship
                johnsmith_1

                There's a difference between a "table occurrence" (a box on the relationship graph) and a "Data source table" (defined on the Tables tab of Manage database) even though they may have exactly the same name. If this is a new concept, you may want to check out this tutorial on table occurrences: Tutorial: What are Table Occurrences?

                Thanks for the link. I have red almost all the books and watched all instructional videos on FM available out there. But none of them teach you the insights of FM, they only delve on the superficial, irrelevant stuff.

                For what you say I understand that I must base my relationships between:

                Staff and Procedures,

                Staff and Forms on real tables not on table occurrences. Is that what you meant?

                The approach you have tried next is a possibility, though it's a complex enough issue that I have to be careful to say that a full answer may require creating a test file for which I do not have the time and energy to create today. You might experiment with this relationship:

                Appraisal----<Procedures>-----Manual

                That way, any given procedure record can list the required staff specific to that procedure.

                The problem with your suggestion is that I don't see a clear strategy to tackle what lies ahead: manual -> procedures -> work instruction -> form. And to each one I have to assign different staff with different actions (originator, reviewer, approver).

                How do I link Staff, Action with manual -> procedures -> work instruction -> form?

                A simple scheme will be very helpful to me.

                I appreciate your help. 



                • 5. Re: Data access through a portal from end to end of a relationship
                  philmodjunk

                  For what you say I understand that I must based my relationships between Staff and Procedures, Staff and Forms on real tables not on table occurrences. Is that what you meant?

                  Exactly the opposite. Relationships refer to table occurrences. Table occurrences refer to actual physical tables that may have the same name or completely different name. You can discover the data source table name for any given occurrence by double clicking it or by hovering the mouse over the arrow in the upper left corner.

                  Table Occurrences, in many ways, are the "glue" that ties layouts, portals, scripts and records together in such a way that FileMaker can determine which relationships apply in each situation where a calculation evaluates, a layout updates, or a script executes.

                  I don't think a "simple" scheme will work here. (I've worked as a technical writer in charge of ISO documents, so this is suddenly becoming very familiar territory here....) You'll need to identify the smallest division of your manual to which you have to assign specific staff and work up.

                  When I worked with such documentation, work instructions, while linked to specific procedures, were separate documents and had their own set of reviewers, but from your last post, it would appear that a manual is divided into procedures which are divided into work instructions which are divided into forms.

                  The key question: If you assign staff to each form, does that automatically make all the staff assignments for each work instruction, procedure and then the manual? Or are there separate staff assignments for each?

                  • 6. Re: Data access through a portal from end to end of a relationship
                    johnsmith_1

                    For what you say I understand that I must based my relationships between Staff and Procedures, Staff and Forms on real tables not on table occurrences. Is that what you meant?

                    Exactly the opposite. Relationships refer to table occurrences. 

                    You are right. It was wrogly stated on my part.

                    I meant: for each type of document, do I have to create a different  Staff table?

                    The key question: If you assign staff to each form, does that automatically make all the staff assignments for each work instruction, procedure and then the manual?

                    No

                    Or are there separate staff assignments for each?

                    Yes and No. It depends.

                    As you already now. Who originates, writes, reviews and approves a document is not engraved in stone. It all depends.

                    • Originator is any end-user (operator, supervisor, manager, director) that needs a process to be documented.
                    • Reviewers go from the end-user, supervisor, managers, to Quality Head.
                    • Approvers go from Department Heads, Quality Head, to Operations Director.

                    In a word, many people from the Staff table can play different roles during document management. So, no rigid rules here.

                    Let me state my question again: do I have to develop a different Staff table for each type of document? In this way there would be no conflict due to the relationship: manual -> procedure -> work instruction -> form or record.

                    Thanks

                    • 7. Re: Data access through a portal from end to end of a relationship
                      philmodjunk

                      There may not be a "law" but you do have to keep your ISO auditors happy Wink.

                      No, you do not and should not have separate staff tables. Depending on your design, you may have multiple table occurrences of the same staff data source table so that you can link them via join tables to different document tables.

                      Here's a question to think about: Do you really need different tables for Manual, Procedure, Work Instruction and Form? Or could you put these all in the same table with a type field to identify the type of documents? That's how I managed this aspect of document control when I did it. Each document has a title, revision, (plus related revision history table...), department, etc. so it was easy to think of them all just as different documents when it came time to manage review and approval.

                      You can still link your documents to get Manual--->Procedure--->Work Instruction---->Form, by the way. That can be done with additional table occurences of the same documents data source table and need not involve the approval process part of your system unless such links are useful to that part of your process in some way.

                      • 8. Re: Data access through a portal from end to end of a relationship
                        johnsmith_1

                        Here's a question to think about: Do you really need different tables for Manual, Procedure, Work Instruction and Form? Or could you put these all in the same table with a type field to identify the type of documents? That's how I managed this aspect of document control when I did it. Each document has a title, revision, (plus related revision history table...), department, etc. so it was easy to think of them all just as different documents when it came time to manage review and approval.

                        That was my first idea when I started to design the database structure. 

                        But I want the database to allow me to:

                        • List all the working instructions that refer to the step # 5 of the process # 2
                        • List all the forms that derive from procedure SOP-QMS-0020
                        • List by type all the documents approved by Jonny Lister from January 1st 2005 till December 31 2010
                        • List all the documents whose originator was Krank Buddy
                        • and a thillion of reports that must be based in a well structured relational database.

                        I chose FileMaker because it is powerful and was sold as user friendly...

                        Since I don't know your time zone, I don't want to waste your time.

                        If sometime you come up with a design that will allow me to achieve my goal with the relationship: manual -> procedire -> work instruction -> form / recod and a Staff and Action tables, that would be great.

                        If not, don't worry. I have already found out an easy to implement, intuitive database software that allows me to achieve my objective.

                        Thanks for your support and time.

                        • 9. Re: Data access through a portal from end to end of a relationship
                          philmodjunk

                          That's exactly why I included this statement:

                          You can still link your documents to get Manual--->Procedure--->Work Instruction---->Form, by the way. That can be done with additional table occurrences of the same documents data source table and need not involve the approval process part of your system unless such links are useful to that part of your process in some way.