Post your file and we can show you how to make it work.
Sometimes a "picture" is worth a thousand words.
It sounds like you need a self-join. Here's what you do:
1) Create a pair of calculation fields in your table. One will be a constant field, called "Supervisor", which always has a value of "Supervisor". The other will be "Y", with a value of "Y".
2) Create another table occurrence of the same table on your Relationships Graph. You can call it "StaffSelf" or something.
3) Create a relationship between the table with two predicates:
Supervisor = Job Title
Y = Current
where the parent table is on the left, and the self-join is on the right.
4) Create a value list, based on related values only, for whatever value you want from StaffSelf, starting from Staff.
This should give you a value list that shows all the supervisors who have a "Y" in their status field.
I worked though your solution, and it did exactly what I was hoping for.
Many thanks Mike.
I have revisited the self-join answer given previously as I needed to make another conditional value list within my same solution.
This time it is for current members of staff who are a counsellor or trainee counsellor, so that I can use a drop down list on the Clients layout to show who is their current counsellor.
I thought it would be simple but am having trouble making it fully work.
Firstly I made a new constant field in my ‘STAFF’ table called ‘Counsellor’ with the values = “counsellor” or “trainee counsellor”.
Then I made a 2nd 'self' table occurrence, this one called STAFF_self_counsellor, and set the relationships:
Counsellor = Job Title
Y = Current
I created a value list as previously except this time with the value (STAFFfull_name_c) from the STAFF_self_counsellor TO.
The field I’m trying to populate in the Clients layout is called CLIENTcurrent_counsellor and this is related to STAFFfull_name_c in the STAFF table, but when I try to use it, the drop down list I get is just blank.
If, within the ‘Staff’ layout I set a field to use this value list, all works fine.
I’ve also found that in another layout (Diary) related to 'Staff' via 'Client', I can set a field to use this list and all works… just not in the Clients table I need it to.
I just cannot understand why!
Conditional value lists are dependent on context. You'll need to define the relationship and value list from the TO where you want the value list to appear. I can't be 100% certain of what you've done (since I can't see your Graph), but it sounds like there's no relational connection between Client and Staff.
Conditional value lists are dependent on context.
Well, conditional value lists that are based on a relationship are dependent on context. There are several conditional value list options that are not based on a relationship and thus are not dependent on context. There are also ways to do "connector" type relationships that allow a CVL to work in a wider context.
A non-relationship based CVL that you might consider is one where you use a calculation to produce the desired list of values. Since such a value list does not use the "include only related values..." option, it will work from any context in your file.
This time it is for current members of staff who are a counsellor or trainee counsellor
You can define a calculation field in the same table with a calculation such as:
If ( Not IsEmpty ( Staff ; List ( "Counsellor" ; "Trainee Counsellor" ) ) ; NameFieldHere )
You can then set up a value list to list either just the names by listing the contents of this calculation field or if you are using ID values like you should, you refer to the ID field as field 1, this calculation field as field 2 and either sort or show the calculation field.
This is one form of what I call a 'hardwired' conditional value list (because the user is not asked to select the category) in my instructional file named:
I was trying to keep it simple ...
The calculation field method is no more complex than a relationship based version, just different.
"Simple" as in "avoid option overload".
But no, a calc field isn't hard to implement.
Thanks for the replies.
I had 'tried' to set a relationship between the Clients and Staff TOs, but that's where I'd come up short...
I think, for this solution, Phil's way of having a non-relationship based list would work better as this list of 'current counsellors' will be required to be used in a couple of places, on the Clients layout and on a Diary layout, both of which aren't related to the Staff TO.
I had a bit of difficulty making the calculation work initially and then, when I did, I had to incorporate an extra bit to only include current members of staff (as ex members still have the positions they held 'ticked' in their record. So I have made a field called STAFFcurrent_counsellors with the calculation:
If(STAFFcurrent="y" and (not IsEmpty ( List ( "counsellor" ; "trainee counsellor") ) ) ; STAFFfull_name_c)
I have made a value list that uses the contents of this field.
One issue I am still having, and can't immediately see the cause of, is that in one of the two places I'm using this value list, the field isn't populating.
I have a field on my Client layout called CLIENTcurrent_counsellor which is a drop-down list using values from this new value list. All works fine, and only the current staff members marked as being a counsellor or trainee counsellor show are listed.
However, I have another field on my Diary layout called DIARYassessment_counsellor which also is set as a drop-down list using values from this new value list. When I slick on the down arrow for this I get no list at all...
Any clues please??
Is the STAFFcurrent field an unstored calculation, perhaps?
Even easier than that...
I'd not selected the correct value list after all for this second field, but rather the one I'd tried to create yesterday the self-join way. Similar list name I'd left in case I went back to it...
It's now deleted!
Thanks for your help...