Establishing relationship between two lists on a share point site

Before I got to know about what I am going to write here, I was pretty convinced that there is no way to establish a relationship between two lists using lookup columns in case of the source column having multiple copies of data.

Using OOTB features of share point a user can only associate a single field from source list with the destination list in form of a lookup column. It should not be the ID since ID won’t be user readable and if it is only a title then it definitely fails in case of multiple copies of title with same value.

Just to make things a bit more clear –

If in case we have two lists – Account, Contract. An Account can have multiple contracts. In order to establish a relationship between the two, I associated column – Title from Account list with column Account Name from the Contract list using the look-up column type.

This relation-ship works well as long as the title column in Account list is unique. But to use this relationship in share point filters, connections etc. we need to have unique instances of title column to make it work accurately.

Situation can be handled accurately if CAML and values returned by a look column are put to use while doing some customization.

List of items returned by the CAML query contains values in the form ‘FieldID;#FieldName’ for lookup columns, Where FieldID and FieldName corresponds to the ID and title columns values respectively for the source list.

Using the above form, the items can be searched for the corresponding ID’s. This property can be put to good use in scenarios such as - populating values in a dropdown on selection change of another drop down list.