11 Replies Latest reply on Aug 26, 2015 2:16 PM by BruceRobertson

    Parent Tables, Table Occorrences and flow of Information

    kilbot

      Title

      Parent Tables, Table Occorrences and flow of Information

      Post

           Question:

           I have a parent table PEOPLE, and several table occurrences: COMPLAINANTS, COMPLAINING WITNESSES, and RESPONDENTS (all in green below). I have placed joint tables between the occurrences and the CASES table.

           My parent table contains all the information on individuals that are involved in cases. One individual can be listed on multiple cases in different ways (ex: as a Complainant, Complaining Witness or Respondent). I want to be able to go to the persons record (PEOPLE table) and see every case they are listed on. How can I do this?

            

        • 1. Re: Parent Tables, Table Occorrences and flow of Information
          philmodjunk

               I think that you have these relationships:

               Complainants-----<Complainant_Case>------CASES----< Comp_Wit_Case>----ComplainingWitnesses
                                                                                  |
                                                                                  ^
                                                                       Respondant_Case
                                                                                  v
                                                                                  |
                                                                            Respondants

               That correct? (can't see any uploaded image of your relationships graph. Did you upload a file of the correct type? (Gif, JPEG, PNG only) )

               Hmmm, I'm thinking as I type here.....

               If you had ONE data source table for all your join table occurrences, you might be able to pull this off.

               Complainants::__pkPeopleID = Complainant_Case::_fkPeopleID
               Cases::__pkCaseID = Complainant_Case::_fkCompCaseID

               ComplainingWitnesses::__pkPeopleID = Comp_Wit_Case::_fkPeopleID
               Cases::__pkCaseID = Comp_Wit_Case::_fkCompWitCaseID

               and the same for Respondants. The key being that you have one join table with different match fields to the Case ID.

               This would allow you to set up this relationship

               People------<People_Case>--------Cases    (but I'd probably use a different occurrence of Cases)

               People::__pkPeopleID = People_Case::_fkPeopleID
               Cases::_pkCaseID = People_Case::_fkcCombinedCaseID

               _fkcCombinedCaseID would be a calculation field with this expression:

               List ( _fkCompCaseID ; _fkCompWitCaseID ; _fkResponCaseID ) //for any given record, only one of these fields would have a value.

               Then you could place a portal to People_Case on your People Layout and list each case to which a given people record was linked.

               PS. If you have FileMaker 12, I'd look at Execute SQL to set up a Union query as a much simpler method for listing all cases to which a given person has been linked.

          • 2. Re: Parent Tables, Table Occorrences and flow of Information
            kilbot

                 I do have Filemaker 12

                 I'll have to find information on the Execute SQL and Union Query

                 I will see what I can come up with.

            • 3. Re: Parent Tables, Table Occorrences and flow of Information
              kilbot

              Here if you would like to take a look I uploaded the database

              http://www.sendspace.com/file/njjxsu

               

              Here is a screen shot before I try anything

               

              a73e1f6d58_1.jpg

              • 4. Re: Parent Tables, Table Occorrences and flow of Information
                philmodjunk

                     First step would be to get ExecuteSQL to query one Join table with a relational "join" to cases so that you see all cases with that _number_id_person value in _kf_number... of the join table.

                     Once you can get that query to work, you can either set up the UNION query or you can write 4 calls to executeSQL and place them inside a List function like this:

                     List ( ExecuteSQL (//query for interviewees goes here) ; ExecuteSQL ( //query for Complainant goes here) ....//and so forth )

                     To see what syntax can be used in ExecuteSQL, open the ODBC JDBC Guide from the FileMaker Help menu and look up the SELECT statement. Seed Code's free SQL Explorer is also very useful in constructing your first queries with correct syntax.

                • 5. Re: Parent Tables, Table Occorrences and flow of Information
                  kilbot

                       So I'm a little lost do my relationships in the snap shot look correct?

                  • 6. Re: Parent Tables, Table Occorrences and flow of Information
                    philmodjunk

                         The only snap shot I see is the one before you make any changes and I don't know which method you wish to implement.

                    • 7. Re: Parent Tables, Table Occorrences and flow of Information
                      kilbot

                      Okay I'm a little lost but tried to draw up what you stated

                      Does this look correct to how you were directing me?

                       

                      a73e1f6d58_2.jpg

                      • 8. Re: Parent Tables, Table Occorrences and flow of Information
                        philmodjunk

                             Not quite. What I described uses same data source table for all three join table occurrences.

                             In this latest screen shot, records from both the Complainents and Respondants are matching to the same field in the join table and I can tell that you have three different tables being used as join tables instead of just one.

                        • 9. Re: Parent Tables, Table Occorrences and flow of Information
                          kilbot

                          Okay would it be like this?

                           

                          a73e1f6d58_3.jpg

                          • 10. Re: Parent Tables, Table Occorrences and flow of Information
                            philmodjunk

                                 I hadn't considered that. It might or might not work. I think it would cause issues for your existing design--especially and calculation fields in People that refer to fields in the join table as well as on some of your existing layouts. But I can't categorically say that it wouldn't work.

                                 Here's what I had in mind:

                                 In Manage | Database | relationships, make a new table occurrence of the Join table by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                                 We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                                 Do this a second time and you have three occurrences of the same data source table.

                                 You may find this tutorial on table occurrences helpful: Tutorial: What are Table Occurrences?

                            • 11. Re: Parent Tables, Table Occorrences and flow of Information
                              BruceRobertson

                              I would think it should look more like this.

                              case_graph.png