4 Replies Latest reply on Jun 18, 2013 2:06 PM by PhillipFountain

    Linking Tables

    PhillipFountain

      Title

      Linking Tables

      Post

            

           I have a layout with a primary table (audit Protocol) representing text fields for the requirements of an audit. There are additional tables that representing: cross references, questions, remarks, and section comments for the audit linked in a one to many relationship with one being a one to one relationship. The one to one relationship is required, for the company being audited, because there is only one cross reference for each requirement in the primary table.

           This layout is fine for a one company layout, but it would be beneficial to introduce a table that lists multiple companies that would be used to select audit questions, cross references, and placement of remarks etc. for each company.

           Any help with the relationships would help. The attached relationship graph is what I’m working with.

      Relationship_Graph.jpg

        • 1. Re: Linking Tables
          ninja

               Howdy,

               Let me reword one of your statements into what I think is your answer.

               YOURS:  This layout is fine for a one company layout, but it would be beneficial to introduce a table that lists multiple companies that would be used to select audit questions, cross references, and placement of remarks etc. for each company.

               MINE: This layout is fine for a one company layout, but it would be beneficial to introduce a LAYOUT that lists multiple companies that would be used to select audit questions, cross references, and placement of remarks etc. for each company.

               This layout would be in list view, based on tblOperators which is then linked by    tblOperators::_OperatorIDpk = tblProtocol2::_OperatorIDfk

          • 2. Re: Linking Tables
            PhillipFountain

                 I think you almost got it, I did not explain it clearly enough. I only need to select one company in a Drop-down list that is associated with the company name field which would then display all the field information relating to the protocol. The layout would not be appropriate in list view, only one company should be viewed at a time, but it would be benefical to change companies at any point in the protocol to compare information with previous audits.

                 So I'm not quite were I want to be yet.

            • 3. Re: Linking Tables
              ninja

                   Good deal,

                   My answer stands with the following edits:

                   1. use form view instead of list view

                   2. do a find for the company in the Company field

                   3. leave the linkage as described in my previous email

                   4. put fields on your layout from the other tables as appropriate

                    

                   FWIW, you could make it fancy by putting a global field (tblOperators::gCompany) on the layout and tag it with an OnModify script trigger.

                   Create a value list based on the Company Field

                   Make the global field on the layout a dropdown list using that value list.

                   Have the OnModify script do your find for you.

                   That way when you change the value in your global field, it will search for that record rather than you having to manually do the find each time.

              • 4. Re: Linking Tables
                PhillipFountain

                      

                     Your tblOperators::_OperatorIDpk = tblProtocol2::_OperatorIDfk gives a one company to many audit Protocols which cannot happen for a given year. The relationship has to be a one to one in this case. I did not state that the audit is updated on January of each year and expires July 1 of the following year. So companies will see and be audited every two years so a one to one relationship is appropriate here. The advantage is that the auditor can look across the companies he has audited over the last 18 months. If an auditor does 10 to 15 audits in this time frame he is doing very good.