10 Replies Latest reply on Jan 11, 2012 11:04 AM by philmodjunk

    Many To Many Relationship Help



      Many To Many Relationship Help


       Hey Everyone,

        I have a table called Client Profile and then three other tables.

      Tables: App1, App2, and App3  

      App 1 and App 2 I have related to client profile database with the clients social security number. I have portal windows to show related records to those applications, however the trouble is App3. App3 is already related to another table so when i try to relate it to client profile. It wants me to create another occurrence of client profile. I'm confused on how this is going to sync. What do i do ?

        • 1. Re: Many To Many Relationship Help

          I don't see a many to many relationship here. FileMaker will not permit a "cycle" in the relationships so when you specify a relationship that would produce that, it offers to create a duplicate "instance" (elsewhere called an "occurrence" ) of one of the two tables you are linking in the new relationship. This may or may not be what you need. (Sometimes you need to duplicate the occurrence of the other table.)

          You'd need to tell us more about the relationships you have the purpose/function of your database before we can advise you further. The fact that you have thee tables with nearly identical names suggest that you may have 3 tables where you only need 1 and that such a change might improve the function/structure of your database. (I may change that suggestion when I learn more about your database, however.)

          • 2. Re: Many To Many Relationship Help

            You have two options to get this relationship set up. Make a new table occurrence of Client Profile and link it to Medicaid Calc App... or make a new occurrence of Medicad Calc App... and link it to client Profile.

            The best choice depends on why you need to add this relationship. Example, if you want a portal of records from Medicaid Calc App... on your Client Profile layout, make a new occurrence of Medicate Calc App... and create a portal to this new occurrence. If you want to add fields from Client Profile to your Medicaid Calc App... layout, make a new occurrence of Client Profile, link it to Medicaid Calc App... and select fields from it for your Medicaid Calc App... layout. Note that these options are not mutually exclusive. You can do both.

            To create a new table occurrence, open Manage | Database | Relationships and select one of the "boxes" by clicking it. Then click the duplicate button (two green plus signs). You can then double click this new occurrence box to open a dialog box where you can rename it to something more descriptive than the name of the original occurrence box plus a number.

            Here's a tutorial thread on table occurrences you may find useful to better understand what table occurrences are and how to use them: Tutorial: What are Table Occurrences?

            Once that makes sense, you may find this article on Anchor Buoy useful: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

            Further comments:

            Item 1

            I don't recommend using social security numbers as a primary key like you have for two reasons:

            1) SSN's are often exploited to commit identity theft. Putting them in so many parts of your database can be a security risk.
            2) While SSN's are supposed to be unique, they really aren't. Some individuals (Typically undocumented immigrants), will give fraudulant SSN's. WHen this happens, you can get two individuals with the same SSN. When you have used this number as your primary key, such an event will create problems for you when you attempt to correct the issue.

            Thus, it's better to record the SSN in the client profile and no where else. Use an auto-entered serial number generated in the Client Profile table to link your other tables. Your database will function the same, but without the above issues.

            Item 2

            I see address fields in multiple tables. Assuming that this is the same address for all records in all tables with the same SSN (hopefully to be replaced with SerialNumber), these duplicate fields should be removed from all tables except the client profile. You can use relationships back to the Client Profile record whenever you need to display, search for or sort on an address. This advice should not be followed if the address info represents the client's address at a specific point in time and needs to be kept as such a "snapshot" so that future changes in address do not change the data stored in that record.

            • 3. Re: Many To Many Relationship Help

              I created a new occurance for Medicaid Calc App Main and linked it to Client Profile. However how does the 2nd occurance get the data from the first occurance cause right now even though they are linked... im still not seeing that data in the portal.

              • 4. Re: Many To Many Relationship Help

                You have not duplicated any table nor have you duplicated any data. The "boxes" you see on this screen are convenient "labels" used so you can create relationships linking various tables. If you hover the mouse over the upper left corner of the box, you should see a pop up appear that lists the same data source table for both occurrences.

                Please check out the tutorial on table occurrences.This takes you through a much more detailed explanation of this concept and provides some useful ways you can exploit multiple occurrences of the same table to do more things with your database.

                • 5. Re: Many To Many Relationship Help

                   Phil the portal wont display the records that i have link to the 2nd occurance however i have the ssn field set as a button to go to that related record and open on that records layout. If i click on the field it will actually take me to the record but its not showing in the portal the ssn . Its just blank.

                  • 6. Re: Many To Many Relationship Help

                     I figured it out phil . Thank you. Just had to delete the portal and recreate it. Didnt like me just changing the settings around.

                    • 7. Re: Many To Many Relationship Help

                      Both portal and the fields inside of it must be specified to be "from" the correct table occurrence(s).

                      • 8. Re: Many To Many Relationship Help

                         I hit the wrong button phil and hit my post as best answer. I cant undo it . Any idea how i do this?

                        • 9. Re: Many To Many Relationship Help


                          • 10. Re: Many To Many Relationship Help

                            Don't worry about it. It's not worth the trouble to change. (we have to ask a FileMaker Inc Ts person to fix it.)