MTASCP

Relationship structure problem

Discussion created by MTASCP on Jan 31, 2017
Latest reply on Feb 9, 2017 by MTASCP

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

Outcomes