1 Reply Latest reply on Jul 22, 2014 2:19 PM by philmodjunk

    Conditional Value Lists with tables that have more than one relationship

    JosephFalduti

      Title

      Conditional Value Lists with tables that have more than one relationship

      Post

           I understand how to create a conditional value list and have successfully made them.  However, I need to create a conditional value list using two tables that already have an existing relationship.  When I add the second relationship to represent the conditional value I want to use to filter the list, I end up having no values populate the list, because the relationships are "AND" relationships and both relations are not being met.  Thus, nothing populates in the dropdown.  

           Is there a way to create a conditional value list for this type of relationship?  

        • 1. Re: Conditional Value Lists with tables that have more than one relationship
          philmodjunk

               The trick is to add more relationships without changing your existing relationships. And this can be done. You can identify as many relationships between two tables as you need using completely different match fields and not have one issue with the result being an "AND" relationships as described.

               You do this by creating additional table occurrences of one or more of your tables.

               Example:

               You have this relationship between Tables A and B:

               Table A------<Table B
               Table A::__pkTableAID = Table B::_fkTableAID

               but you then need a second relationship between Table's A and B that match only by a field name Category in both tables. So you go to manage | database | Relationships, select the Table Occurrence Box for Table B by clicking it and then click the duplicate button (Two green plus signs). This produces:

               Table B 2

               This is not a new table. It's a new reference to the records in Table B. Now drag from category in Table A to Category in Table B 2 to produce:

               Table B 2>----Table A-----<Table B

               Table A::__pkTableAID = Table B::_fkTableAID

               Table A::Category = Table B 2::Category

               Assuming that the relationship based on Category is the one that you need for a conditional value list for a layout based on Table A, you'd set up your conditional value list to list values from Table B 2 instead of from Table B.

               For more about table occurrences and how to use them: Tutorial: What are Table Occurrences?