Contacts, Events and Event Price relationship

[robbyo77]

Hi, this is my first post & I'm wanting to pull my hair out. I'm brand new with FM, so go easy on me. I've spent 17+ hours on VTC learning this program, but this relationship thing is killing me. Here's my scenario:

Tables

Contacts

Event_Registrations (join table)

Events (classes or trips)

Event_Fees (trips can have multiple fee levels)

I want to have a portal in Events to add Contacts (Event_Registrations) to the event. In that portal I want to choose the Contact's Name, select their Fee Level (Event_Fees) and have it enter the Fee associated with that fee level in the 3rd field.

I have the 4 tables related in the order they're listed above with primary & foreign keys.

Where I'm getting stuck is the choosing of the fee level & its associated price. What fields do I create in Event_Registrations if any? How do I get them to show up in the portal and get the price to change when I change the fee level?

This probably makes no sense, but if anyone can decipher it & help me out, I would GREATLY appreciate it. Thanks in advance!

[robbyo77]

Maybe if I laid out a real-world application, it would make sense.

I'm taking students on a ski trip. The relationship between CONTACTS (students) & EVENTS (ski trip) is many-to-many, so I have a join table called REGISTRATIONS.

My ski trip has multiple fee options which I've created in an EVENT FEES table. Example:

Student Skier - $299

Student Non-Renter - $269

Student Non-Skier - $183

I have a portal on my EVENT layout that allows me to enter the Fee_Type (Student Skier, etc.) and it's associated fee. 2 fields...simple.

I want to have a second portal on my EVENT layout that allows me to add REGISTRATIONS and the fee level they've chosen. Example:

Dave Jones (CONTACT), Student Skier, $183

Of course, I'll have multiple CONTACTS that have the same fee type.

I'm thinking I need more table occurrences, or calculation fields in REGISTRATIONS, but I can't figure out what the solution is. I don't want to have redundant storage either. Say I change the fee, I'd want it to update on each registration.

Right now I have 1 table occurrence for each table. CONTACTS to REGISTRATIONS to EVENTS to EVENT FEES.

Does this make more sense? Can anyone help?

Edited November 12, 2009 by Guest

[NathanAshton]

Hey mate, can you attach what you have done so far?

I've had a lot of help on this forum, so time I helped back :

[robbyo77]

Sure thing! I started with a free solution called FM Starting point (HUGE help), so try to ignore all the leftovers from that. I'm simply interested in the tables that are represented in the relationship diagram right now.

I created 2 events, 4 contacts & some event fees. If you can show me how to make it work, I'll be DEEPLY indebted!! Thanks for your response!

Students.fp7.zip

[robbyo77]

I think I know what to do, but I don't know how to write the calculation (or maybe I need a script).

In the REGISTRATION table I need a foreign key for the EVENT_FEE primary key. I'll set it to give me a drop-down list of the event fee primary key, but showing the event-type field contents. I also need a field that captures the event_type data from EVENT_FEES and a field for the fee.

My question is: how do I make those fields auto-enter only when the foreign key is entered or changed? Is this a calculation with a trigger? is it a script trigger? I have no idea how to do either one.

[NathanAshton]

I'm only a beginner too, so some things I say may not be 100% correct.

I fixed it up for you. You don't need any calculation fields in the Registrations table. All it needs to store is the contacts id, the event id and the fare type id. Thats the beauty of relationships :

I've only done a rough patch up job, but the functionality works ok. Let me know if it makes sense what Ive done.

Cheers

Students.fp7.zip

[robbyo77]

Bro, that makes perfect sense! Thanks a TON! It's one of those things where I've been too concerned with wanting to figure it out to see the simplest solution. Thank you so much for taking the time to teach me. I hope I can do this for someone else one day. Now I can sleep!!!!