8 Replies Latest reply on Jul 12, 2012 5:21 AM by philmodjunk

    Searching Multiple Table Occurrences

    MikeTrotter_1

      Title

      Searching Multiple Table Occurrences

      Post

      I would like some thoughts on how best to accomplish this search.

      I have a table (tbl_People) that has several occurrences in my file (tbl_PeopleDefendant, tbl_PeopleVictim, tbl_PeopleWitness). A persons role is indicated by their presence in the table occurrence and the same person can be in multiple tables. 

      How can I best search for a person and denote which TO they are found in?

      Thanks for your thoughts,

      Mike

        • 1. Re: Searching Multiple Table Occurrences
          philmodjunk

          In FileMaker, that question doesn't really make sense.

          Since your TO's all refer to the same data source table, if a record is found in one of these TOs, it is found in all of them.

          When you perform a find, it is performed from a context of a specific layout--which in turn refers to a specific TO.

          I would assume that some additional data--either in the field or in a related table will identify a given record as "defandant", "Victim", "Witness", etc and it is possible that a given People record could be more than one of these.

          I suggest explaining in more general terms what you are trying to do and how your tables and TO's are set up.

          • 2. Re: Searching Multiple Table Occurrences
            MikeTrotter_1

            I see the flaw in my logic now. Since all records in tbl_People are included in all subsequent TO's of that table, searching on any TO will result in a match and no inference can be made as to whether the record is actually a defendant, victim, or witness.

             

            My main table (tbl_Cases) is related to tbl_defendant and tbl_victim via a FK (one to many). tbl_Cases is related to tbl_witnesses via a join table (many to many).

            So I will need to grab the key from the match in tbl_People and then search tbl_Cases for the FK match. I will then search the join table for witness matches.

            Thanks for the help.

            • 3. Re: Searching Multiple Table Occurrences
              MikeTrotter_1

              As a follow-up, what is the best was to search all of my join tables for the PersonID and generate an array or list of all the associated CaseIDs?

              • 4. Re: Searching Multiple Table Occurrences
                philmodjunk

                I really don't have a clear idea of how you have set up all your tables, occurrences and the resulting relationships. I have a general idea from your posts, but the details here are important. Please post a more detailed description of your table occurrences and their relationships.

                Also, knowing the big picture can be very helpful so I can avoid providing a solution that answers the specific question but fails to meet the needs of the underlying reason for asking the question in the first place. Why do you need such a a list? How will you use it?

                Finally, what version of FileMaker are you using? (FMP 12 may have a very useful alterntive approach to doing this.)

                • 5. Re: Searching Multiple Table Occurrences
                  MikeTrotter_1

                  I really appreciate your willingness to help. 

                  I am attaching the relationship graph for my file.

                  Ultimately, my goal is to present the user with a list of cases where the person has a roll as a Defendant, Victim, CoDefendant, ArrestingPerson, or Witness.

                  This is what I think I need to do:

                  1: Allow the user to search tbl_People for a certain person and assing the pk_PersonID to a $$PersonID variable once they select it.

                  2: Search tbl_Cases for fk_DefendantID = $$PersonID and return an array of matching pk_CaseIDs

                  3: I then want to do the same thing on all the Join tables to end with an array of pk_CaseIDs where $$PersonID ocurs.

                   

                  Lastly, I am using 11. I will get my copy of 12 next week at DevCon and will start learning that.

                   

                  Thanks again,

                  mike

                  • 6. Re: Searching Multiple Table Occurrences
                    philmodjunk

                    What you can do is a much simpler approach:

                    Perform the search on tbl_PeopleDefendants. The fact that the person may not be a defendant is irrelevant to this method.

                    Use Go To Related records to pull up a found set of Cases.

                    At this point, you have a found set listing all cases where that person's ID appears. A report based on this table can use filtered portals (filtering by PersonID) to show the reason for the fact that they are linked to that case. These portals can be stacked and set to slide up to reduce unused space when you print the report.

                    One idea you might play around with is to use one data source table for all for "join" table occurrences. This would require redesigning that table to have one ID field for each of your 4 occurrences shown on the far right of your relationship graph--any given join record would have data in one of these fields with the other three empty--but may make it possible to set up a summary report based on this unified join table that lists all case participants, grouped by category and for one or more cases.

                    Once you get FMP 12, and if you decide to use it, Execute SQL offers some options for pulling data from multiple tables into a single list that may prove useful for such reporting purposes.

                    • 7. Re: Searching Multiple Table Occurrences
                      MikeTrotter_1

                      I will experiment with the filtered portals. I like the idea of being able to see each of the roles instead of just seeing that they are involved. I am also looking forward to getting my hands on 12. SQL, finally...

                      Thanks

                      • 8. Re: Searching Multiple Table Occurrences
                        philmodjunk

                        You can download the 30 day free trial for 12 and play with it now. You'll find that while Execute SQL offers some nice options, the fact that it dumps it's returned value into a single field and can only use Select statements limits its utility. Still it offers hope for the future. I'd like to see an SQL option to use in place of a table occurrence name in layout and portal setups.