Changing custom Contact Reference field from single to multi-select causes failure

Question

I had a user report an issue with using a custom field. The field records who is the interviewer for a particular interview and uses a contract reference type field, and, on inspection, allows you to add multiple interviewers - the Multi-Select box was ticked. Looking at it, everything seemed fine, but I could see from the log that it was failing when you tried to add even a single interviewer. Looking at the error data (Gist of error, I could see that it was trying to add a Hex value to an integer field.

To test this, I recreated another field within in the same custom field set using Contract Reference and Multi-Select and all worked fine.

Answer 1

When I checked the table details in MySQL workbench, I could see that the problem field was of type INT(10) and that the recreated field was of type VARCHAR(255) - this would make sense due to the seperator that needs to be stored. At some point in the distant past someone must have changed their minds about needing more than one person logged as an interviewer and changed the field to 'multi-select' by ticking the box.

When I tried to check it back to single and then change it back to multi-select on my local, the system froze and on checking the logs, I could see Foreign Key constraint errors.

In the end I first exported the data, deleted the the two fields, recreated them, (ensuring that the Contact Reference fields was multi select from the start) and reimported the data. All is good now.

The moral to the story is NOT to make these sorts of changes of data type. Unfortunately, the message that comes up when you do try this is '.. all data will be preserved'. This might be true, but depending on when the field was created, whether it has a search index against it etc, you might have an issue on your hands.