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");
myQuery.SelectSQL();
Response.Write(myQuery.RecordCount);
with(myQuery)
{
while (!eof)
{
Response.Write(FieldValue("case_description")+"<br>");
Response.Write(FieldValue("pers_type")+"<br>");
Response.Write(FieldValue("comp_name")+"<br>");
NextRecord();
}
}
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.
Important!
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;
try
{
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;
break;
}
}
//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)
{
//MessageBox.Show(CRMPerson.lastname);
labeladdr_address1.Text= CRMAddress.address1;
labeladdr_city.Text = CRMAddress.city;
labeladdr_postcode.Text = CRMAddress.postcode;
labeladdr_country.Text = CRMAddress.country;
break;
}
}
...
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
-
people (Persons)
-
address
-
email
-
phone
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.
Metadata
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
Warning!
-
-
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.