3 Replies Latest reply on Mar 21, 2012 11:19 AM by philmodjunk

    Values in a list based on a calculation.



      Values in a list based on a calculation.


      Here's the background: I have a value list with a list of names for various jobs. This list is generated from the Name field in the Jobs table, and is used by almost every other table I've got to link the various records to the job they're about (hours, purchase orders, various forms). The Jobs table also has a Status field to denote whether the job is active or complete.

      What I'm trying to do is set the value list to only contain Names from records where the Status field reads as "Active".

      Any ideas on how I would go about this?

        • 1. Re: Values in a list based on a calculation.

          A simple but not terribly flexible method is to define a calculation field in Jobs like this:

          If ( status = "active" ; nameField )

          This field will contain a name only when status = "Active". You can base your value list on this calculation field.

          But you might want to consider not entering names from a value list like this. What will you do if you get two people with the same name? What happens when someone changes their name? If you were to use an ID number that uniquely identifies each person instead of their name, you would avoide such issues.

          • 2. Re: Values in a list based on a calculation.

            I think I may have typo'd in my original post. The Name field contains the Name of the job (or project) the our company is currently working on. It's more of a Where than a Who.

            When a user selects a job from the value list, it's actually the project that the record should be associated with. When the project is complete, Id like it so that selecting "Complete" in the status field on the record for the job removes it from the value list.

            • 3. Re: Values in a list based on a calculation.

              I'd still be inclined to use an ID number but it's less of an issue. You can still encounter a potential problem if you create new project, then create some related records before discovering that it needs the name changed (maybe a data entry error...), you'll have the same issue.

              The method I described works with either a name or an Id number. The only difference is the field you reference inside the If function to control whether the field is empty or contains a name/ID for not completed jobs.