I'm going to include my standard list of links on conditional value lists at the end of this post in case you've missed one of them and for others who might read this thread. A conditional value list is indeed the way to get what you need here. There are two approaches you can take to get a conditional value list that meets what you have specified:
You can define a calculation field that is empty for unavailable drivers, but which returns the driver's name if they are available. This field is then used as the second field in the value list in place of the driver's name field.
You can define a relationship that matches only to available drivers.
Either method works. They are referred two as "Option 1" and "Option 2" in the Forum Tutorial link. Feel free to describe what you've tried and exactly where it has gone wrong for you if you would like additional help with your value list.
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.
The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Thanks for the help. I'm going to digest these links and if I'm still stuck or lost with anything I'll come back to you.
I have now had a chance to look at and digest the links and info you kindly sent me. Unfortunately I still cannot make this conditional value list work BUT I think it may be something more fundamental. I think these lists all hinge on Relationship Tables and Table Occurrences, (which I now understand their differences after having read an adjoining article off one of your links).
I think part of my problem maybe that one of the relationships is broken and I don't understand why, or how to fix it. I have three tables which link to the main layout / Table via the foreign keys and primary keys. Two of these tables work fine and display all the relevant data using the _fk and _pk keys, (destination table and Clients table) but the drivers table doesn't and yet the setup is exactly the same. The only difference is that the data trying to be displayed comes from a drop down list box and a value list (see attached document which I hope explains / shows this) but I don't see how this affects it. The only way I can get this field on the main form to display the data from the drivers table is to delete the .pk key and .fk key in the Relationship graph and point the drivers field to the Journey::Driver field and not the Driver::Drivers field. This I think is preventing the Conditional value list from working and plus the fact I was also looking at the problem from the wrong way round. I think I should be using the Driver Status field to show the list of available drivers and not, as I was trying to do, to just show the drivers that were available. I hope all the attached info shows /displays the problem and that you may be able to tell me where I've gone wrong.
The most obvious problem lies in the design of your value list. The value list is entering what appears to be the driver's name (Driver::Driver) instead of the Driver's ID (Driver::_pkDriverID). But that is not the only issue. You also have selected the wrong field to format with this drop down list so you are both entering the wrong data and entering it into the wrong field. Your screenshots show that you have formatted the Driver::Driver field with this value list instead of the Journey::_fkDriverID field and then the value list should specify Driver::_pkDriverID as the first field and Driver::Driver as the second so that you can see the driver's name, but selecting a driver actually enters the Driver ID.
Once that is working, you can take a stab at updating the value list to set it up as a conditional value list that only lists available drivers.
Have followed your instructions and yes it all works fine now, except now it shows the driver ID instead of name now. Is there anyway I can now display the drivers name instead of ID in the dropdown?
When you set up your value list, under 'Edit Value List', did you choose "Use values from field:" If so, when the window popped up did you check "Also display values from second field'? And down at the bottom, "Show values from second field'. A screenshot of that window may be the quickest way to help solve your problem.
Yes and what Steve describes will work for a pop up menu but not a drop down list. Both will list only the driver's name in the list that deploys, but drop down lists show the entered ID after you exit the field while a Pop up menu does not.
There are ways to hide a drop down list behind the Driver::Driver field if you want to use a drop down list instead of a pop up menu. The trick is to give Driver::Driver an opaque fill color, deny Browse mode access to it in the Inspector and then you place it on top of the Drop down list formatted field.
I had set up the Value List as you have described. (see attached) which is why I was surprised I couldn't just select the desired field to display. I wonder why FM doesn't give you the option to select which field to display when it does give you the option to display it?
I have sort of got round it using one of Phil's early answers. I changed the dropdown to a popup and then added a field and pointed it to Driver::Driver field from the driver table. Bit makeshift but works although it would look cleaner with just the one dropdown list.
Well you don't have to do both, just one or the other.
If you select the Pop Up menu option, you don't need to add the Driver::Driver field. That "makeshift" is only needed if you want to use the drop down list option.
Right I'll amend this. I've also been rethinking the Conditional List problem now the table problem is fixed. I was wondering if I can use multiple conditional lists to drill right down to the date, day hour and minute for the drivers involved to show their availability. For example, could I create a new table / form and add a new field pointing to the driver value list, another field with month, another field with day and date and another with time , hour and minute which then somehow interacts with the newly created popup list of drivers on the main layout to show those drivers available for hire? ( As image ) If this is possible the big question is how?
Seems like you may need an additional table linked to Driver for recording their availability dates/times...
Thats what I was thinking (as per last diagram) but then do I add new fields and point them to conditional value lists that I create? And if so how then do I go about this?
It's an interesting question. Let's see if we can define the scope of how you need to manage availability a bit more precisely first.
Drivers are volunteers, correct? And thus they can choose when they are available by date and time?
How precisely can a driver specify availability, by the hour, 2 hour block, 4 hours...?
And do your drivers only volunteer during normal business hours or could the choose to be available any day at any time?
And would I be correct that at the time you need this to work, You have a client that needs a driver for a specific start date/time through a specific end date/time?
Drivers are volunteers, correct? Correct
And thus they can choose when they are available by date and time? Correct
How precisely can a driver specify availability, by the hour, 2 hour block, 4 hours...? Job dependent. Please see my answer to your last question which I hope explains their availability more clearly.
And do your drivers only volunteer during normal business hours or could the choose to be available any day at any time? Depending on driver. Most drivers work 0800 to 1800 hours but there are drivers who will work earlier or later depending on the job. i.e someone wants to be collected from an airport at 05.00am in the morning. Some drivers will only work certain days of the week say Monday through to Thursday. Some will only work Monday and Tuesday whereas others will work all week and again some drivers will also work all week and at weekends. Because they volunteer their time freely they can choose the hours they are available and from this pool of drivers I then match a free driver with a client. (This was behind my thinking that if all drivers were available 24/7 I could then just deselect the time they weren't available which would leave me the pool of drivers that were).
And would I be correct that at the time you need this to work, You have a client that needs a driver for a specific start date/time through a specific end date/time? Correct. For example: a Client may have a hospital appointment at Worthing Hospital for 10.30 Monday 16th September. The time allocated for this client is an hour and half at the hospital by which time hopefully they are seen and ready to return. So that driver would not be available then for other jobs until 12.00 pm plus half an hour either side of the appointment as he has to get to the hospital, i.e 10.00am until 12.30pm. This driver would then be available for work after 12.30 that day.
If you need anything clarified please let me know and thank you for your time and endless patience.