This is my first question and my first project in the FileMaker Community, so please go easy on me
I'm working on a solution for a nonprofit involving the relationships one would expect:
- Persons can make gifts, which is a M:N relationship. One person can make many gifts and one gift can made by either one or two people. So I made a "Persons | Gifts" join table.
- Persons can attend events, which is also a M:N relationship. So I made a "Persons | Events" join table.
- Gifts can also be made in relation to events (but is not necessary). I treat this as a 1:M relationship, but not between the "Gifts" and "Events" TOs. Instead I place the 1:M relationship between the "Events" and "Persons | Gifts" TOs. As a result, the "Persons | Gifts" TO has three FKs: _fk_Event_ID; _fk_Person_ID; and _fk_Gift_ID.
(You may have noticed the circular relationships. I added the multiple TOs to get around it. Here is relevant part of the relationship graph)
On my "Gifts" layout, I have a portal where I can select up to 2 persons as donors. So far so good; on the "Persons | Gifts" layout in table view, it creates two records with unique FKs for each person and the same FK for the gift.
Here's where the issue is: On that same "Gifts" layout, I have a related field for the Event FK with a pop-up menu. I can select any event I want, but if I check the "Persons | Gifts" layout in table view, only one those two records I mentioned in the previous paragraph displays the related event. This becomes a problem when I want to use portals on an "events" layout and certain records are missing.
Curiously, if I turn that related field with the Event FK into a portal, and then select the same event twice, then both persons wind up with the desired event in the join table. The problem with that is it becomes possible to select two different events in the portal, which violates the business rule.
I'm not sure how I can best fix this, and would welcome any suggestions if anyone has had a similar experience. I suspect the join table with three foreign keys might be the culprit, but I'm just grasping at straws there. Happy to provide additional screenshots if it helps, but I wanted to preserve some brevity in this post.
Thanks in advance!