A phone number in CRM maybe configured to consist of 1, 2 or 3 parts.
The country code (optional)
The area code (optional)
and the Number
The configuration of the optional parts has been discussed elsewhere on this site.
The telephone numbers are stored in the phone table in the CRM database.
Physically the columns set up at datatype nchar with varying lengths.
Phon_CountryCode nchar 10
Phon_AreaCode nchar 40
Phon_Number nchar 40
The use of the nchar for UTF-8 and unicode support means that by default the maximum number of characters allowed to be entered into each field is:
Phon_CountryCode 5
Phon_AreaCode 20
Phon_Number 20
The importance of unicode support is discussed in another article elsewhere on this site.
In the interface the maximum length that can be entered into each phonenumber is actually the same,
Phon_CountryCode 5
Phon_AreaCode 20
Phon_Number 20
We will see later in this article that the maximum entry width is controlled by the entry in the custom_edits meta data table.
The maximum lengths are all the same whether you are entering the Company phone number tab or the Person phone screen. Phone numbers of type Business, Fax etc all behave the same in Sage CRM by default.
The size of edit box however in the phone and email screen of company and person are set to different lengths
Phon_CountryCode 4
Phon_AreaCode 10
Phon_Number 10
So although you can enter 5 digits into the country code box the size seems to be 4 and the number can hold 20 but you only see the first 10 digits when editing the number.
Normally this would be enough for most purposes. For example a typical telephone number in the UK would be in the form '7890 1234'. There is therefore plenty of room to display the number including the space character. But where the optional country code and area codes are not used then the phone number becomes "44 20 7890 1234" so becoming longer than the display of 10 digits when entering and editing the number.
The phone number table is not exposed in the Customization interface of Sage CRM, so you can not easily alter the field lengths. You also can not additional fields (e.g. extension) to the table via the interface.
The telephone number field length, however, can be increased directly within the database to allow you to hold extra information such as extensions, but this would have an impact on CTI integration. See elsewhere on this site for an article about the validation of telephone numbers and the use of characters in phone numbers. The phone fields are held in the database as text (nchar) so adding extra text such as "x" or "ext." at the end of the number is possible.
We have 2 challenges. The first is how to increase the default display length of the number and secondly how to increase the physical length of the data allowed to be stored.
Increasing the Display Length of Phone Number Edit Field
The display length is controlled by the meta data held in the custom_edits table. The specific value we are interested in is the colp_entrysize. We can see this by running a query
SELECT colp_entrysize, *
FROM Custom_Edits
WHERE (ColP_Entity = 'phone') AND (ColP_ColName LIKE 'phon_number%')
This reports that the default length for all of the phone_number fields is 10.
If we were interested in the area_code or the country_code then we would need to change the query.
To update the length of the phon_number fields then we can run a update statement against the database:
UPDATE custom_edits
SET colp_entrysize = 20
WHERE (ColP_Entity = 'phone') AND (ColP_ColName LIKE 'phon_number%')
To see the effect we would then need to refresh the meta data.
Increasing the Maximum Phone Number Length
To increase the maximum length of the phone number to 40 usuable characters we would first have to increase the physical length to the field in the database. This can be done using the Enterprise Manager or SQL designer tool.
Note: Telephone numbers of type "Business" and "Fax" are denormalised into the Company and Person tables. So if we alter the length of the data to be greater than the corresponding fields in the Company and Person table then we can expect SQL Errors. A change to the maximum width in the phone table will mean we also have the change the fields in the company and person table.
If we change the length of the phon_number field to 80 then because the field is of datatype nchar we will experience it as a 40 character length field in CRM.
What ever you change the length of the phon_number field to, then you must also change the comp_phonenumber and pers_phonenumber fields to the same length.
In the previous example we changed the display length for the field. Now we need to change the entry width. We saw at the beginning of this article that by default the entry width of the phone number field is 20 characters. If we now want to be able to enter and store phone numbers up to 40 characters long then we need to change the entry width. The property in the custom_edits table that controls this is the colp_lookupwidth.
To increase the entry widths of the telephone numbers we can run the following SQL:
UPDATE custom_edits
SET colp_lookupwidth = 40
WHERE (ColP_Entity = 'phone') AND (ColP_ColName LIKE 'phon_number%')
and colp_lookupwidth is not null;
To see this take effect we will need to stop and start the webserver rather than just refresh the meta data.
You can stop and start the web server by typing IISRESET after selecting 'Run' from the windows "Start" menu.
Note: Because you can change something does not mean that you should change things. I have not spent time testing this, so it is possible that the changes I have suggest could break your system. So you will need to allow time for testing, testing, testing.
The country code (optional)
The area code (optional)
and the Number
The configuration of the optional parts has been discussed elsewhere on this site.
The telephone numbers are stored in the phone table in the CRM database.
Physically the columns set up at datatype nchar with varying lengths.
Phon_CountryCode nchar 10
Phon_AreaCode nchar 40
Phon_Number nchar 40
The use of the nchar for UTF-8 and unicode support means that by default the maximum number of characters allowed to be entered into each field is:
Phon_CountryCode 5
Phon_AreaCode 20
Phon_Number 20
The importance of unicode support is discussed in another article elsewhere on this site.
In the interface the maximum length that can be entered into each phonenumber is actually the same,
Phon_CountryCode 5
Phon_AreaCode 20
Phon_Number 20
We will see later in this article that the maximum entry width is controlled by the entry in the custom_edits meta data table.
The maximum lengths are all the same whether you are entering the Company phone number tab or the Person phone screen. Phone numbers of type Business, Fax etc all behave the same in Sage CRM by default.
The size of edit box however in the phone and email screen of company and person are set to different lengths
Phon_CountryCode 4
Phon_AreaCode 10
Phon_Number 10
So although you can enter 5 digits into the country code box the size seems to be 4 and the number can hold 20 but you only see the first 10 digits when editing the number.
Normally this would be enough for most purposes. For example a typical telephone number in the UK would be in the form '7890 1234'. There is therefore plenty of room to display the number including the space character. But where the optional country code and area codes are not used then the phone number becomes "44 20 7890 1234" so becoming longer than the display of 10 digits when entering and editing the number.
The phone number table is not exposed in the Customization interface of Sage CRM, so you can not easily alter the field lengths. You also can not additional fields (e.g. extension) to the table via the interface.
The telephone number field length, however, can be increased directly within the database to allow you to hold extra information such as extensions, but this would have an impact on CTI integration. See elsewhere on this site for an article about the validation of telephone numbers and the use of characters in phone numbers. The phone fields are held in the database as text (nchar) so adding extra text such as "x" or "ext." at the end of the number is possible.
We have 2 challenges. The first is how to increase the default display length of the number and secondly how to increase the physical length of the data allowed to be stored.
Increasing the Display Length of Phone Number Edit Field
The display length is controlled by the meta data held in the custom_edits table. The specific value we are interested in is the colp_entrysize. We can see this by running a query
SELECT colp_entrysize, *
FROM Custom_Edits
WHERE (ColP_Entity = 'phone') AND (ColP_ColName LIKE 'phon_number%')
This reports that the default length for all of the phone_number fields is 10.
If we were interested in the area_code or the country_code then we would need to change the query.
To update the length of the phon_number fields then we can run a update statement against the database:
UPDATE custom_edits
SET colp_entrysize = 20
WHERE (ColP_Entity = 'phone') AND (ColP_ColName LIKE 'phon_number%')
To see the effect we would then need to refresh the meta data.
Increasing the Maximum Phone Number Length
To increase the maximum length of the phone number to 40 usuable characters we would first have to increase the physical length to the field in the database. This can be done using the Enterprise Manager or SQL designer tool.
Note: Telephone numbers of type "Business" and "Fax" are denormalised into the Company and Person tables. So if we alter the length of the data to be greater than the corresponding fields in the Company and Person table then we can expect SQL Errors. A change to the maximum width in the phone table will mean we also have the change the fields in the company and person table.
If we change the length of the phon_number field to 80 then because the field is of datatype nchar we will experience it as a 40 character length field in CRM.
What ever you change the length of the phon_number field to, then you must also change the comp_phonenumber and pers_phonenumber fields to the same length.
In the previous example we changed the display length for the field. Now we need to change the entry width. We saw at the beginning of this article that by default the entry width of the phone number field is 20 characters. If we now want to be able to enter and store phone numbers up to 40 characters long then we need to change the entry width. The property in the custom_edits table that controls this is the colp_lookupwidth.
To increase the entry widths of the telephone numbers we can run the following SQL:
UPDATE custom_edits
SET colp_lookupwidth = 40
WHERE (ColP_Entity = 'phone') AND (ColP_ColName LIKE 'phon_number%')
and colp_lookupwidth is not null;
To see this take effect we will need to stop and start the webserver rather than just refresh the meta data.
You can stop and start the web server by typing IISRESET after selecting 'Run' from the windows "Start" menu.
Note: Because you can change something does not mean that you should change things. I have not spent time testing this, so it is possible that the changes I have suggest could break your system. So you will need to allow time for testing, testing, testing.
