How do I clean up choice fields

I can still edit this post, so a Prologue is everything is sorting as expected when switching back and forth between Text and Choices type. That works great.

I’m leaving my original embarrassing post with problems because of my misentered manual data for continuity with the replies. But I thought the radio button Choice list restriction would go away when the field type was switched to Text. That’s still to be resolved. further down the posts.

Setting the field type to Choices and creating a list of candidates does sort newly entered data in the order of the choice list. But, if you’ve entered previous data, with the same content as a Choice choice. it will sort separately because each content, though the same visually, is different internally.

What is the best/easiest way to get the non-choice entered data to sort with their choices twins?

I can imagine a situation where data is used for a while and in that time it becomes clear that a certain field would benefit from being a choice type. So now you have to “convert” all the older data to choice so like names will sort together.

Or, some data is coming in two-month intervals and being identified as JF, MA, MJ, JA, SO, ND. Sorting those alphabetically will not put them in chronological order, but if they were in a choice list in that order, they would sort properly. I can see setting up a dictionary with those values and assigning my own number to a field for sorting, then selecting the older data and Filling each with the appropriate sort order number to bring them into the fold.

But is there an easy way to convert previously entered data to its appropriate choice-value?

This is not correct. I just tested this to make sure. Whenever you edit the Choice list, any existing data will be converted to Choice if there is now a choice value for it.

For example, I created a Medal field and put in data with Gold, Silver and Bronze. When I sort this, gold sorts to the top, then silver, then bronze, because that was the order of my choices.

Next, without changing the list of choices I added some records with Platinum and Aluminum. When I sort, these go to the bottom. When sorting a choice field, Panorama will first sort the choices in order. Then any data that is not a choice will move to the bottom, in alphabetical order.

Finally, I revised my choice list to:

Platinum Gold Silver Bronze Aluminum

Then I sorted the database again, and they sorted in that order.

All of the text cells had been converted to choice values.

This works in reverse also. If I remove Platinum and Aluminum from the choice list, any cells with those values will be immediately converted back to text. Then they will sort to the bottom.

There is no option to sort the choice values alphabetically, interleaving them with any text values in the field. The choice values will always sort at the top, in order of the choices. Then any text values will be below, in alphabetical order.

In a private email to me @designer indicated that he was watching a video from 2015 about this topic. Panorama X was still an early beta in 2015, it’s possible that the choice feature wasn’t working quite right at that time. I don’t remember that, but it is possible. So if that old video says something different than what I’m telling you now, the old video is wrong.

I ran a different test. Here the field’s data type is set for text and sorted.

Then I set it for Choices, and sorted again.

I can toggle the data type back and forth between Text, and Choices, and the sort order switches back and forth with it.

I like your data set :slight_smile:

I’ve since modified it, and my choices list.

For those following along at home, “Cat” and “Young” are now stored as text (because they aren’t in the choice list), so they sort to the bottom.

I tried my test again - a lot of things to get right; put the choice list in the Choices box, not the Formula box, switch the type to Choices, make sure my hand entered languages are spelled correctly to match those in the Choices list…

When I change the type to Text and click in the field, I still get a popup with just the choice list options.

Ignore that I left out the “l” in the manually entered, “Engish”. Once corrected everything sorts, choice or text type, as expected.

But note that as Text Type, I’m still getting radio buttons for entry.


I tried to add in German for a language - with the field specified as Text, but I could only choose from the four radio buttons. I can’t delete the field content by opening it, because my only options are the radio buttons. I can cut via the Edit menu. And I can Paste it into the field. But I can’t open it. Again, this is when the field type is Text.

It seems that switching from Text to Choices only changes the sort order, not the ability to open the field and enter non-Choice list content when the field is Text Type.

How did you enter Platinum and Aluminum as non-choice items? Did you have to clear out the choices list in addition to changing the field back to Text type?

Also, is there a way to put in a blank/empty choice giving a radio button that will clear the field content? The video said there will not be a way, when Choice Type, to enter a non-choice item. But this is a different goal. A blank choice type could be used to clear the field, rather than having to Cut (or Cmd X) from the edit menu.

You can paste the exception value into the Choices field. Or you can set the Editor Mode popup menu in the field properties to “Text Editor”.

Ah, the old “Text Editor” mode routine. :grinning:

Thank you - that does the trick.

Sorry, I’m so late to the party. I’ll catch up. When I focus on an object. I have to understand all of it. If I go forward with partial understanding, it becomes superstition instead of knowledge.

I live at the foothills of the Superstition Mountains so that pretty much says all you need to know about how I operate. :upside_down_face:

Gary, it is safe to say, with all your contributions, that you are one of the kings of the mountain. Your realm and those of a few more well-known “others” are like Game of Thrones. Only peaceful.