Change Entry Type of Field in Sage CRM

By | October 17, 2011

Sage CRM standard functionality allows us to add multiple types (Text, Selection List, Multi Select, Search Select Advanced etc) of entries in an entity and showing them for at the entity level for informative purpose. What if you have created a field with wrong data type? Has anyone thought how one can change the data type of field without deleting the field? Well, the answer is yes you can change the data type and for this you need to do some database level changes.
Suppose you have created a field in the Company entity with the following specifications.
Entry Type : Currency
Column Name : comp_test
Now, if you want to change the Entry Type of this field, you will normally navigate to field level under the appropriate entity (Here Company) and select the field to change the entry type. But sometimes Entry Type column in the field Read Only.
You need to follow the below steps.
1. Login to MS-SQL server.
2. Select the Sage CRM database.
3. Select the Company Table and check the data type of any existing Selection field.
4. Create a query on the Sage CRM database in the below format.
alter table < < table name > >
alter column < < column name > > < < data type of new field > >
Here in this case, my query would be something like this.
alter table company
alter column comp_test nchar(40)
5. Execute the query by pressing Execute button or F5.
6. Now, Login to Sage CRM.
7. Navigate to Administration > Customization > Company.
8. In the Fields tab, select the field comp_test (which will show you as Currency type)
9. Click on field name and change type of the field.
10. Click on Save button.
11. Do the Metadata Refresh.
12. Run the IISRESET command.
The similar thing can be achieved by only executing the SQL queries only perquisite is you need to know the Entry Type of the new field. You need to execute the Update query on the Custom_Edits table which stores all the field level information. The query would be something like below. You need to change it as per requirement.
–Query – Change the column properties to make it selection field
update custom_edits
set
colp_entrytype=21
colp_defaulttype=0
colp_entrysize=20,
colp_datatype=4,
colp_datasize=40
colp_updateddate =getdate()
where colp_colname = ‘< field name e.g. comp_test >‘
— Query – Alter column in table to fit the selection field
alter table < < table name > > alter column < < column name > > < < data type of new field > >
Login to CRM and do Metadata refresh.
I would recommend using 1st option as it saves your time to search for the Entry Type of the new field. Make sure that you have database backup before applying above scripts on the CRM database.
For more details on Add-On and pricing, please drop us an email at crm@greytrix.com.