A customer had the requirement to be able to retrieve person records in Sage CRM using the SOAP web services based on their email address.

On the face of it this looks like this may be a difficult job (but it isn't!). This is because of normalization. The Person and Email information are split across different tables and are linked using the EmailLink table.

The Person and Email tables are exposed by default in web services but the EmailLink table is not described in the WSDL.

It is however not difficult to find a person using their email address. This is because Sage CRM SOAP web services supports the use of sub-selects or sub-queries in the clauses used to fetch the data.

For more detail about this please see the article "Web Services, Where Clauses and Subqueries".

Below is an example SQL statement that shows how information from the person table can be fetched using the idea of a Subquery.

[code language="sql"]
select pers_lastname from person where pers_personid = (SELECT dbo.EmailLink.ELink_RecordID
FROM dbo.Email INNER JOIN
dbo.EmailLink ON dbo.Email.Emai_EmailId = dbo.EmailLink.ELink_EmailId
WHERE (dbo.Email.Emai_EmailAddress = N'[email protected]') AND (dbo.EmailLink.ELink_EntityID = 13))
[/code]

Note: The clause "dbo.EmailLink.ELink_EntityID = 13" ensures that only email address belonging to the Person table are checked. The value 13 is the default value for the person table are defined in the meta data table custom_tables.

Sub-queries form part of a where clause and several web service methods allow us to pass where clauses.

CRM.query(WhereClause, EntityName);
CRM.queryrecord(ListofColumns, WhereClause, EntityName, OrderByColumn);
CRM.queryidnodate(WhereClause, EntityName, IncludeDeleteflag);

Below is some example C# code that shows how the subquery/subselect can be used.

[code language="csharp"]
WebService CRMService = new WebService();
logonresult CRMLogon = CRMService.logon("Admin", "");
CRMService.SessionHeaderValue = new SessionHeader();
CRMService.SessionHeaderValue.sessionId = CRMLogon.sessionid;

string strSQLWhereClause = "pers_personid = (SELECT EmailLink.ELink_RecordID ";
strSQLWhereClause += " FROM Email INNER JOIN ";
strSQLWhereClause += " EmailLink ON Email.Emai_EmailId = EmailLink.ELink_EmailId ";
strSQLWhereClause += " WHERE (Email.Emai_EmailAddress = N'[email protected]') AND (EmailLink.ELink_EntityID = 13))";

queryresult CRMQueryResult = CRMService.query(strSQLWhereClause, "person");
ewarebase[] CRMBase = CRMQueryResult.records;

for (int intCount = 0; intCount
{
person CRMEntity = (person)CRMBase[intCount];
listBox1.Items.Add(CRMEntity.lastname);
}

CRMService.logoff(CRMService.SessionHeaderValue.sessionId);
[/code]