1 Reply Latest reply on Feb 27, 2012 10:24 PM by philmodjunk

    Relationship between field with 1 value to a field with a list of values

    ChrisWondra

      Title

      Relationship between field with 1 value to a field with a list of values

      Post

      Is there a way to have a relationship between a single value and a list of values. Example: Table A has a field called IDNumber, Table B has a field called IDNumberList, I need a relationship that would see that IDNumberList "Contains" IDNumber.

      I'v tried filtering, FindValue functions, PatternCount functions, Global Variable tricks, etc. to no avail.

      I can get it to work with scripting, but that will not be a good idea as there will be several hundred thousands records.

      Thoughts?

        • 1. Re: Relationship between field with 1 value to a field with a list of values
          philmodjunk

          Define this relationship:

          Table A::IDNumber = Table B::IdNumberList

          From a layout based on Table A, a reference to Table B::IDNumberList will be empty unless IDNumber is one of the return separated values in IDNumberList. This works because a Value in IDNumber will match to any number in IDNumberList--an Or type of relationship instead of And.

          Note: this produces a many to many relationship without using a join table. While this works and can be very useful in many situations, often using a join table to list the values instead of NumberList is a much more flexible option.