Removing Choice values

[hantsjoel]

Wise heads of the internet,

I have a choice column populated with values. My customer now wants to remove some of those values and add new ones. The problem is that the values that need to remove have already been used in existing rows in the data and, as I understand, removing the value from the choice column will clear that data from the existing rows.

What is best practice here?

• Leave the existing choice column as it is and hide it from forms and create a new choice column with the updated values to use going forward?
• Retain the existing choice column, leave the old values but mark them as DO NOT USE (not ideal UX)
• Something else I'm not thinking of?

Thanks for your advice!

[Amik]

hi @hantsjoel 

It depends on your data source. 

In Dataverse, suppose you have a choice column of Red, Yellow, and Blue, and each choice appears at least once in a row in your table. You then configure the choice column and change choice Yellow to choice Purple. Dataverse is intelligent enough to automatically update each row to which has a choice value of Yellow to Purple. This functionality applies to both Global & Local choices.

SharePoint works differently. It will not automatically update the choice value with the new value. but the old choice value will remain even if that choice has been deleted. It will not get cleared.

I am not equipped to speak for other data sources but I hope this helps.

------------------------------------------------------------------------------------------------------------------------------

 If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

[hantsjoel]

Hi Amik, thanks for your reply.

Yes I'm working with Dataverse. Using your example, let's say that I want to replace Yellow with Purple in the Choices, but I want the row of data that already has Yellow selected to retain its value of Yellow (I don't want Yellow to change to Purple in the existing data). Is there a straightforward approach to this scenario?

[Amik]

Honestly, it is not a scenario I have come across but from the top of my head, I would create separate text column to capture the choice value. You have two options:

• Use power automate to update that text column with the choice value when a new row is added.
• If using a canvas app, patch the text column with the choice value.
  • Before you try it and to save you time, at present Business Rules do not support updating a field from a choice column.

With either method, you can safely change the choice column without affecting existing items in your table as well as store the choice value elsewhere if the choice becomes orphaned. 

------------------------------------------------------------------------------------------------------------------------------

 If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

[GuidoPreite]

you can write some javascript to remove the options not used anymore, in this way the user (from UI) will not be able to set the old options, the script should handle how to act when open a record with an old option is selected (so not to remove it in this case)

hope it helps

[jkic]

I have the same requirement. Did you get a good solution for this? If I understand correctly you want to keep Yellow, so I'd add a new choice for Purple. Then the challenge is hiding Yellow so it's no longer selectable in your drop-down. I'm trying to do this in a canvas app, I'd hoped to add 'Archived' to the Description field of the choice but I can't find a way to get that data into the canvas app. I can get it using the web API, so can use it in Power BI or Data flows, but not canvas app. My solution is to create a 'mapping' table in Dataverse and add the archived choices there. I can then filter the choices in the drop-down to exclude any which exist in the mapping table

[piergaetano]

I have same issue: in DEV I removed a value but the move in PRD (after QA) do not remove that value even if that value is never used

[jkic]

@piergaetano That's a slightly different issue, but I've seen that too.  Assuming you are importing a Managed Solution from DEV (via QA) to PRD, I believe it will add New choice values, but not remove existing ones.  It's by design, presumably to prevent you removing a choice which has been used.  This is a good post on the subject Solved: Managed solution import. Update of dataverse choic... - Power Platform Community (microsoft....

[hantsjoel]

I ended up creating a new choice column that has only my new choices. I then transferred the values from the previous choice column to the new one for all existing records. I then deprecated the old column. It's not a perfect solution, but about as neat as it gets. I can understand why Dataverse works this way, but Sharepoint seems to handle this a little better.