locked
Is this query possible in CRM 3.0 RRS feed

  • Question

  •  

    Hi,

     

    Can I write following query in Microsoft CRM 3.0.

     

    Select

         A.Code,

         A.Name,

         C.Amount,

         C.Date

    From

         Customer A

        Join Ledger C On A.CustCode = C.CustCode

    Where

        A.CustCode = 'AAA'

     

    Could anybody give me idea, how can I write this quey in CRM 3.0

     

    Thanks

    Wednesday, January 30, 2008 2:24 AM

Answers

  • Here is one way to use Join in entities. see this way.  Another way is use SQL Query like join etc in your SQL Select statement

     

    http://www.philiprichardson.org/blog/post/Part-3-Applications-which-Connect-to-CRM.aspx

     

     

    // Get the user information.
                            WhoAmIRequest userRequest = new WhoAmIRequest();
                            WhoAmIResponse user = (WhoAmIResponse) service.Execute(userRequest);

                            // Create the ConditionExpression.
                            ConditionExpression condition = new ConditionExpression();

                            // Set the condition for the retrieval to be when the last name of the account's owner is not Cannon.
                            condition.AttributeName = "partyid";
                            condition.Operator = ConditionOperator.Equal;
                            condition.Values = new string [] {user.UserId.ToString()};

                            // Build the filter based on the condition.
                            FilterExpression filter = new FilterExpression();
                            filter.FilterOperator = LogicalOperator.And;
                            filter.Conditions = new ConditionExpression[] {condition};

                            // Create a LinkEntity to link the owner's information to the account.
                            LinkEntity link = new LinkEntity();

                            // Set the properties of the LinkEntity.
                            link.LinkCriteria = filter;

                            // Set the linking entity to be the activity.
                            link.LinkFromEntityName = EntityName.activitypointer.ToString();

                            // Set the attribute being linked to to be the activityid.
                            link.LinkFromAttributeName = "activityid";

                            // Set the entity being linked to to be the activityparty.
                            link.LinkToEntityName = EntityName.activityparty.ToString();
                     
                            // Set the attribute linking to the activityparty to be the activityid.
                            link.LinkToAttributeName = "activityid";
                     
                            // Create the query.
                            QueryExpression query = new QueryExpression();

                            // Set the properties of the query.
                            query.EntityName = EntityName.activitypointer.ToString();
                            query.ColumnSet = new AllColumns();
                            query.LinkEntities = new LinkEntity[] {link};

                            // Create the request object.
                            RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

                            // Set the properties of the request object.
                            retrieve.Query = query;
               

    Wednesday, January 30, 2008 5:52 AM
    Moderator
  • Hi Daniel,

    From my experience that is the limitation in CRM 3.0. You can only retrieve values from one particular entity at a time. Much like Advanced Find, you can't retrieve information from more than one entity.

    Alternately, you could look at mapping data from the parent field to the child field.

    Cheers,

    Karlo
    Tuesday, September 23, 2008 3:53 PM

All replies

  • Here is one way to use Join in entities. see this way.  Another way is use SQL Query like join etc in your SQL Select statement

     

    http://www.philiprichardson.org/blog/post/Part-3-Applications-which-Connect-to-CRM.aspx

     

     

    // Get the user information.
                            WhoAmIRequest userRequest = new WhoAmIRequest();
                            WhoAmIResponse user = (WhoAmIResponse) service.Execute(userRequest);

                            // Create the ConditionExpression.
                            ConditionExpression condition = new ConditionExpression();

                            // Set the condition for the retrieval to be when the last name of the account's owner is not Cannon.
                            condition.AttributeName = "partyid";
                            condition.Operator = ConditionOperator.Equal;
                            condition.Values = new string [] {user.UserId.ToString()};

                            // Build the filter based on the condition.
                            FilterExpression filter = new FilterExpression();
                            filter.FilterOperator = LogicalOperator.And;
                            filter.Conditions = new ConditionExpression[] {condition};

                            // Create a LinkEntity to link the owner's information to the account.
                            LinkEntity link = new LinkEntity();

                            // Set the properties of the LinkEntity.
                            link.LinkCriteria = filter;

                            // Set the linking entity to be the activity.
                            link.LinkFromEntityName = EntityName.activitypointer.ToString();

                            // Set the attribute being linked to to be the activityid.
                            link.LinkFromAttributeName = "activityid";

                            // Set the entity being linked to to be the activityparty.
                            link.LinkToEntityName = EntityName.activityparty.ToString();
                     
                            // Set the attribute linking to the activityparty to be the activityid.
                            link.LinkToAttributeName = "activityid";
                     
                            // Create the query.
                            QueryExpression query = new QueryExpression();

                            // Set the properties of the query.
                            query.EntityName = EntityName.activitypointer.ToString();
                            query.ColumnSet = new AllColumns();
                            query.LinkEntities = new LinkEntity[] {link};

                            // Create the request object.
                            RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

                            // Set the properties of the request object.
                            retrieve.Query = query;
               

    Wednesday, January 30, 2008 5:52 AM
    Moderator
  • As Imran suggests, you can get the results from code using the CRM web service, or from a SQL statement, but you cannot create an Advanced Find in CRM 3.0 to display the results
    Wednesday, January 30, 2008 12:24 PM
    Moderator
  • I suggest you use FetchXML.
    google the FetchXML builder.

    It is easier to write an maintain.
    The only difference is that it will return the result set in XML at which point all you have to do is look for your data.

    Oshri Cohen
    http://mymscrm3.blogspot.com/
    http://www.linkedin.com/in/oshricohen

     

    Thursday, January 31, 2008 6:48 PM
  • Hi,

     

    I have a question with regards to the code written above. In my opinion the query would only show the results of one entity. How can I adjust the query so that it shows also the columns of the linked entity?

    As in the example:

    Select a.name, b.money FROM contact a, account b WHERE a.id = b.id

     

    Thanks in advance!

     

    Daniel

     

    Monday, September 22, 2008 4:54 PM
  • Hi Daniel.

    You could actually just write a SQL query against the entity view (e.g. FilteredContact) which from my knowledge is still supported. As it is a view you are able to enfore the security model (i.e. it will return only records for which the user in question has rights to read).

    I wouldn't usually recommend this solution, but in certain cases (as yours seems to be) it may be the best/only solution.

    Cheers,

    Karlo


    Monday, September 22, 2008 5:58 PM
  • Thanks for the reply, but I need to do it with the queryexpression and the linkEntity. Is there no way to do that?

     

    Thanks,


    Daniel

     

    Tuesday, September 23, 2008 8:22 AM
  • Hi Daniel,

    From my experience that is the limitation in CRM 3.0. You can only retrieve values from one particular entity at a time. Much like Advanced Find, you can't retrieve information from more than one entity.

    Alternately, you could look at mapping data from the parent field to the child field.

    Cheers,

    Karlo
    Tuesday, September 23, 2008 3:53 PM