I suggest a major table restructuring.
Put your volunteers and members in one table. Add a field that identifies those records that belong to volunteers.
Now you won't have to search the membership table for volunteers. Instead, you can pull up a list of membership records and change their volunteer status simply by clicking a radio button or check box.
This would mean that when I want to associate a volunteer to an event, I would have to select a name from a table that will contain over 1000 records. Thats quite a drop-down list!
My membership table is the main table for the DB. I would have to enter a lot of dummy info in a membership record if someone who was not a member volunteered at one of our events - which happens often.
You can set up a conditional value list that only lists the volunteers from your table.
If volunteer status is by the event, you may also want to consider a join table that links an indifiduals record to a given event record.
You can have a field that uses a checkbox field to identify whether a person is a volunteer, a member or both.
You shouldn't need to enter dummy data for non-member volunteers. It should, instead, be possible to fill out just the relevant fields and leave the rest blank.
You may find it best to split your Main table into two parts, one table would be a contact table recording data on each individual and a second, related table just for recording membership info.
The main idea here is to move all name and contact info into the same table structure and then use labels to refer to specific subsets of your total list of individuals.
Matching "volunteer" records to membership records can be done, but you have to keep in mind that names are not unique and are subject to change.
Simply creating a new table occurrence of your membership table and linking it to a table occurrence for your volunteer table would allow you to quickly determine which volunteers are also members--as long as each individual's name is unique.
If Table Occurrence is a new term for you, you might check this tutorial on the subject:
I thought of the conditional list while eating lunch. It does make more sense as one most likely would want to keep contact info for volunteers and adding these fields to the volunteer table would be redundant. I'll explore this option, but I did originally have the volunteer table linked to the membership table. When I tried linking it to the event table, ( this is what I originally wanted - volunteeres associated to an event ) it wanted to create a table occurence. I have read about this, but I'm trying to not make things over complicated - at least to my current knowlege level of FMP. Thanks for the link - I'll take a look.
A classic many to many relationship appears to be in order:
Volunteer::VolunteerID = Volunteer_Event::VolunteerID
Events::EventID = Volunteer_Event::EventID
A portal to Volunteer_Event on a volunteers layout will list all events for which they have volunteered. A portal to Volunteer_Event on an Events layout will list all volunteers assigned to that event.
If you want a demo file for this, see this link, just replace c"ontracts" and "companies" with "volunteers" and "events":
This is a great idea! I assume that both relationships to the join table will enable addirtion of records. I never considered a portal on the volunteer table to show what events they participated in, but that is good info to have. I'll set this up this evening after I get home
Usually, you alllow creation of records and cascading deletes for the Join table, Volunteer_Event.
If you are on an event record and need to create a new volunteer record, that usually works best with a script and a button to create the new volunteer record so that it can be linked to a join table record.
If I wanted to add a volunteer that didn't exist to an event, would'nt I first add them to the membership table and mark them as a volunteer - going along with your suggestion to keep them there. I'm hoping I can go with what you told me in your post that suggested the join table and get a working solution.
My script writing skills have yet to be tested as I will soon embark on writing a script to import records from a temp file and add or update records in the membership table. You gave me advice on this earlier. Up untill now, I have ben desigining layouts and setting up the data base.
I really appreciate all your help.
Yes, that's the basic approach.
A script is not needed, it just comes in handy sometimes when you have a given event up and realize that you need to add a volunteer to the event but don't have a record for them. You would click a button and its script would create both the new volunteer record and the linking join record as well as taking you to the layout for entering the needed data for your volunteer. When you then return to the event layout, the new volunteer will already appear in your portal.
Regarding conditional lists, I searched the forum and found your post:
Following option 1 which seems to fit my needs, I created a field in the membership table called volunteers. I entered the following calculation: If ( IsVolunteer= "Yes"; First Name & " " & Last Name; "" ) Note that I previously set up the field "IsVolunteer" in the membership table and set a few records to "Yes"
I then created a value list based on this field called Volunteers
I then created a field in my Volunteer table called VolunteerList and set it up as a dropdown on theayout using the volunteers value list.
The list is not populated. What am I missing? I tried putting the dropdown on the membership layout, but still no luck.
What format is the IsVolunteer field?
If it is a check box field where two or more values may be selected you have to use a different expression than IsVolunteer = "Yes".
Did you specify text as this calculation field's return type?
Did you set this up with a text field and an auto-entered calculation? (This field won't automatically update for existing records.)
IsVolunteer is set up as a dropdown list tied to a value list that has Yes or No. I looked throught the forum posts relating to conditional lists, but I didn't see infop on the correct calculation.
I believe the calculation required that a text value be returned, but I need to check.
Yes - set up with text field and auto entered calculation. I added a new record afterward - maybe my problem is the caculation.
The auto-entered calculation is the problem. Make this a field of type calculation and it should work for you. The auto-entered calculation will only enter data for new records or records where one of the referenced fields was modified.
You could use Replace field contents with a calculation to put the correct data in all the existing records, but simply changing the field type is simpler.
OK thanks for the help on the list - I got it to work. Still having issues with the volunteer layout. I have the volunteers dropdown list and a dropdown list of events, but I'm not sure how the portal you suggested works when pointed to the join table. How does it populate event names that the current volunteer has been assigned in the portal rows? Also, I can assign a volunteer to an event more than once. I'll keep hacking at it.