We are able to create applications that can interact with Sage CRM remotely using the Web Service Interface.

If the external application is going to be able to make reference to the data, objects and methods within the Sage CRM via a web call, then the data structures, properties, objects and methods have to be described and understood by that application.

An application's WSDL definition (Web Service Definition Language) is the how the data types and methods etc are made available in Web Services.

The WSDL definitions for Sage CRM are made available for an external application via a web reference e.g. calling http://localhost/crm/eware.dll/webservice/webservice.wsdl

At the moment the request is made the application will gain an understanding of the objects exposed by Sage CRM via Web Services.

The important thing to realise is that the WSDL definitions obtained by calling the webservice.wsdl mentioned above represent a snapshot of the CRM system. Those are the definitions that are correct then.

But the definitions of tables and fields in Sage CRM rarely stands still. It is very likely that new entities/tables will be added to CRM and that new columns will be added to existing tables. All these structures may in turn be exposed to Web Services.

The WSDL for Sage CRM is dynamic in terms of the entities and fields that are exposed, so all new custom data should be reflected in it. But as we have seen the WSDL is requested as a web reference by an external application and represents the Sage CRM WSDL definitions only at a individual point in time. Once the request has been made and the web reference cached in the application, if the internal WSDL changes then web references to the WSDL are potentially out of date and would have to be refreshed.

For example if you create an application that is designed to interact with many implementations of CRM then you can never be sure what tables and columns are going to be present. Of course you can make assumptions about the main entities (Company, Person, Case, Opportuinity, etc) and about mandatory fields but Sage CRM can be very heavily customised with extra tables and fields.

So how can we hope to write an application that has to work with unknown tables and fields?

The Sage CRM web service interface provides us with several mechanisms for developing our applications that can take into account the likelihood of change to the underlying data structures. These mechanisms include checking the version to make sure about the presence of fields or tables we know were introduced by a particular version or service patch. But the most important technique uses heuristic methods do discover the tables and fields exposed in the webservice interface and which allows us to dynamically construct the SQL data manipulation language to carry out create, read, update and delete tasks.

Examples of these methods in C# are shown below.

1) How to discover what version of CRM is being used.

For example the database structure many change slightly between versions of Sage CRM.

WebService CRM = new WebService();
logonresult CRMlogon = CRM.logon("Admin", "");
CRM.SessionHeaderValue = new SessionHeader();
CRM.SessionHeaderValue.sessionId = CRMlogon.sessionid;
getversionstringresult CRMVersion = CRM.getversionstring();

2) To discover all the tables exposed in Meta Data

getallmetadataresult allMetaData = CRM.getallmetadata();
tableinfo[] myTableInfo = allMetaData.tables;
for (int intCount = 0; intCount
{

//Do something with the list of table names e.g. write to a windows listBox
myListBox.Items.Add(myTableInfo[intCount].tablename);
}

3) To discover the fields available in an individual Table

getmetadataresult CompanyMetaData = CRM.getmetadata("company");
crmfield[] companyfields = CompanyMetaData.records;
for (int intCount = 0; intCount
{
//Do something with the list of field names e.g. write to a windows listBox
myListBox.Items.Add(companyfields[intCount].name);
}

4) Construct a dynamically assembled SQL statement to retrieve data from table

string strListofFields = "comp_companyid, comp_name";
string strWhereClause = "comp_type='Customer'";
string strEntityName = "company";
string strOrderBy = "comp_name";

//The List of fields can be created dynamically from the use of getmetadata() method.
//The Where clause can be contructed dynamically based on User selection
//The Entity name is derived from the information obtained by getallmetadata() method.
//The Order By clause can be produced from User selection.

queryrecordresult myQueryRecordResult = CRM.queryrecord(strListofFields, strWhereClause, strEntityName, strOrderBy);
crmrecord[] myRecordList = myQueryRecordResult.records;

for (int intCount = 0; intCount
{
recordfield[] myFieldList = myRecordList[intCount].records;
for (int intCount2 = 0; intCount2
{
recordfield myField = (recordfield)myFieldList[intCount2];
//Do something with the list of field and values e.g. write to a windows listBox
myListBox.Items.Add(myField.name + ": =" + myField.value);

}
}

Other Methods that allow you to dynamically assemble SQL are:

addrecord()
updaterecord()

Selection lists associated with fields on any table, which includes new custom tables, can be found using the getdropdownvalues() method. The getdropdownvalues() method has been discussed elsewhere on this site.