Skip to main content

I am using a SharePoint list as the source for a lookup column in another list.

Is there any way to have any changes in this source list to only be reflected in new records that use the lookup column and leave the existing records unchanged?

 

My testing has shown the following:

1) Any changes to the source of the lookup list are reflected in all the existing records.

2) Any deletions from the source of the lookup list appear as blanks in any existing records.

3) Restoring a deletion from the source of the lookup list replaces the blanks with the correct information.

4)Additions to the source of the lookup list have no effect on existing records in the list.

 

Item 1 I can live with but I'd prefer that it didn't affect existing records.

Item 2 is definitely problematic. For example let's say that the source list contains a list of Sites and we have closed down our Brisbane site. Ideally we should be able to delete Brisbane from the source list so that no one can select a non-existent site but we need to know that the relevant existing records belong to Brisbane for historical purposes.

Prefixing Brisbane with Z or similar or adding something like "do not use" aren't wonderful solutions especially as some of our lists change very often and would end up with a lot of Z items.

Please let me know if you require any additional information.

Nintex Forms 2010 version 1.11.3.1

 

Nintex Workflows 2010 version 2.5.1.0

Are you using a lookup column in SharePoint? If you use a lookup column there are relationship behaviour settings to manage some of this for you.

Yes I am but none of them solve this issue.

This is the solution that I came up with.

Guidelines for creating and maintaining source lists for lookup columns

Configuration

  • Add an Archived (Yes/No) column to the source list and create a view called Current that filters out Archived records and sorts by Title.
  • Enter the following in the Description of the source list, “Do not delete the Current view as it is required for List Lookup controls on the … forms.” This will then appear under the title of the list and in All Site Content as a warning.
  • Tick Enforce relationship behaviour and select Restrict delete within the column settings for the Lookup column. This will prevent the deletion of items from the source list that are used in the Lookup column and avoids item 2 from my question.
  • Enter Current in the Source view field for the Control Settings - List Lookup for the lookup column field on the Nintex form

 

Maintaining items on a source list for a lookup column

  • Do not delete items from the list that are used in the lookup column (enforced relationship behaviour should prevent this)
  • Instead of deleting items from the source list, set their Archived field to Yes. This will remove the item from the dropdown list so that it can’t be selected for any future records but any existing records will remain unchanged.
  • Add new items as required to the All Items view of the list. The items are automatically sorted in alphabetical order in the dropdown list.
  • Do not delete the Current view from the list.
  • Any changes to items in the source list are reflected in all the corresponding records in the lookup column. Therefore only make edits to existing items in the source list that won’t change the meaning of existing records. For example changing QLD to Queensland is acceptable but changing Queensland to Melbourne is NOT acceptable.

 

 

I’m open to any simpler solutions or if you can see anything that I’ve left out of how to maintain items on a source list for a lookup column.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings