I think you have a many to many relationship issue here. Check me on this first so I don't type in a big chunk of text only to discover I'm misunderstanding.
Any given event can be linked to any number of venues (in contacts table)?
And any given venue (contact) can be linked to any number of contacts?
I see the second part of that statement is true, but am unsure that the first part is true.
I think I had better try and build a better picture of what I am doing, so that you can see what I am trying to achieve, as I don;t think I did a very good job in the first post?
I already have a contact ID for the contacts, and also for the events.
You made a good point - it will probably be a many to many as for example;
• Drake Circus (Venue) has three events coming up in the next 9 months on three different dates (two pool events and one collection)..
• Each event will have volunteers, so "Fred Tandy" can volunteer at more than one
• Each venue can have more than one event, and one event can have more than one contact
It's a little baffling - I have a great layout, but I might have set it up all wrong?
I have 5 tables;
• Correspondence History (works fine)
• CPE_Events (Contains all the event details such as Event_Date_Start, etc - 82 fields)
• General_Contact_Info - For all contacts, including venues as many venues are also my contacts, and there's just one person per venue who I must contact - 254 fields
• Role_Description - Just for out volunteer roles (works fine)
The trick, obviously, is to get the whole thing working well together as we will need to retain a lot of information..
Basically, we have many events going on, and we are always appealing for volunteers, so we need the ability to keep track of all the events and volunteers as well as being able to send appeals/newsletters etc out to the right people.
I started off well, but may have lost my way a little!
PS - Where are you based? I know some people speak via telephone to discuss things, but I can't do that what with being deaf, but I would be willing to travel to listen to the experts' advice. I am based in Stoke on Trent..
I'm in California--just a few hours drive from FileMaker Inc.
I see not one but several many to many relationships. I'm going to pretend you have a table named "volunteers" for this example. It's possible that your volunteers are listed in contacts, (that's where I'd put them), and if you have, this example will still work, you just have Volunteers use Contacts as its data source table and use ContactID in place of VolunteerID.
In your relationship graph, you'd have this relationship:
Volunteers::VolunteerID = Events_Volunteers::VolunteerID
CPE_Events::EventID = Events_Volunteers::EventID
This allows you to link a volunteer record to any number of Events and a given event can list all the volunteers for that event. That's a many to many relationship. You can place a portal to Event_Volunteers on a Volunteers layout and include fields from CPE_Events to list all events for which that volunteer has volunteered. You can place a portal to Event_Volunteers on a CPE_Events layout, include fields from volunteers and it will list all volunteers for that event. You can include a Role field in Events_Volunteers to document the role for that specific match up of event and volunteer. (That way the same volunteer and fill different roles for each event.)
Here's a demo file that links contracts to companies in this fashion that you can download and examine: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html
Yes, I see now where you are coming from and it makes sense (I think).
I tried to download the file you pointed me to but to no avail. It seems I have to sign up to this just to get the download?
Is it worth it? (considering the charity will pick up the tab) and are there lots of filemaker sample files on there?
A file to download would be great as like you said I could open and examine it..
In the meantime, I will keep trying, looking at your input.
Sorry - my apologies - I have joined the free plan as I didn't see it, sorry..
Actually, you don't have to join anything. Just click the blue download button and wait 20 seconds for the download link to appear.
Sometimes the download page includes deliberately misleading links to other sites or to get their software--none of which is needed just to get the file.