6 Replies Latest reply on Dec 30, 2014 9:25 AM by Garryt

    filter records available for a value list


      Hey Thrill seekers,

      I'm trying to create a sign in sheet for our local Habitat Volunteers.  Let's say I've got three tables, Days, Volunteers, and LinkWorkers.


      Days has Address and Date values that list what dates we are working on which houses.  We can have several houses running at the same time.


      Volunteers has their names and contact poop.


      LinkWorkers joins the two so someone can sign up for as many days as they are willing to work, God bless-em.


      I've got a sign in sheet that lists the address, then a portal that shows records from LInkWorkers.  The Name fields is a value list that shows all the names from the volunteers table.  What I'm trying to do is remove names from the value list if they have already signed up for that day.


      So if I've signed myself up to work on 1/1/15 at 1111first street, and someone wants to assign me to another house not realizing I've already signed up for a house my name will be removed from the value list.  BUT...it will be available for another date where I'm not signed up.


      Does that make sense to ya'll and do you have any idea how to pull it off?


      Thanks in advance for your expertise. 

        • 1. Re: filter records available for a value list

          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?

          • 2. Re: filter records available for a value list

            There was quite a lengthy discussion about value lists in an earlier thread..


            Check it out and see if it helps..


            How to create a value list just form some certain records of a table?



            • 3. Re: filter records available for a value list

              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?

              • 4. Re: filter records available for a value list

                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)

                • 5. Re: filter records available for a value list

                  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.

                  • 6. Re: filter records available for a value list

                    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: