# SOLVED: Value List with only active

**URL:** <https://the.fmsoup.org/t/solved-value-list-with-only-active/2012>\
**Category:** Questions\
**Tags:** join\
**Created:** [April 14, 2021, 9:08pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012 "2021-04-14T21:08:48Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![JasonMark](https://avatars.discourse-cdn.com/v4/letter/j/a9adbd/32.png) [@JasonMark](https://the.fmsoup.org/u/JasonMark)\
**Post date:** [April 14, 2021, 9:08pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/1 "2021-04-14T21:08:48Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![planteg](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/planteg/32/627_2.png) [@planteg](https://the.fmsoup.org/u/planteg)\
**Post date:** [April 14, 2021, 9:17pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/2 "2021-04-14T21:17:22Z")

</div>

How about doing a find on the Active field ?

---

<div class="post-metadata">

**Author:** ![mike275](https://avatars.discourse-cdn.com/v4/letter/m/7993a0/32.png) [@mike275](https://the.fmsoup.org/u/mike275)\
**Post date:** [April 14, 2021, 9:18pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/3 "2021-04-14T21:18:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JasonMark](https://avatars.discourse-cdn.com/v4/letter/j/a9adbd/32.png) [@JasonMark](https://the.fmsoup.org/u/JasonMark)\
**Post date:** [April 14, 2021, 9:22pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/4 "2021-04-14T21:22:37Z")

</div>

Thanks Mike that's perfect. Easy peasy.

---

<div class="post-metadata">

**Author:** ![mike275](https://avatars.discourse-cdn.com/v4/letter/m/7993a0/32.png) [@mike275](https://the.fmsoup.org/u/mike275)\
**Post date:** [April 14, 2021, 9:34pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/5 "2021-04-14T21:34:07Z")

</div>

Another thing that I am using is a custom function vlookup

> **[FileMaker Custom Function: VLookup ( LookupTableAndFieldName ;...](https://www.briandunning.com/cf/1740)**
>
> Works similar to the Excel VLookup function (but with some nice optional features): Searches in a specific field for a matching lookup value, and returns the value from a different field in the same record. -- This version of the function is...

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

---

<div class="post-metadata">

**Author:** ![pfro](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/pfro/32/1815_2.png) [@pfro](https://the.fmsoup.org/u/pfro)\
**Post date:** [January 1, 2022, 6:24pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/6 "2022-01-01T18:24:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JasonMark](https://avatars.discourse-cdn.com/v4/letter/j/a9adbd/32.png) [@JasonMark](https://the.fmsoup.org/u/JasonMark)\
**Post date:** [January 1, 2022, 8:40pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/7 "2022-01-01T20:40:06Z")

</div>

👍🏾 Good call.

---

<div class="post-metadata">

**Author:** ![JasonMark](https://avatars.discourse-cdn.com/v4/letter/j/a9adbd/32.png) [@JasonMark](https://the.fmsoup.org/u/JasonMark)\
**Post date:** [January 1, 2022, 8:41pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/8 "2022-01-01T20:41:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jwilling](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/jwilling/32/256_2.png) [@jwilling](https://the.fmsoup.org/u/jwilling)\
**Post date:** [January 2, 2022, 10:47pm UTC](https://the.fmsoup.org/t/solved-value-list-with-only-active/2012/9 "2022-01-02T22:47:11Z")

</div>

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