SharePoint Lookup column duplicates and missing records.

Question

Hi.

I've a document Library called Contracts and a SP list called Contract Data.
As it suggests, all of the clients' data such as contract start / end / renewal etc is in the data list. This forms the metadata for the content within the Contracts library.

The library has a lookup column called "Client Details Lookup" which is then selected at the document set and links to the related data entry. When contracts are added into the docset the lookup column has the effect of cascading the metadata to all documents within. This means that our legal team can review the contract and click the lookup link to see all of the associated data. The lookup column has 4 additional fields which are displayed at the document level within the docset.

The docset folders have been created with Power Automate, so the folder name is exactly that of the entry in the data list. For the folder to exist, there has to be an equivalent entry in the data list (as this is still in DEV).

So far, so good....

Now I've encountered two strange issues.
When I try to select the list entry in the Client Details Lookup column it missing some entries and duplicating others!

That should be impossible given the lookup column can only "see" what is in the data list.... so how is it seeing duplicates and missing some others? (Total items in the list is approx 550.)

Duplicate Entries

Missing Entries

Answer 1

Hi @PMD75 ，

The reason for some duplicate columns in the Lookup column may be that the list column it refers to has other metadata fields with different values.
I suggest you can take the following steps to troubleshooting this issue.

1. You could split additional fields into multiple lists and then do cascading lookups.
   For example, the first lookup control on the form will find the first list and the second lookup control will find the second list but filter off of the first.
   This is how cascading lookups should be setup and this approach should solve both loss and duplication issues. In terms of relational design, that would be optimal.
2. You might consider creating a calculated column that combines two fields to create a unique, non-ambiguous value.
   Change the lookup to reference the new calculated column for its display, instead of the default Title or whatever it's set to.
   When you pick from the dropdown now, you will see your calculated value that makes it clear which item you're selecting.
   If you don't want to create multiple lists, you could try this method, it will solve the problem of duplication, but it may not be lost again.

For Reference:
How to show unique values in a lookup column?
Remove duplicate entries from a list lookup for cascading dropdowns
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi.
Apologies for the late response.
In the end the issue was resolved by deleting the lists/libraries and starting from scratch.

Now, everything 'looks up' correctly. - Seems there was a bug with the containers.

Regards
Paul