4 Replies Latest reply on May 28, 2014 9:17 AM by BenSachs-Hamilton

    Creating a filtered value list to use across an entire database

    BenSachs-Hamilton

      Title

      Creating a filtered value list to use across an entire database

      Post

           I use a database to manage data about residents in a boarding house, and I'd like to create a conditional value list that contains only current residents, which I could then use for data entry in multiple tables (room occupancy, chores, etc.).

           I have one primary People table, with ID (serial number) and Name fields. I have a separate Residencies table, with a PersonID field linked to the People::ID field, as well as a "ResidentStatus" field ("Current" or "Former").

           Can anyone explain how to generate the value list I'm looking for? I'm happy to provide more information if needed.

        • 1. Re: Creating a filtered value list to use across an entire database
          philmodjunk

               Is ResidentStatus a field defined in People?

               If so, then you can define a calculation field or auto-entered text field as:

               If ( ResidentStatus = "Current" ; Name )

               This field will be empty if the status is not "current".

               Then define your value list to list PeopleID as the first field and this name field as the second field. Either set it to only show the second field or to sort values on the second field (the name) and you'll only have a value list of current people records.

               And since no relationships were used for this conditional value list, it can be used on any layout where you need to enter a People ID from a list limited to only current residents.

          • 2. Re: Creating a filtered value list to use across an entire database
            BenSachs-Hamilton

                 OK, I think I'm getting closer.

                 I now have a calculation field (CurrentResident) in the People table that returns only the names of current residents, and is blank for all others. I've then set up a value list ("Current Residents") with People::ID as the first field and People::CurrentResident as the second (with the options to "Include all values" and "Show values only from second field").

                 I have a Chores table with the following fields: ID, PersonID (linked to People::ID), and Chore (with it's own custom, fixed value list). To my understanding, I should be able to designate Chores::PersonID as a drop-down list, with the following specifications: Display data from Chores::PersonID, Values from "Current Residents." When I do this, however, the resulting drop-down list contains only a question mark.

                 What am I doing wrong?

            • 3. Re: Creating a filtered value list to use across an entire database
              philmodjunk

                   What kind of field is People::CurrentResident? Is it an indexed field? It cannot be an unstored calculation field as such fields cannot be indexed and thus cannot be used as the "sorted" field in this type of value list.

              • 4. Re: Creating a filtered value list to use across an entire database
                BenSachs-Hamilton

                     Got it! Yes, it was indexed, but the calculation result was set to Number instead of Text. Now it works fine. Thanks again for your help!