3 Replies Latest reply on Jan 6, 2012 10:39 AM by philmodjunk

    How do I select one from many?


      How do I select one from many?


      I am trying to create a layout with data from multiple tables.  The database is to follow legal cases for a consultant. I created the database from Salesforce exported tables so the structure is not ideal.

      The table "cases" lists the basics of each case. There are mulitple "individuals" associated with each case (lawyers, doctors, consultants, etc.). These are stored in the "individuals" table.  There is another table "role" that stores the role for a particular individual for each case (e.g., plaintiff attorney, defense attorney, consultant). This table also has a checkbox field "isClient" to identifity the individual for each case is the "client" for my client.

      What I am trying to do is create a simple report for each case that lists a few specific elements about the case (this is easy and works) and also lists the client for that case (not easy).  I don't know how to do this. In other words, what I am trying to do is have the layout show ONE specific individual out of the multipleindividuals that may be associated with the case, using a checkbox field that identifies the individual.

      Any help would be appreciated!

        • 1. Re: How do I select one from many?

          I created the database from Salesforce exported tables so the structure is not ideal.

          Good table structure is vital. The time spent correcting issues with your database structure--the tables and relationships--will save you hours of struggling to get a bad structure to work for you.

          Is this the relationships that you have?


          Cases::CaseID = Role::CaseID
          Individuals::IndividualID = Role::IndividualID

          With Cases::CaseID and Individuals::IndividualID defined as auto-entered serial numbers?

          If so, you would seem to have a very good structure here--not one that I'd change.

          There are several options you can use to list just the client from the list of related individuals: a filtered relationship a filtered portal, or a layout base on the Role table.

          (1) A filtered portal is simplest to set up, but requires FileMaker 11.

          Add a one row portal to Role on your Cases layout.

          In portal setup, specify this portal filter:

          Role::CheckBoxField = "IsClient"

          You can then add fields from Individual to this one row portal to show the name or other info about the client.

          (2) A filtered relationship requires adding a field to Cases and adding a new occurrences of Role and Individual. Add a calculation field, constClient, set to return text. Use "IsClient" as its calculation expression. Go to Manage | Database | Relationships, click Role to select it then click the duplicate button (two green plus signs) to make anew occurrence of it. You can double click the new table occurrence to open a dialog where you can rename it. Do the same with Individual. Then you can define thiese new relationships:

          cases::CaseID = ClientRole::CaseID AND
          cases::constClient = ClientRole::CheckBoxField

          ClientRole::IndividualID = ClientIndividual::IndividualID

          In this case, you do not need a portal, you just add ClientIndividual::Name or any other fields from that table to your layout to display info about the client.

          (3) Instead of all that, just base your report layout on the Role table. Perform a find for the specified CaseID and "IsClient" to pull up the specific Role record for the client. You can add fields from Cases and Individuals to this layout to present the info that you need for your report.

          • 2. Re: How do I select one from many?

            Wow!  Thanks for the answer.  As a Filemaker newbie, I'm happy to say that yes the relationships and primary keys were set up as you suggested.

            I actually understand each of the solutions you offered. I did #3 which was the "duh" answer that I would never have thought of.

            I truly appreciate your answer and help with adding to my knowledge!

            • 3. Re: How do I select one from many?

              Don't feel too bad. That "duh" answer didn't pop to the front of my brain until after I'd typed most of the other text for options 1 and 2...Wink