Deals are associated with a Year. Specifically Deal Document Set has a Site column called UW Year, which represents the year for this deal. We want to choose the year from a dropdown – I’m open to how to implement the values. Can be a list, term set, hardcoded list, whatever will work.

This is the tricky bit: Now in 2020, we want to see 2020, 2021 and 2022. Works great for this year. Deals will be associated with one of those.

But next year, we want the choices to be 2021, 2022 and 2023. However, Deals that have 2020 should still retain 2020.

It's like you want a dropdown for convenience only, but not to be linked with the values in the dropdown. If they are removed tomorrow, you lose nothing except convenience. If you edit an item, the Year value will remain the year it was set to regardless of which years are currently in the dropdown.

Any ideas how to solve this? Ideally without coding.

  • You basically need to have a lookup list containing years, 2020, ..2030 etc and have a flag like active and another column, calculated one, say ActiveYears to be always filled with the ActiveYears. InActive years will be blank for this column. In the Deals list/library, you need to create a lookup column referring to this list, but pointing to ActiveYears column Oct 13, 2020 at 19:48

2 Answers 2


Here are the steps:

  1. Create a list containing years with a choice column, say [Status] with values "Active" and "Inactive", and a calculated column called [ActiveYears]
  2. Set the formula to the [ActiveYears] to the following:

Please not that [Title] has been renamed to [Years] in the above formula.

  1. Items in list DealYears will look something like

enter image description here

  1. Now create a lookup column in [Deals] referring [DealYear] list, but pointing to [ActiveYears] column

  2. When an item is created in [Deals], the only active years will be shown in the dropdown, but previously entered years will still be retained.

enter image description here

Only Active Years are available to choose from: enter image description here


Actually, while creating the lookup column for Deals, need to add an additional field, [Years], from [DealYears]. Please see the revised screenshots below.

enter image description here

List Deals showing items with currently active years and also previously active years, which are now inactive

enter image description here

  • Thank you! What would happen to a Deal with 2019 as its year, after 2019 has been set to Inactive? Wouldn't it get wiped out if you go to edit the Deal?
    – Sandra
    Oct 13, 2020 at 20:12
  • It will retain in the list.. nothing is deleted. Old items would still work. But, when creating new items, users should see only the active years to choose from Oct 13, 2020 at 20:16
  • Ok! Looking forward to testing it out, and will return to mark as answer. Thanks again
    – Sandra
    Oct 13, 2020 at 21:17
  • Maybe I told you wrong, sorry. But I will find a solution for you. Oct 14, 2020 at 0:29
  • Please see the updated screenshots. As I've mentioned, you need to add an additional field to the [Deal] list. Let me know if you have any questions. Oct 14, 2020 at 1:01

It turns out you can use a Term Set, and then to hide one of the years, you Deprecate that term.

This can be undone later by Enabling the term.

While Deprecated, the term no longer appears in the dropdown list for users to pick.

Records that use the deprecated term retain the value. You can even modify another field and save, and still retain the deprecated value. Of course if you specifically edit the fields, remove the deprecated term and add something else, at that point you will have the new value, and would not be able to go back to the deprecated value without Enabling it first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.