I have a list of staff members and I want a value list of only active staff members (i.e. staff members with the field "Active" set to "1"), and I feel like there has to be a better way.
Usually I create a global field in the Staff table, and join that table to itself like this:
Staff::Blank_Global < Staff_SelfJoinForActive:
This gives me a list of all users who have an active state greater than zero (i.e. 1)
Is there a better way to do this? Does it matter if I have a global field or not here?
You can use an extra calculated field with the calculation Case ( Active=1;Name ) and then use for the value list the new field that has only the active members
Another thing that I am using is a custom function vlookup
this way I give a nice auto entry value list with the company name or the staff name and automatically I auto enter the primary key. (instead of a huge popup menu list with no search capabilities
How about making that field ‘auto enter calc”, so that the thing works faster. If you use a calc field, and there are lots of records, it has to calculate very single record to show the ValueList..
Unless you set the AcTIVE field via script, this field, now a normal Text field, with auto enter calc, is always up to date.
That being said in this case the calc doesn't use a join and what I've read and seen from my own tests is speed of an indexed Calc is the same as an indexed auto-enter. I do use Auto-enter when it goes through a join since you can't index a joined calc.
Good call.