How to retain the existing records in the main list during lookup list updates?

Question

_{Monday, August 5, 2013 10:12 AM}

Hi Guys,

We have a list that has several lookup columns from other lists. Once we edit the item in the lookup list, the existing records in the main list (list that is using the lookups) are getting updated too. This seems to be nice but we don’t want the existing records to be updated. How can we make the existing records retain?

Thank You,

Arnel

All replies (7)

_{Tuesday, August 6, 2013 2:06 PM ✅Answered}

• SharePoint Event Receivers
• Using Event Receivers in SharePoint Foundation 2010 (Part 1 of 2)
• SPItemEventReceiver.ItemAdded Method
• How to: Create a Custom Field Type and Field Control

The 2nd option (Event Receiver) would be much easier to implement.

Please remember to mark your question as answered if this solves your problem.

_{Monday, August 5, 2013 1:12 PM | 2 votes}

It sounds like you may want to use a Choice column to handle your values instead of a lookup column. Lookups are intended to create a relationship between the lists, if the data is updated in one it is reflected in the other. A choice column is pretty static, if you change the values available in the choice column it will not change the value already added to the list items.

With that said, a Choice column may not give you what you need (not knowing what your other requirements are). You may also be able to do some trickery with some custom code in the list event receivers to capture the original value of the item into a separate field.

Please remember to mark your question as answered if this solves your problem.

_{Monday, August 5, 2013 3:17 PM}

Hi Eric,

Thanks for your response. Yes, we used lookup column since we are also getting related value from the lookup fields and we have a cascading drop down on our NewForm. Is it possible to make the lookup relation static? Any reference guys or inputs will much be appreciated.

Thank You,

Arnel

_{Monday, August 5, 2013 6:55 PM | 1 vote}

There is no setting to disable that functionality with a lookup column.

A lookup field consists of a LookupLabel (the value that you see), and the LookupID (the ID to the selected lookup item). When you save an item both the label and ID are stored with that item in the format of "1;#value". If you change the value in the lookup list, this gets reflected back in the original list as well because that relationship is established using the LookupID.

With some custom development, and event receiver could copy the original value to another field in the list, which would remain unchanged if you change the value in the lookup.

Why do you need to keep the original value in the list item? Why is the lookup value being changed? Perhaps with a bigger picture, I can help think of some other solutions that may help.

Please remember to mark your question as answered if this solves your problem.

_{Tuesday, August 6, 2013 8:44 AM}

Hi Eric,

In our custom NewForm we have several lookup fields coming from different lists. Below are those fields.

Originating Plant – This is a lookup column in Plant list.

Supplier Name   - This is a lookup column in Supplier list.

Evaluator - This is a lookup column in Evaluator list.

Manager - This is a lookup column in Manager list.

Escalatee - This is a lookup column in Escalatee list. 

We need to change or update these lists (Supplier, Evaluator, Manager and Escalatee) from time to time so we just used lookup columns. However, the existing records are getting updated as well whenever we do an update. We need to retain the list item unchanged for record tracking. Hope this helps providing you a better picture of our case. Thanks again and I really appreciate your help.

Regards,

Arnel

_{Tuesday, August 6, 2013 11:47 AM | 1 vote}

 The options I see are:

1. Use a Choice column instead of a lookup list. This wouldn't work if your pulling other values from the lookup list into this item though.
2. Create an event receiver to save the original value into another field. This would at least let you see the original value for record tracking.
3. Create a custom field type that would handle what your trying to do.

The last two options require custom development unfortunately.

If I think of anything else I'll be sure to let you know.

Please remember to mark your question as answered if this solves your problem.

_{Tuesday, August 6, 2013 1:25 PM}

Thanks Eric. Do you happen to know some good references on how do the last 2 options?