A design query: I can't see what relevance Address data has to Days/Dates. Should that not be in its own table? And if it was, would that begin to address the issue you raise?
Re: Relevance of address to days/Dates. First of all, we have a list of volunteers, several hundred of them; which makes for a very large drop down list. That's one table
In any given weekend, we have up to 15 houses under construction, and of course a house is built over a series of days. The available work days on every house are in another table; which includes the address of the house, and the days available for work. That's the relevance of days and addresses
The joining table creates the many to many relationship that allows our volunteers to work as much as they want. This is where I'm trying to limit the contents of the drop down list. If someone is trying organize that many volunteers between that many houses, it would be nice if I could remove names from the list that are already signed up somewhere else that day.
So I'm trying to find a way to use the entire volunteer list, BUT...remove those who are assigned to any house on the date to which my portal refers. For instance. If someone tells us they are available for every Saturday in March, and there are 6 houses building throughout march, then they could be looking at a screen just chock full of portals and names. It would be so easy to assign a person to multiple houses because you didn't see that they'd already been assigned elsewhere. So I just want to take their name out of the list for days in which they've signed up. Doe that make sense?
Thanks Garry. At this time SQL is a bit beyond my skillset. But I'll slog through it. I can see it being very helpful. I could imagine when they click on the drop down list I'd create a variable for the date, then ask for all the names in the volunteer table that are NOT in the LinkWorkers table that have a value of this date. Does that sound kind of like where you were pointing me?
Is there a way to do that with Filemaker relationships, without having to learn a new language? (it's all Greek to me, and I took Latin. Or should I say I flunked latin)
This is really a question of how you denote and store a volunteers participation for given dates.
If you work on a day-by-day basis, and have a table where each entry is a join table of projectID/volunteerID/date, then you could find all available “assets” for a given day by using a relationship chain like
someTable::gTheDate = Join::date / Join::volunteerID = Volunteers::id / Volunteers::id ≠ Volunteers_otherTO::id
so that on a layout of someTable a portal into Volunteers_otherTO would show you all available volunteers for gTheDate.
You could do (about) the same by using ExecuteSQL(), to
• SELECT (a list of) volunteerIDs FROM the join table WHERE the date = gTheDate
• SELECT (a list of) volunteerIDs FROM the volunteers table WHERE the volunteerID is (NOT IN) the other list (and status equals 'active', or whatever other criteria are necessary to whittle down that list)
Put these IDs into a global field that drives a relationship into the Volunteers table. Might be a bit snappier, and only requires one TO.
PS: Unless there are considerations at play you haven't named (vicinity of volunteer address / project address?), like keywords I fail to see the relevance of addresses for this requirement.
You are thinking in the right direction.. With SQL, you can bring back a set of records or values from any table and you don;t need a relationship. If you create a query that isolates the group of records that you want to select from, you can set those values in a global field as a carriage returned list, which you can refer to as the source for your value list.
So if you wanted to show available dates, (or volunteers) you would want to set the resultant volunteer names in the global text field. You would then be able to view them in your value list.
Although it may seem daunting, learning the basics of SQL will give you such a reward and make many complex relationships unnecessary.
Just to consider a very basic situation (although there are obviously alternative FileMaker methods of doing this).
If you want a field to show a list of names from a customer table with SQL, you just bring back all the names from that table in a simple query. If you then want to choose the names from a supplier / vendor table, you just execute a different query targeting the supplier / vendor table and set the same text field as you did when you queried the customer table..
No relationships, one global and one text field to use for your value list selection.
You could have a look at this: