katkramer Posted April 9, 2008 Share Posted April 9, 2008 I've been using FM for MANY years, however, until recently I used lookup fields, separate FM files, and repeating fields because I never understood portals and tables. So fairly strong FM experience, but a little weak here. I need help! I have been working on a database for my construction company, and we are tracking project info, the subs that worked on the project, and warranty issues. However, it's not linking things correctly, and the portals are not showing the info correctly. I think the problem is within how I have the relationships created. Each project record has basic info about the project Each project can have multiple subcontractors Each project will have warranty issues after completion Each of these issues is assigned to the subcontractor who worked on it As I understand it: The project-subcontractor is a many-to-many relationship, because there are many projects with multiple subs and many subs that work on multiple projects. The project-issues is a one-to-many relationship. One project may have multiple issues, but each issue belongs to only one project. The issue-subcontractor is a one-to-many relationship. Each issue belongs to one subcontractor, but each sub may have multiple issues. So I have the project linked to the subcontractors with a linking/tween table (I found this technique in a book), then linked the subs with the issues. From the book I understand that the project will automatically be linked with the issues in a "pass-through" relationship. I've included the some screen shots, or you can view them in a web page at http://www.glasskatz.com/filemaker.html. (Posting these graphics quickly tweaked my web design on this page...I'm aware of it!) This page has larger graphics than what I uploaded here. I enclosed the FM file, in which I went back to the basics to try and get the relationship right. I've got the relationships created so that new records can be created and deleted through the relationship. If you're good at this kind of thing...you might open the file and check it out... I've got to head out to a meeting, but I'll post more after lunch. Link to comment Share on other sites More sharing options...
Ender Posted April 9, 2008 Share Posted April 9, 2008 Hey kat, I think the problem was a matter of context. Adding additional TOs and using those to look into tables from the other side seemed to solve it. However, I believe your one-to-many between Projects and Issues is incorrectly represented. See my v2 version of your file for my take on how it might be done. Link to comment Share on other sites More sharing options...
katkramer Posted April 9, 2008 Author Share Posted April 9, 2008 I will take a look at that...thanks for your prompt reply! A friend of mine who is a SQL DB developer sent me a diagram of how this would work in a SQL world... Link to comment Share on other sites More sharing options...
Ender Posted April 9, 2008 Share Posted April 9, 2008 Ya, your ERD looks about right. I missed that one-to-many between Sub and Issues. As v2 is now, multiple Subs can be implicated in each Issue. If you only want one Sub per Issue, you can remove the Sub_Issue table and link directly between Sub and Issues by adding a match key in Issues for SubID. Then tinker with the value lists and layouts as necessary. Link to comment Share on other sites More sharing options...
katkramer Posted April 9, 2008 Author Share Posted April 9, 2008 I'll play with that too...there IS a possibility of having multiple subs ASSIGNED to an issue. With my database as it is now (not the sample one I posted), EVERY issue related to a project shows up on every sub. No wonder I don't play video games...FM gives me hours of FUN!! Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts