Lookup column with no duplicates

Question

I have a library containing documents, each one regarding a project. I also have a custom list with tasks (for monitoring) which has a column PROJECT. There are more than one tasks per project so this column has many duplicates.

What I need is to create a lookup column for the documents pointing at the PROJECT column of the list. The problem is that even if SP can group the list items per project (sorry for the Greek characters, I hope you get the picture):

when I fill the columns for a new document at the library I get a list of duplicates:

Is there a way to filter out the duplicates, so I can only have PROJECT A, PROJECT B etc?

Answer 1

I don't think that this is possible.

When you select an item in the lookup, SharePoint would internally store the ID of the item you selected. If the item is the result of a group by on multiple rows, which row should it refer to?

As far as I know, you won't be able to achieve your goal by standard means. As an option, couldn't you put the information about the project in a separate list and have that list as a lookup on both the document and the task libs? That would avoid redundancy in the Task list and normalize your data (pretty much as you would do on a standard DB). Can this be reasonable for you?

Otherwise, I fear that custom development is the only solution that comes to my mind.

---

Edit: about the "create Projects list and update it automatically using the entries of Task list" option.

That would be possible but ootb lookup on list don't allow fill in choise. That mean that if you want to still have a lookup on the Task list you would still need to develop some custom component (probably you can get away with a simple custom field that extend the lookup one - good old Waldek has already given some point about that in the past).

Notice that if you use Mastykarz solution, that would also resolve the "update project item" problem. Otherwise, another option would be to use some list event receiver to intercept the item updated/updating event and write some custom code that update the project list as required.