SOLVED: Value List with only active

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::radioactive:

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?

How about doing a find on the Active field ?

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


Thanks Mike that's perfect. Easy peasy.

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

1 Like

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.

1 Like

:+1:t5: Good call.

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.


I have the same experience. Stored calculation fields (indexed) seem just as fast as stored auto-enters.

1 Like