1 2 Previous Next 16 Replies Latest reply on Feb 9, 2017 10:02 AM by MTASCP

    Relationship structure problem


      I am using FileMaker 15 Advanced.  I am trying to build a database to track bills (state legislature) and contacts.  Most of my experience has been with simple solutions and have been successful but I can't seem get this database remotely started.  I hope you can help and here's the issue.


      The contacts are assigned to many states and the documents I need to track are assigned to one state or can apply to all states.  The idea is to download the documents from the web and upload those into filemaker, create a list of bills  by state  and send the  information to the contact by either text or email.  I also want to be able to add new contacts and maintain the existing contact information.


      I read and viewed many training videos and went through the filemaker training videos and created several scenarios that when applied in filemaker will either pull the contacts by state or the bill by state but I can't seem to bring the two together.  I can't seem to see the forest for the trees.


      First attempt:  Created the Contact table, State Table, and Bill Table.   Relationship was Contact ID and the Bill ID were connected by a joint table called State.  I could generate a list of contacts by state or bill by state.  I could  neveresee one contact with all the state bills the contact was responsible for.  Meaning Contact Mary Ann Jones is responsible for Maine, Kentucky and Indiana. Two new bills were recently uploaded in filemaker.  One for Maine and one for Kentucky.  I would upload those into filemaker and create a list for the contact these bills would be listed and then emailed or text to Mary Ann.


      Second attempt:  I created the Contact Table and the Bill Table.  I followed the thinking of a training video I had watched where two join tables were created.  Contact table with a join table for the contact to state,  second join table to create a relationship contact to state to the bill.  End result looked like this. 

      Could anyone tell me what I have done wrong?

      Screen Shot 2017-02-01 at 12.14.47 AM.png

        • 1. Re: Relationship structure problem

          Seems like you need one join table rather than two, but which links to state, contact and bills by their respective IDS.


          A summary report based on this join table would then produce the desired report.

          • 2. Re: Relationship structure problem

            Sorry, I just saw your thread, and it looks like something I can contribute. Let me see if I can get you a sample ERD.

            • 3. Re: Relationship structure problem

              I thought that too  but some how I seem to have messed it up.  I am going to set it up again and post it this evening.  Maybe I just missed a step.

              • 4. Re: Relationship structure problem

                I would really appreciate that!!  I saw philmodjunk's post as well and I am going to set up the one join table and post.  Hopefully, it will be something simple I missed.

                • 5. Re: Relationship structure problem

                  sometimes multi-predicate relationships are the solution.

                  1 of 1 people found this helpful
                  • 6. Re: Relationship structure problem

                    Sometimes, but not always, a join file that links to many tables can solve problems (and create them...)


                    The join table can create many records and many relationships.


                    ID Number








                    You can create a dashboard with each related table shown in a portal. The JoinTable record shows its ID and description and other info you might add.


                    Thus one or more clients can be related to one or more documents and one or more states, etc. via one record.

                    1 of 1 people found this helpful
                    • 7. Re: Relationship structure problem

                      ok, I'm not sure what multi predicate relationships mean. Is there a video or discussion to explain this?

                      • 8. Re: Relationship structure problem

                        "Multi-Predicate" means more than one pair of match fields for one relationship.

                        1 of 1 people found this helpful
                        • 9. Re: Relationship structure problem

                          I reconfigured the tables to just contacts, bills and states.  What is hanging me up is one contact is responsible for many states and the bills are usually related to one state or can relate to all (which I can create another field for all states).  The contact to many states is the problem child. 

                          Here is an example of the contact table.  So my contacts could be thought of as a team.


                          First NameLast NamePhone NumberState 1state 2state 3state 4state 5state 6state 7state 8state 9state 10state 11state 12state 13state 14state 15state 16
                          Mary AnnJones555-234-5678KentuckyMissouriOklahoma
                          Thomas Smith531-345-8979Rhode IslandIndianaKentuckyGeorgiaMississippi
                          JohnThomas101-234-7865KentuckyKansasWyomingRhode Island

                          I was able to create a layout by state and show each contact that belonged to the individual state but when I added the bills I could not find a way to associate the bill to the state layout.  Does this make sense?

                          • 10. Re: Relationship structure problem

                            ok, that seems like a good solution.  I have not had to create a relationship like that before.  How are those created?

                            • 11. Re: Relationship structure problem

                              I'll leave that to @siplus, as he made the suggestion. Here's how you might use a single join table.


                              What is hanging me up is one contact is responsible for many states

                              Does the link from Contact to states stay exactly the same no matter the bill? Or will one contact link to one list of states for one bill and a different list for another? (Even if this is not usual, is it at all possible?)

                              • 12. Re: Relationship structure problem

                                Several contacts will relate to one state and the bills will either be for one state or for all states



                                Team Kentucky may have 5 contacts.  When I upload a new list of bills, I want to be able to email the new bills (if any) to the Kentucky team.  Does that make sense?

                                • 13. Re: Relationship structure problem

                                  it doesn't answer my question.


                                  IF contact A is linked to Delaware, New Jersey and New York


                                  will Contact A ALWAYS be linked to those three and only those three states for the foreseeable future?


                                  Or might Contact A be linked to New York for Bill 123 and linked to New Jersey and Delaware for Bill 456?


                                  What I am exploring is whether Only contacts link to states or whether a combination of state and bill links to states.

                                  • 14. Re: Relationship structure problem

                                    Here's an idea I am working on that is similar to your question.




                                    My requirement is that I be able to link my company file and person file to the LINKS fle and then to the Tasks file so that I can find the task from either file. Then the task file must show either or both labels from the company or person file.


                                    This allows one company to have many employees and for one person to have relationships with many companies. It takes more work than not using the inbetween file, much more and requires extensive scripting to create the related records. But this is what you are asking for and you will have to experience a bit of pain, lots of frustration and then the exhilaration of victory.

                                    1 2 Previous Next