10 Replies Latest reply on Dec 14, 2009 12:46 PM by johnhorner

    Filtering "parent" specific information from "child" record

    johnhorner

      Title

      Filtering "parent" specific information from "child" record

      Post

      I have a contacts database with a table for "companies" and another table for "individuals".  i currenty have it set up so that a portal displays all the individuals related to a given company (as employees or members, for example).  one of the fields in the portal is for notes about the individual's role or relationship to this company or other relevant information.  this is also not a problem.  the problem is that, in some cases, an individual will be linked to more than one company so that if he is the "ceo" at his own company, he is merely a "member" at the local gym.  does anyone have a good method for setting it up so that the appropriate note will show up in the portal depending on whether i am looking at the record/portal for his own company vs the record/portal for the gym (e.g. in one case the note should read: "bob is the ceo", and in the other case it should read: "bob is a member who can bench 250lbs")?  any thoughs much appreciateed.  thanks.

        • 1. Re: Filtering "parent" specific information from "child" record
          philmodjunk
            

          This reads like a classic "many to many" relationship problem. A given personnel record can be linked to many companies and a given company record can be linked to many Personnel records.

           

          The solution is to define a Join table here:


          Your relationships would look like this:

          Personnel:: PersonnelID = CompPerson:: PersonnelID

          Company::CompanyID = CompPerson:: CompanyID

           

          Thus the CompPerson "join" table forms the link between the two tables.

           

          Make your notes field a text field in the CompPerson table.

          Make your Personnel portal on your Company layout point to CompPerson and include related tables from Personnel in this portal row.

           

          Include the notes field in this row also.

           

          That should do it.

          • 2. Re: Filtering "parent" specific information from "child" record
            johnhorner
              

            thanks for the response.  i have tried to implement it as you outlined but i am getting very strange behavior.  here are some of the symptoms:

             

            1.  the first test individual that i linked to a test company shows up twice in the portal but the note only displays in the first portal row

            2.  even though i have selected to allow for creation of related records in the portal, i am unable to enter a new employee in the portal.

            3.  if i create another individual and assign them to the same company and write a note to go with it (all from the individual's layout), they don't show up at all in the portal on the company layout.

             

            one of the main concepts i don't understand is if the portal is set up to display the notes "join" table (the one you call "CompPerson", how could it display any individuals if they don't have a note already established and, therefore, a record from which to join?

             

            on a somewhat related note, do you know if having 2 separate tables to manage contacts (e.g. companies and individuals) is the best way to do this, or would it be preferable to simply have a single contact table that could contain either a company or an individual?

             

            hope you can help me get this working... thanks!

            • 3. Re: Filtering "parent" specific information from "child" record
              philmodjunk
                

              I don't think you've got your relationship between companies and CompPerson set up correctly and that's the source of most of your issues.

               

              1.  the first test individual that i linked to a test company shows up twice in the portal but the note only displays in the first portal row

              Each individual should only be listed once in the portal. The second instance shows that you have a second related CompPerson record and this should not be.

               

              3.  if i create another individual and assign them to the same company and write a note to go with it (all from the individual's layout), they don't show up at all in the portal on the company layout.

              How are you assigning them to the same company? You have to create a new CompPerson record with their PersonnelID number entered into the CompPerson:: PersonnelID field. If "Allow create" is enabled for CompPerson, This is done simply by entering/selecting there PersonnelID number in the CompPerson:: PersonnelID field.

               

              2.  even though i have selected to allow for creation of related records in the portal, i am unable to enter a new employee in the portal.

              That is an expected limitation of this set up. You have to create the new Personnel record in the Personnel table first, then use the portal to create a new CompPerson record on the Companies  layout. There are ways to script this process to make it easier for the user.

               

              Some basic facts to keep in mind:

               

              CompPerson has at least three fields:

              CompanyID

              PersonnelID

              Notes

               

              If you place a portal To CompPerson on your Companies layout, you can assign existing personnel records to the current company record. If you place a portal to CompPerson on your Personnel layout, you can assign an existing company record to your current personnel record. In both cases, creating the related CompPerson record takes place by entering data in a blank portal row (The allow creation of related records option automates this for you), The data you are entering is the ID number linking to the "other" table. You can format this field as a drop down or pop-up menu that displays the ID numbers and Names of either the Personnel or Company table to make entering the number easier.

              • 4. Re: Filtering "parent" specific information from "child" record
                johnhorner
                  

                thanks for all the help.  i understand now how your solution works.  i think it is probably not the ideal solution for my particular situation as the convenience of being able to enter a new individual directly into the portal is fairly essential.  but i think with a little tinkering i can use what you have taught me about join tables to craft a variation.

                 

                did you have any thoughts about 1 table vs 2 tables for contact management?

                 

                thanks again!

                • 5. Re: Filtering "parent" specific information from "child" record
                  philmodjunk
                    

                  "i think it is probably not the ideal solution for my particular situation as the convenience of being able to enter a new individual directly into the portal is fairly essential."

                  It's fairly easy to enable this through a little scripting. In one of my solutions I put a button for adding the new related record. The button's script then works something like this:

                   

                  Set Variable [$companyID; Value: Companies::CompanyID]

                  Go To Layout [Personnel (Personnel)]

                  New Record/Request

                  Set Field [Personnel::companyid; $CompanyID]

                  Go To Layout [Original layout]

                  Commit Record

                  Go to Portal row [Last]

                  Go To Field [Personnel::Name]

                   

                  (If you sort your portal alpabetically by name, go to the first portal row instead of the last).

                   

                  "did you have any thoughts about 1 table vs 2 tables for contact management?"

                  The last contact manager I set up had a lot more than just 2 tables. This is one use for a database where you really, really want to set up properly normalized tables with relationships based on keys that are not subject to change (don't use name fields for the match fields.)

                  • 6. Re: Filtering "parent" specific information from "child" record
                    johnhorner
                      

                    thanks for the scripting tip.  i will definitely give that a try.  it seems a little more elegant than the somewhat involved workaround i devised over the weekend.

                     

                    as for the table question, i do have several tables that are utilized for the overall contact management solution (such as addresses, phone numbers, contact history, etc), but i was wondering more specifically about the contacts themselves.  on the one hand, it seems to make more sense to have a single table for all contacts regardless of whether tey are a company or an individual since, afterr all, they are all "contacts" (it seems that it also makes it easier to search and navigate one table rather than 2 when looking for a contact), on the other hand, since a company has somewhat different information associated with it than an individual does (such as their birthday), it really requires 2 separate layouts to display the appropriate info.  now that i have written this out and thought more about it, it seems like there would not necessarily be one correct answer but that the decision to use 1 or 2 (or more) tables to split up contacts into categories (e.g company or individual) would depend on the ultimate purpose of the database.  thanks again for helping me and teaching me some new things about filemaker!

                    • 7. Re: Filtering "parent" specific information from "child" record
                      philmodjunk
                         My preference would be to use a single table for this kind of data. You can always have a "company" layout and and "Person" layout with a field in your table used to determine which contact record is a "person" and which is a "company" if you need to do it this way. With script triggers in FMP 10, you can even make your layouts switch back and forth between the two formats as you click through the different records.
                      • 8. Re: Filtering "parent" specific information from "child" record
                        johnhorner
                           thanks... i have this database which has been slowly evolving for many years but which was initially set up with contacts divided between 2 different tables.  i have recently been considering consolidating them, but before i did so i wanted to make sure that someone with more experience than myself approved of the single table method.  now, i am going to do it, and then incorporate your join table method and use your scripting technique for adding records to the portal.  i really apreciate all the help!
                        • 9. Re: Filtering "parent" specific information from "child" record
                          philmodjunk
                            

                          I've been there and then some.

                           

                          You create a database and it gets put to use. Over time, you insert different features that your users have requested. At the same time, your user's business model evolves and your understanding of Filemaker and Databases grow and then Filemaker puts out several new versions with new features that allow you do new things or to do old things in a new and better way...

                           

                          Eventually, you have to step back from the database and look it over very carefully to see if you need to peel some layers back--or even completely redesign the system in order to make sure you are really improving the database and not just "bolting on more shiny parts...":smileywink: