Checkbox fields are brilliant for letting a user quickly collect certain types of data. But they do have their limitations and you may discover that you want to be able to store answers such as 'Y' (Yes), 'N' (No), 'M' (Maybe) rather than a simple 'check'.

If that is the case then you will need to change a CheckBox back into a selection list.

Assuming you are using MS SQL Server, if a new field is created as a CheckBox then the field will be added as type nchar of length 2. That is enough to store 'Y' as the value of a Checkbox field when 'checked'.

But you will find that you can't change a checkbox into another type of field.

In Sage CRM a System Administrator is normally able to change fields that are based on the same physical datatype into the different entrytypes that work with that datatype. So if should be possible to change the entrytype of a field from a Selection list into an Intelligent Select and an Intelligent Select into a Selection list or a Checkbox. This is because they are all based on the datatype nchar.

The different values of the entrytype are stored in the custom_edits table in meta data. You can read more about entrytypes in the article "Sage CRM EntryTypes used in the COM ASP API".

But for historic reasons fields of entrytype CheckBox can't be changed to another entrytype.

Note: This can be a challenge if a System Administrator accidentally changes an existing field to be a checkbox and discovers that there is no way in the interface of changing the field back to the correct entry type.

So what can be done to change a field of entrytype 'Checkbox' to a 'Selection' list?

The change will need to be done in the database using SQL. To carry out these changes all the users will need to be logged out of the system.

The query below will retrieve the meta data for a field on the company table called 'comp_sector' which was accidentally changed into a Checkbox.

select * from custom_edits where colp_colname = 'comp_sector';

The value of colp_entrytype is 45 which indicates that the field is a CheckBox.

The meta data can be changed using the SQL below;

update Custom_Edits set ColP_EntryType = 21
where ColP_ColName = 'comp_sector';

Selection lists are of entrytype 21.

To complete the job the meta data will have to be refreshed.

You can then edit the selection field to add the choices that you need.