Relationships / Tables mess.
I'm new to this - using FM Pro 12 (that's all they gave me, folks).
I'm setting up a workshop registration database. It has four tables: Workshops, Participants, Registrations, and Commerce Items. Each participant can be registered for multiple workshops, each workshop has multiple participants, and each workshop has various commerce items - basically the registration fees.
The problem is that my relationships chart is starting to resemble the Pan Am route map circa 1972 and various and sundry functions aren't working properly, and when I fix one, another one breaks.
- Participants has _pkParticipantID, which is an _fk in Registrations
- Workshops has _pkWorkshopID, which is an _fk in Commerce and Registrations
The glue that holds everything together is the Registrations table - here I have it set to assign participants to workshops, based on the commerce available for any given workshop. At first glance, it looks great - when I register a participant for a workshop, they appear in a nice ordered list on the workshop record, and the workshop appears on the participant record.
However, things that I've discovered I can't do:
1) I want to include invoice data on the Registration record.
When I select the commerce item - which successfully populates a pull down menu, filtering for only the commerce items that are assigned to the selected workshop - I want the Cost field from the Commerce table to populate the Amount Due field on the registration table, and I cannot seem to make this happen. I've tried a script to copy the data on selection -- nada. It always selects the first commerce item associated with the workshop, but I can't seem to get it to do a match on the _fkCommerceID on the Registrations table to the _pkCommerce ID on the Commerce Items table and copy the value from that record.
The two tables don't have a direct relationship - they go through the workshop table. I tried creating a direct relationship with a second occurrence of the Commerce Items table but it didn't seem to help. I am stumped here.
2) One of the things I'm trying to do as an "at a glance" screen on the workshop field is, for example, this.
I have a "Dietary Restrictions" field that counts the number of registrants who are Vegan, Vegetarian, Gluten-free, etc., so that the person who handles catering will have this info quickly. It's a series of check boxes on the Participant table.
I have two fields set up for this in the Workshop table, one, cDietaryVegan is a calculation = FilterValues ( Participant::Dietary Restrictons ; "Vegan" ), and the second - which appears on the record, is cVeganCount, which is a Count of cDietaryVegan. However, it returns a count of all participants who have the box checked (it's not restricting only to participants in a given workshop, even though it's on the workshop record page (not in a portal)). Again, I'm wracking my brain trying to figure out how to get this to work properly.
I had a midnight brain flash that I should probably have a _pkTransactionID, but it would require new Table Occurrences, at which point the relationship graph starts to look like spaghetti and I am left with the nagging suspicion that I am vastly overcomplicating this.
Or am I? Can anyone talk me through this? I can post screenshots, etc.