Occasionally you may be faced with a requirement to know what database datatype a field is before you start to work with it.

This type of information is not held in the custom_edits table - only the Sage CRM entrytype is held there. These are all documented in the developers guide.

We may also have a problem in using the RecordObject to get the Type as the RecordObject always seems to return it's data as Strings (see below) and so that does not let us know what the physical datatype is within the database.

var myRecord = CRM.FindRecord("company","comp_companyid=28");
Response.Write(typeof(myRecord.comp_companyid)+"
");
//always seems to return strings

To solve this problem in an install of Sage CRM running on MS SQL Server I used the existing system tables to give me the answer I needed. I also used the QueryObject to construct the necessary SQL.

The getFieldType() function is constructed to allow me to look at fields in tables that exist in connected third party databases and returns the physical datatype as a string.

function getFieldType(strDatabase,strTable,strField)
{
var strSQLStatement ="";
strSQLStatement += "SELECT st.name AS DBColType, sc.length AS DBColLength";
strSQLStatement +=", sc.name AS DBColName, sc.xprec AS DBPrecision";
strSQLStatement +=" FROM syscolumns sc, sysobjects so, systypes st WHERE";
strSQLStatement +=" sc.id = so.id AND sc.xusertype = st.xusertype AND so.name = '";
strSQLStatement += strTable;
strSQLStatement +="' AND sc.name LIKE '";
strSQLStatement += strField;
strSQLStatement +="'";

var myQuery = CRM.CreateQueryObj(strSQLStatement,strDatabase);
myQuery.SelectSQL();

return myQuery.FieldValue("DBColType")
}

Response.Write(getFieldType("Northwind","Orders","Employeeid"));