3 Replies Latest reply on Sep 6, 2015 10:53 AM by flybynight

    Filtered Value List

    FredH

      Hi,

       

      I have a Company table which contains following fields :

      Company_Name

      Type 1 FLAG

      Type 2 FLAG

      Note : Each company can flag 1 or both types

       

      I also have a Business table which contains following fields

      Business_Name

      Responsible Type 1

      Responsible Type 2

       

      For each Business, I need to identify the Responsible Type 1 & 2.  I would like to do this based on a value list which uses the Company_Names from the Company table.  I also would like to make sure that for Responsible Type 1, the values shown are only companies where Type 1 FLAG is "1".  Same for Responsible Type 2 based on Type 2 FLAG.

       

      I already did several tests but I do not manage to make the value lists work correctly.
      Do you know how I should set this up?

       

      Thanks in advance for your input?

      Kind regards,

       

      FredH

        • 1. Re: Filtered Value List
          flybynight

          You'll want to set up a table occurrence for each value list. Depending on your naming convention, they could be something like this:

          Business_Company__Type1

          Business_Company__Type2

           

          I would suggest creating a field called c1 (constant 1) in the source table, with a [stored] calculated field set to = 1. Then base each relationship linking c1 to Type 1 FLAG, plus whatever other criteria you need. Have your value list with fields from the appropriate TO and show only related values, starting from Business.

           

          HTH,

          -Shawn

          • 2. Re: Filtered Value List
            FredH

            Hi flybynight,

             

            Thanks a lot for your help.  I was nearly there but I my calculated field was not set up as "stored".
            Adding this does the trick!

             

            Thanks again,

             

            FredH

            • 3. Re: Filtered Value List
              flybynight

              No problem. I should have also said that the c1 field can't be a global field. You would think it would work, but it doesn't. There are other threads here about that.