A colleague asked me a very simple question.
"I want to do a basic query with simple parameters e.g. in SQL: select * from opportunity where Oppo_ChannelId = 1 and Oppo_Product='TimEx5'. Do you have any sample web services code?"
This is not such a straightforward question in Web Services as it is in the COM API.
In answering the question I have assumed that you are familiar with Web Services and concepts like the WSDL and how to create your first project to logon and logoff and handle sessions. If you are not then please have a look at the follow articles that introduce some of the concepts.
When designing an webservice request to fetch data from Sage CRM you will have to consider what you really need as a result. There are 3 choices that allow you to return sets of results.
- queryidnodate() or queryid()
- queryrecord()
- query()
All the query methods are restricted by security rights.
Note: All the code samples below are in C# and I have used the sample code from the Sage CRM web services snippets for Visual Studio.
The choice of which method you use is important. For example if you just want a list of IDs that match the criteria you would use
queryidnodate() or queryid()
e.g.
[code language="csharp"]
queryidnodateresult CRMQueryIdNoDateResult = CRMService.queryidnodate("Oppo_ChannelId = 1 and Oppo_Product='TimEx5'", "opportunity", false);
aisid[] CRMIDset = CRMQueryIdNoDateResult.records;
for (int intCount = 0; intCount
{
//Example Use
//VisualControl.Items.Add(CRMIDset[intCount].id);
}
[/code]
This is the fastest query that you can ask and the result is not throttled by the "Maximum number of records to return" settings in
Administration -> System -> Web Services
Once you have the list of IDs you can then you them to retrieve the details of the records either by using queryentity() or queryrecord().
queryrecord()
This is next fastest way of fetching data. It is fast because it only works against one database table and you specify the fields that are returned in the result set. But because of the very dynamic nature of the returned set it is slightly more fiddly to handle.
e.g.
[code language="csharp"]
queryrecordresult CRMQueryRecordResult = CRMService.queryrecord("oppo_opportunityid, oppo_description, oppo_status, oppo_type", "Oppo_ChannelId = 1 and Oppo_Product='TimEx5'", "opportunity", "oppo_status");
crmrecord[] EntityNameList = CRMQueryRecordResult.records;
for (int intCount = 0; intCount
{
recordfield[] CRMFieldList = EntityNameList[intCount].records;
for (int intCount2 = 0; intCount2
{
recordfield CRMField = (recordfield)CRMFieldList[intCount2];
//Example Use
//VisualControl.Items.Add(CRMField.name + ": =" + CRMField.value);
}
}
[/code]
The queryrecord() method allows you to specify the fields returned in the XML and will only return data for the table referenced and not the subordinate records. So a QueryRecord run against the company table will not retrieve person or address info. This is the preferred method for getting specific data back quickly.
query()
This method, query(), is the one that is easiest to understand and to use but it is also the most problematic because of the amount of data that it returns. It is much slower than queryrecord() and much slower than queryid() and queryidnodate().
[code language="csharp"]
string strSQLWhereClause = "Oppo_ChannelId = 1 and Oppo_Product='TimEx5'";
queryresult CRMQueryResult = CRMService.query(strSQLWhereClause, "opportunity");
ewarebase[] CRMBase = CRMQueryResult.records;
for (int intCount = 0; intCount
{
CRMEntity = (EntityName)CRMBase[intCount];
//Example Use
//VisualControl.Items.Add(CRMEntity.FieldName);
}
[/code]
The query() method returns the set of entities with all their subordinate data. Using query() on opportunities would not be too bad because opportunities as defined in the WSDL do not have any collections of entities underneath them. An individual opportunity is not a parent of a set of address or phone records. But if you asked for all companies being with 'A' then you will have to wait a long time as the SQL is fired and the response XML is assembled. The query() method used against an entity like company or person would return the company with all its subordinate data (All the Persons, All the Addresses, All the Persons' Addresses, All the Phone Numbers for both the Company and the listed Person records etc).
The queryentity() method works in the same way but because this is retrieves a single entity e.g. the company Gatecom, the load of retrieval is not too bad and speed of getting a set of company data up in a web service project using queryentity() is about the same as retrieving the same company's details in the main interface. BUT compared with the main interface the method query() can be excruciating and should only be used in very particular circumstances.
