Finding Person Records by Email Address using SOAP web services in Sage CRM

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Finding Person Records by Email Address using SOAP web services in Sage CRM

  • Comments 2
  • Likes

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'syaltoy@demosagecrm.com') 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'syaltoy@demosagecrm.com') AND (EmailLink.ELink_EntityID = 13))";

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

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

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

Comments
  • I'm working on an app at the moment where I need to update leads, company and person records where there is a match on an email address. So far I can read the email addresses in via a spreadsheet and I need to process each one for each of the 3 entities because there is no guarantee that the email address is unique and not held against a lead and a company and a person and also more than once for each.

    So, I have a function called ProcessLeads. I pass in the email address and the values I need to update on the lead records but I cannot get the code working properly so I must be doing something wrong. Here's the code in the function:

           public int ProcessLeads(string psEmailAddress, string psOptionOne, string psOptionTwo)

           {

               string sWhereClause = "lead_personemail = " + psEmailAddress;

               queryresult oLeads = crm.query(sWhereClause, "lead");

               ewarebase[] CRMBase = oLeads.records;

               updateresult oCRMLeadUpdateResult;

               for (int i = 0; i

  • Lee

    You should see the transactions in the Web Services log and the SQL Log.  These will show whether there is either a problem with the web services being passed or the SQL that is being processed.