none
Good practices for managing lookups that change periodically RRS feed

  • Question

  • I have a SharePoint list that tracks stuff.  From that list, my organization wants to show related items in other lists, such as business priorities and strategic initiatives.  For example,

    Title: Website upgrade project

    Description: Build some new features on our website

    Business Priority: (lookup) 4. Internal Processes

    Strategic Initiative: (lookup) FY19 Initiative #27

    Quantity of sticky notes used: (lookup) 50-100

    The last one represents those oddball columns that you know probably won't get used in the long term, so it just ends up cluttering your list until someone allows you to delete it.  Even with the more persistent items such as strategic initiatives, the lookup will eventually be cluttered with initiatives from ten years ago that you don't want to delete because they're still associated with historical stuff, but they'll never again be selected from the current list.

    On a scale of 1-10 with SharePoint I'm like a 5, and I'm also trying to make the list easy to maintain when I'm gone.  In other words I'm more interested in an effective practice/process for how to add and manage those columns and their contents than a code-based solution.

    • Edited by snkr Wednesday, August 21, 2019 1:05 PM
    Tuesday, August 20, 2019 6:23 PM

All replies

  • Hi  snkr,

    In SharePoint their is OOTB feature called Information Management policies, you can enable that one and use it to achieve the goal without any code.

    Retention – The Retention policy feature lets you define retention stages, with an action that happens at the end of each stage. For example, you could define a two-stage retention policy on all documents in a specific library that deletes all previous versions of the document one year after the document is created, and declares the document to be a record five years after the document is created.

    Here are guide, how to setup retention policy.

    https://mikesnotebook.wordpress.com/2013/05/20/sharepoint-retention-policies/

    https://www.dynamics101.com/sharepoint-information-management-policy/

    In addition, you can also choose to delete past items via the power shell command, you can refer to the commands in the following article

    https://sharepoint.stackexchange.com/questions/250949/how-to-delete-items-from-large-list-by-modified-date-using-powershell

    Best regards

    Itch Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, August 21, 2019 7:56 AM
  • Thanks for the reply.  The issue isn't as much figuring out how to remove old items as figuring out how to effectively keep them.
    Wednesday, August 21, 2019 11:30 AM
  • Hi snkr,

    The actions that can occur at the end of a stage include the following:

    1.Moving the item to the Recycle Bin

    2.Permanently deleting the item

    3.Transferring the item to another location

    Instead of deleting these items, you can move them to other lists, which makes it easier for you to manage

    Best regards

    Itch Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, August 22, 2019 9:19 AM
  • I might not have phrased my original question correctly, so I'll try to clarify.  I do not want to delete or relocate any of the items in the list.  All of the items in the list will remain in the list.

    Certain choice or lookup columns in the list items will begin to have obsolete values that will start to clutter the choice or lookup dropdowns.  Those values will be obsolete for future entries in the list, but not for past entries where they show historical context.  If a lookup value is deleted, it gets deleted from every list item where that value was selected.  If a choice value is deleted, it prevents someone from editing a record and re-saving if the choice column was a required field.

    This question is strictly about how to manage lookup and choice columns, not the list items that use those columns.

    Friday, August 23, 2019 1:15 PM
  • snkr - it may help to consider a list item naming approach.  As items in linked lists become outdated or whatever, rename them in such a way that this change in status is visible in the lookup box.  For example, I had a list that had a lookup onto document titles in a document library.  To help her more quickly identify appropriate documents, I setup a naming scheme for the secretary that the secretary could maintain and that would more clearly identify a document when it (the document title) appeared in the lookup box.  

    As another example, there was a list (call it A) that had a lookup onto another list (call it B), and i setup the naming scheme for items in list B so that they sorted themselves out when viewed from the lookup on A.  List B had transactional items in it that repeated from year to year, but that referred to different instances of the same thing unique to each year.  So, the naming scheme prefixed the date first for the list item title.  That way, when the user viewed these items from the lookup, he or she could quickly scroll to and identify items appropriate to the year of interest.  

    These administrative approaches were a bit cumbersome, but they were simple, no-code approaches that reasonably and inexpensively resolved the problem.



    Friday, August 23, 2019 5:05 PM
  • I did something like that, although I'm not thrilled with it.  I added a choice column in the column being looked up from called Status that includes two options: Active and Archived.  Once an item in the lookup list is changed to Archived I used a calculated column to change the name to "Archived - [Number] [Title]" which sorts it to the bottom of the dropdown menu (letters sort below numbers).  The problem is that something could get archived before a data call using that item is requested, so whoever creates the report would have to do a find-and-replace to remove "Archived" from everything after exporting the data.

    Example:

    002   |   Title 1   |   Active   |   002 Title 1

    026   |   Title 2   |   Active   |   026 Title 2

    013   |   Title 3   |   Archived   |   Archived - 013 Title 3

    • Edited by snkr Tuesday, August 27, 2019 11:36 AM
    Friday, August 23, 2019 7:33 PM
  • The difficulty with that approach, using a workflow that is started on changing a lookup from Active to Archive, is that it depends a great deal on user accuracy. What if the user inadvertently changes an item that shouldn't be changed? Who checks the accuracy of these changes? Will there be periodic review?

    One way of trying to control for these unknowns involves custom permissions.  It's possible to create custom permission levels that grant users the ability to only view and add new items to a list, only view and change existing items in a list, or even only allow them to view the items in that list. Using such custom permission levels, you could create a system where your general users can view items in the target list or even add and/or delete them, but they can't change them, such as changing that lookup from Active to Archive.  This would prevent the general users from inadvertently making changes to that lookup.  You then assign a few specific persons who will be administering this system to the permission level that allows changes.

    Saturday, August 24, 2019 9:41 PM
  • It's a calculated column that creates the title, not a workflow.  I put the lookup lists at the root, so anyone can look up the values in those lists but I manage the contents.  If I change something in that list, it's changed in every site that looked up a value from that list.
    Tuesday, August 27, 2019 11:41 AM
  • Hi snkr ,

    No OOB method can meet your needs.

    If you need more information, you can go to user voice and post about it.

    You can view the information on the User Voice, and once this feature is up-to-date, SharePoint User Voice Admin will reply below.

    Thank you for your understanding.

    Best regards

    Itch Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, August 29, 2019 9:22 AM
  • Hi ,

    Has the problem been solved?

    If you think the replies are helpful to you, please remember to mark them as answers. It will help others who meet the similar question in this forum.

    Thank you for your understanding.

    Best regards

    Itch Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, September 2, 2019 9:38 AM
  • Well, since your last response was, "No OOB method can meet your needs," I'm going to go with no, my problem is not solved.

    As I said in the original post, I'm more interested in an effective practice/process for how to add and manage those columns and their contents..."

    Tuesday, September 3, 2019 1:10 PM