3 Replies Latest reply on Oct 6, 2012 10:28 PM by ziceg

    Conditional Value List to show only Active


      Hi, I know this has been discussed ad nauseam before, but I'm getting so confused I still can't get it to work for me... Sorry


      Can someone help me in simple easy words....


      I have a table Employees with Fields (Record ID, Name, Surname, Active)


      I want to create a value list that I can use in man other tables later on where I will need to select from a value list which Employee I am assigning things to eg Equipment. But I only want to be able to see in the value list the ACTIVE employees.


      I have set Active field to receive its value from a checkbox that comes from a value list "1" so that if it is checked it becomes "1"


      Here is were I'm stuck....


      If I go to relationships and create another table "Employee_Active" what do I link to what? How do I tell it to only look for the "1" in the Active fields? (Do I link "record ID" from original to "Active" in double up?


      If I go to create a value list and try to Use values from field... what exactly do I do? at which point can I point it to only show me the results of Active=1?


      I know it's got to be really easy... but I'm a very confused cookie

        • 1. Re: Conditional Value List to show only Active

          To set up a value list of only active employees, try this:


          1. In Employees, create a field, called z_one, and make it a calculation = 1. Important: make sure it is an unstored calculation.


          2. Create a second table occurance of Employees, called Employees_ActiveOnly.


          3. Create a relationship between Employees and Employees_ActiveOnly, using these fields: Employees::z_one = Employees_ActiveOnly::Active.


          4. Set up your value list called Active Employees. Use values from the field Employees_ActiveOnly::Name. Select the "Include only related values starting from:" and select Employees.


          5. The value list Active Employees is ready to use!


          Please note: if you actually want to store the Employee's Record ID instead of their name in fields that use the value list in a pop up or drop down, you'll need to set up the value list to use values from the first field Employees_ActiveOnly::Record ID and also display values from Employees_ActiveOnly::Name. The "Include only related values" remains the same, but you may want to check the "Show values only from second field" box.


          This is how I've always handled similar situations, but maybe someone can suggest a better way?



          • 2. Re: Conditional Value List to show only Active

            A simple method to create your value list would be to make a calculation field Active_Name = If ( Active ; Name ) and base your value list on that. No need for extra relationships.

            • 3. Re: Conditional Value List to show only Active

              Thank you that was very helpful