locked
how can i get count of entity RRS feed

  • Question

  • For example, i want to count emails for a contact.

    how do i write with my asp.net code?

    Thanks
    Thursday, October 8, 2009 8:27 PM

Answers

All replies

  • public int GetEmailCountFromContact(CrmService crmService, string contactGuid)
    {
        ConditionExpression cond = new ConditionExpression();
    
        //Attribute name is the regarding object id.
        //Value to search for in the regarding field is the guid of the contact sent in
        cond.AttributeName = "regardingobjectid";
        cond.Operator = ConditionOperator.Equal;
        cond.Values = new object[] { contactGuid };
    
        FilterExpression filter = new FilterExpression();
        filter.FilterOperator = LogicalOperator.And;
        filter.Conditions = new ConditionExpression[] { cond };
    
        QueryExpression query = new QueryExpression();
    
        //Return all columns from the crm.
        query.ColumnSet = new AllColumns();
        query.Criteria = filter;
        query.EntityName = "email";
    
        RetrieveMultipleRequest request = new RetrieveMultipleRequest();
        request.Query = query;
    
        //Execute the request.
        RetrieveMultipleResponse response = (RetrieveMultipleResponse)crmService.Execute(request);
    
        //Get the number of returned emails.
        int returnCount = response.BusinessEntityCollection.BusinessEntities.Count();
    
        //Return the number of emailes associated 
        return returnCount;
    
    }

    This is somewhat how a this query should look like, I didn´t have a crm system to test this query but it should be close to this.

    This query asks the CRM system to return you all the emails that have the contact guid in their regarding field, so this should give you the count of emails that have that contact as regarding.

    Make sure to check if you get the correct number returned though since again I wasn't able to test this, and expect typos I typed this straight in here, no copy paste stuff 8)

    2 things to double check.

    1. cond.AttributeName = "regardingobjectid"; //Check if this name is correct, can be seen in the email entity, this should be the regarding field name (Database name not the display name).
    2. query.EntityName = "email"; //Check if this name is correct, can be seen in the email entity, this should be the name of the entity (Database name not the display name).


    There is a chance you might need to find what your looking for in a many to many relationship middle table, if that is the case then you can read some up on the following thread on how to find the correct names to insert into your query, also you might have to change that query to query for manytomany relationship stuff(again can´t check since no access to a CRM system atm).

    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/93bad32b-9009-45aa-b8ca-ee71988edb2c

    Thursday, October 8, 2009 11:42 PM
  • thanks for the answer.  the retreivemultiple response will retrieve all the emails and takes long time.

    this one is more like sql  select * from email where contact=value;        IT take too much time for executing. and also it has problem when the data is large.

    I am wondering there anything like:  select count(*) from email where contact=value; 
    can we do this through webservice?
    Friday, October 9, 2009 2:18 PM
  • To the best of my knowledge you can not query the sql straight via the webservice.

    //Return all columns from the crm.
    query.ColumnSet = new AllColumns();

    you can try and change this to only fetch the emailid (or whatever fields you might need from the email), that should reduce the data fetched and might speed up things.
    Something along the lines of the following (double check the name of "emailid" if it is correct).

    query.ColumnSet = new ColumnSet("emailid");
    or
    query.ColumnSet = new object[] { "emailid" };


    You should not be getting all emails returned, the "cond" variable should be filtering on the contact guid so you should only get emails regarding that contact.
    Friday, October 9, 2009 2:27 PM
  • As of rollup2 fetchxml also supports aggregation. Take a look at the following post published by the CRM team. You’ll need to transform the crmservice.retrievmultiple(queryexpression) to a crmservice.fetch(fetchxml) request.

    http://blogs.msdn.com/crm/archive/2009/03/10/support-for-count-distinct-in-fetch-xml-queries.aspx

     


    GI CRM Blog * GI Website
    • Marked as answer by Jim Glass Jr Wednesday, October 14, 2009 4:14 PM
    Friday, October 9, 2009 8:30 PM