Using Joins in Web Services

Hints, Tips and Tricks

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

Using Joins in Web Services

  • Comments 17
  • Likes

Imagine you need to retrieve information from CRM via the webservices interface; this query should combine data from multiple tables.  In the COM API (ASP pages, self service, table level scripts etc) you could use the QueryObject to build the exact SQL you need.

var mySQL = "SELECT Person.*, Cases.*, Company.*, Address.*, Chan_ChannelId, Chan_Description";
mySQL += " FROM Cases LEFT JOIN Person ON ";
mySQL += " Pers_PersonId = Case_PrimaryPersonId AND Pers_Deleted IS NULL ";
mySQL += " LEFT JOIN Company ON Comp_CompanyId = Case_PrimaryCompanyId ";
mySQL += " LEFT JOIN Channel ON Comp_ChannelId = Chan_ChannelId";
mySQL += " LEFT JOIN Address ON Pers_PrimaryAddressId = Addr_AddressId ";
mySQL += " WHERE Case_Deleted IS NULL"
var myQuery = CRM.CreateQueryObj("YourSQLStatement","Database");
while (!eof)

Within the Web Services interface there is no equivalent method to allow us to an adhoc query.  We do have several methods that allow us to retrieve data from the system.

CRM.query("comp_name like '" + textBoxSearch.Text + "%'", "Company");
CRM.queryentity(int.Parse(textBoxSearch.Text), "Company");
CRM.queryrecord("comp_companyid, comp_name", "comp_type='Customer'", "company", "comp_name");
CRM.queryidnodate("comp_type='customer'", "company", false);

In the examples above CRM is the name of the webservice object.

But none of the examples above allow us to build a dynamic SQL statement that joins data from several tables.  Each needs to be used with a single table as defined in CRM's metadata.  We can only access data from tables that have been explicitly exposed to webservices.  A general SQL statement would not be allowed as you could theoretically access tables not open to the webservice interface.


What I have just written "Each needs to be used with a single table..." needs a little more explanation. The Web Services interface uses the concept of Entities.   Two of the methods I mentioned above, query() and queryentity(), implicitly involve more than one table but only from the parent down. 

For example in this snippet of code the queryentity() method is used to retrieve data into not just from the company table but also the records of persons that belong to the company.  And then also the addresses, phone and email records of that company and persons.

queryentityresult CRMEntityResult;
ewarebase CRMBase;
ewarebase[] CRMBase2;
ewarebaselist CRMCompanyPeople = new ewarebaselist();
person CRMPerson;
ewarebaselist CRMCompanyAddresses = new ewarebaselist();
address CRMAddress;
CRMEntityResult = CRM60.queryentity(int.Parse(textBoxSearch.Text), "Company");
CRMBase = CRMEntityResult.records;
CRMCompany = (company)CRMBase;
//extract the person records
CRMCompanyPeople = CRMCompany.people;
CRMBase2 = CRMCompanyPeople.records;
for (int intCount = 0; intCount < CRMBase2.Length; intCount++)
CRMPerson = (person)CRMBase2[intCount];
if (CRMPerson.personid == CRMCompany.primarypersonid)
labelPers_LastName.Text= CRMPerson.lastname;
labelPers_Salutation.Text = CRMPerson.salutation;
labelPers_FirstName.Text = CRMPerson.firstname;
labelPers_Gender.Text = CRMPerson.gender;
//extract the address records
CRMCompanyAddresses = CRMCompany.address;
CRMBase2 = CRMCompanyAddresses.records;
for (int intCount = 0; intCount < CRMBase2.Length; intCount++)
CRMAddress = (address)CRMBase2[intCount];
if (CRMAddress.addressid == CRMCompany.primaryaddressid)
labeladdr_address1.Text= CRMAddress.address1;
labeladdr_city.Text =;
labeladdr_postcode.Text = CRMAddress.postcode;
labeladdr_country.Text =;

Note:  The code above is an incomplete extract from the example project that can be found in the resources section. 

These are the Web Services entities that are joined implicitly to child records via the query() and queryentity() methods

  • Company
  • people (Persons)
  • address
  • email
  • phone
  • Orders
  • orderitems
  • Person
  • address
  • email
  • phone
  • Orders
  • quoteitems
  • NewProduct
  • pricing
  • uomfamily
  • uoms

The converse of the query() and queryentity() methods is the add() method that allows us to insert data into CRM.  This also allows us to insert not just a company record but also the child person, address and email records.


But... the keyword here is metadata.  Using CRM's features we can build a database view then link it to CRM as a "table" and then make that table available to webservices.

Consider the view "vListCases".  This is a default view defined in CRM.

We can link to this view as a table.

Administration -> Advanced Customization -> Tables and Databases

Using the new table connection option.

Once the link has been defined we can then edit it

And make the table/view available via webservices


The new view or table is then described in the WSDL.

This means that the view is available to be accessed in web services code


  • If you are exposing a view to webservices then you need to be pay attention to how column aliases are named. (See the article "The importance of column prefixes in tables exposed via the Web Services API").
  • Only use views for retrieving data. It is best not to try and update or insert data using views exposed to webservices.
  • You will need to be mindful of performance.  When accessing the views via webservices it is also a good idea to only use queryrecord() rather than query().

If you don't want to create a view within CRM then we can still accomplish much via the webservice interface.  For example we can discover relationships between the records that could allow us to create the links between tables in our application.  The article "Using WebServices to discover relationship information about tables" discusses how we can "discover" which Sage CRM tables are related to each other.

  • Does queryentity person really contain phone records? I can only see address and email.

  • I created a table connection as you've mentioned above  for a view that I want to query in web services code. My query has no mention of "_deleted" or "WITH (NOLOCK)" but I get the following error in SQL logs:

    Jun 9 2014 16:08:30.562 7960 8956 1 fselectsql,time,sql,errormsg 15 Select oppo_opportunityid from vlasearchlistopportunity  WITH (NOLOCK)  where _deleted IS NULL AND oppo_opportunityid=66 Invalid column name '_deleted'

    Could you please help me with this? Thanks

  • Keyhan

    The clause "_deleted is null" is being added automatically.  I think you need to add the column "oppo_deleted" into your view.

  • Hi Jeff,

    I tried adding "oppo_deleted" first to the table connection that I created. I got a message that it already exists in another table. I tried adding it to the Opportunity table but I got the message that oppo_deleted already exists. I believe oppo_deleted is a system field that is automatically created when you create the table. What happens if I delete this field from Opportunity table through SQL Server Mananagement Studio? I was afraid that I might mess up the system if I delete this field. Any further help regarding this issue is highly appreciated.



  • Keyhan

    You should not need to change or add the column in the table.  You should certainly not remove a column from a CRM table via SQL Management Studio (unless told to by support).

    The column oppo_deleted is the column in the Opportunity table that keeps track of records that have been 'soft' deleted.  The clause that is added automatically to queries "oppo_deleted is null" shows that records that have been 'deleted' are suppressed and not returned in the results.

    You need to look at your view 'vlasearchlistopportunity'.  Does this view contain the column oppo_deleted? If not then add this to the view.

  • Jeff,

    The view already contains oppo_deleted.

  • Ksayyah

    Does the WSDL contain mention of "_deleted"?  Is this something that appears in the Web Services log or only the SQL Log?

  • Hi Jeff,

    It just appears in the SQL log. After I've added the view table I get the following error when I try to view the WSDL. I could view the WSDL before adding this table.

    An error has occurred during program execution.

    Please read the following information for further details.

    Access violation at address 1A14CE58 in module 'eware.dll'. Read of address 00000000.

  • Ksayyah

    That is a big clue.  Unexpose the view/table from web services, restart the server and try to access the wsdl again.  You need to have a WSDL with integrity.

    This article was written in 2009, back in the days of Sage CRM 6.2 and there have been numerous changes to the software and data model that may now prevent this trick from working.

  • Hi Jeff,

    So is there a new way of accessing the view from web services?

  • You can mix SOAP webservices with SData requests.  SData requests can pull data directly back from views that are exposed to SData.  You don't need to describe them as tables or anything.  There are examples of how to use SData in this blog.  The down side is that the handling of the returned XML is a little more problematic.  For an easy way for using SData for Ajax in Sage CRM 7.2 see the Client Side API examples in this blog.

  • Hi Jeff,

    Can we run Stored Procedures using WSDL or sdata?

    Thanks for your help with views. CLient Side API is very easy to use.

  • I cannot access Territories table via Client Side API(sdata). I set 'Bord_SDataAccess' &' Bord_WebServiceTable' in Custom_Tables' to 'Y' in SQL Server Management Studio. Do I need to set anything else? Is it possible to access Territory via sdata?