Fetching data using the SOAP Web Services. Using queryid() & queryidnodate(), queryrecord() and query().

Hints, Tips and Tricks

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

Fetching data using the SOAP Web Services. Using queryid() & queryidnodate(), queryrecord() and query().

  • Comments 14
  • Likes

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 < CRMIDset.Length; 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 < EntityNameList.Length; intCount++)
{
recordfield[] CRMFieldList = EntityNameList[intCount].records;
for (int intCount2 = 0; intCount2 < CRMFieldList.Length; 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 < CRMBase.Length; 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.

 

Comments
  • when i am executing query like  CRMService.queryrecord("count(*)", "", "company", "")

    it failed with message as query to run successfully

    while when tried for table "users", "lead" , "opportunity" it gives us proper record count

    Only failed for table "company" and "person"

    i have also confirm that all these table are  enable for webservice

    Can any one please tell why  query gets failed for company and person

    Is there any extra configuration for this?

  • query failed with message as  "query failed  to run successfully"

  • All the database interactions will show in the SQL log.  I suspect the use of the aggregate count(*) is creating the problem.

    The queryrecordresult object has a property called length which will give you the number of records returned that match the criteria.  If you just want a count of records in the dataset then I would consider just using the queryidnodate() method and referent the length property of the returned result.

    queryidnodateresult idList = CRMService.queryidnodate("comp_type='customer'", "company", true);

    aisid[] myaisid = idList.records;

    MessageBox.Show("Number of IDs returned by query:"+myaisid.Length.ToString());

  • Thank You Jeff  for the Quick reply.

    I have set the log setting as : "SQL Logging Level:Errors only "

    then also no SQL Log  created for this failed result  while when set "SQL Logging Level:All queries over the threshold"

    then SQL Error log is as follow:

    Aug 14 2015 14:20:59.110 6192 8956 3 execsql,time,sql 16 UPDATE Activity SET Acty_Duration = DATEDIFF(MI, Acty_Login, COALESCE(Acty_Logout,'20150814 14:20:59')) WHERE Acty_LogoutMethod IS NULL

    Aug 14 2015 14:21:50.326 6192 9972 3 fselectsql,time,sql 234 Select count(*) from company where comp_deleted IS NULL AND  comp_companyid> 0 and comp_updateddate >= '07/16/2015 02:15:34'

    Aug 14 2015 14:21:56.128 6192 9972 3 fselectsql,time,sql 16 Select Parm_Name, Parm_Value From Custom_Sysparams  WITH (NOLOCK)  where  Parm_Name = N'MetadataVersion' OR  Parm_Name = N'SysLockedTime' OR  Parm_Name = N'SysLockedByUsrID' ORDER BY Parm_Name

    so I think it's something SOAP API fail to serialize the response

    Also Jeff

    when I used

    queryidnodateresult idList = CRMService.queryidnodate("comp_type='customer'", "company", true);

    aisid[] myaisid = idList.records;

    it's give me proper result that's 1204

    but what if the record count is above 100000 because we have limitation on returning the dataset count  from Webservice setting as

    Maximum number of records to return:100

    Maximum size of request:1000000

  • The CRMService.queryidnodate() is not limited by the maximum number records to return.  The setting Maximum size of request indicates the maximum number of characters to be requested so is not a limit in this case.

    I would log a case for the usage of count(*) in the queryrecord method in web services.

  • Thank You very Much Jeff  :)

    i will use queryidnodate() method now

  • Jeff  i am having one more query:

    Currently For Sage CRM  Web Service setting, i have set  "Send and return all dates and times in universal time:Yes "

    so that's means it

    When this is selected, all dates coming from the server will be set to universal time.

    Also, all dates coming to the web server will be offset from universal time.

    This is primarily important for migrations to the hosting service from different time zones.

    But when i execute the  where-clause as comp_updateddate >= datevariable

    here  for datevariable first i have used date.now then  converted it on

    universal date then added the user timezone offset  and pass that date to where-clause

    but it's fail to return the data

    what i want to say is its only return data with offset from universal time but not consider offset when data coming to web server

  • i have to this date conversion because i application is running  on different server and SageCRM Server has different timezone

  • This article may have as it discusses dates in web services

    community.sagecrm.com/.../setting-datetime-fields-to-null-via-the-soap-web-services-interface.aspx

  • Thank you for the link but it's for setting date in the Sage CRM , I don't have problem while setting date in Sage CRM

    Let me explain problem once again

    In sage CRM Record have created date as "27/7/2015 3:12 AM"

    While when I pulled record by webservice getting Createddate as

    "2015-07-27T07:12:58+00:00"

    as per the Sage CRM setting Server time zone:(UTC -05:00) Eastern Time (US & Canada)

    also user which is use for syncing have Time Zone:(UTC -05:00) Eastern Time (US & Canada)

    so with considering offset as -5 ,

    when execute query like :

    CRMService.queryrecord("", "comp_ Createddate >= '2015-07-27T07:12:00'", "company", "comp_companyid")

    it's return empty record

    but when execute like

     CRMService.queryrecord("", "comp_ Createddate >= '27/7/2015 3:12'", "company", "comp_companyid")

    so what i am saying for the option  "Send and return all dates and times in universal time:Yes "

    is only for displaying  return  date in universal time   and not for  executing the query

  • Hi Jeff

    Can you  please answer for this now:

    I want count from user  table , so now i am executing  query like

    queryidnodateresult idList = CRMService.queryidnodate("", "users", true);

    aisid[] myaisid = idList.records;

    MessageBox.Show("Number of IDs returned by query:"+myaisid.Length.ToString());

    but it return exception as {"The string '' is not a valid AllXsd value. "}

    While SQl log does not shoe any error

    here is sql log

    Aug 19 2015 18:13:53.802 11556 4932 3 execsql,time,sql 0 UPDATE Activity SET Acty_Duration = DATEDIFF(MI, Acty_Login, COALESCE(Acty_Logout,'20150819 18:13:53')) WHERE Acty_LogoutMethod IS NULL

    Aug 19 2015 18:14:02.696 11556 1388 3 fselectsql,time,sql 0 Select User_UserID, user_updateddate, user_CreatedDate, user_deleted  from users where user_deleted IS NULL ORDER BY user_updateddate

    Aug 19 2015 18:14:09.542 11556 1388 3 fselectsql,time,sql 0 Select Parm_Name, Parm_Value From Custom_Sysparams  WITH (NOLOCK)  where  Parm_Name = N'MetadataVersion' OR  Parm_Name = N'SysLockedTime' OR  Parm_Name = N'SysLockedByUsrID' ORDER BY Parm_Name

  • Would you mind starting a thread on the forums.  This would make it easier to manage the responses. Thanks

  • Here is new link for new thread :)

  • community.sagecrm.com/.../12285.aspx