locked
Retrieving multiple addresses RRS feed

  • Question

  • Hi,

    I am trying to retrieve multiple addresses from the customer name.  However, the inner join expression seems not to be working.
    Am I missing anything based on the below codes?

    Thanks in advance,

    Young


    ----------------------------------------------------------------------------------------------------------------------------------------

    ColumnSet cols = new ColumnSet();

    cols.Attributes = new string[] { "name", "accountid"};//, "customeraddressid", "name", "line1", "line2", "city", "stateorprovince", "postalcode" };

    LinkEntity innerJoinOne = new LinkEntity();

    innerJoinOne.LinkFromEntityName = EntityName.account.ToString();

    innerJoinOne.LinkFromAttributeName = "accountid";

    innerJoinOne.LinkToEntityName = EntityName.customeraddress.ToString();

    innerJoinOne.LinkToAttributeName = "parentid";

    innerJoinOne.JoinOperator = JoinOperator.Inner;

    ConditionExpression where = new ConditionExpression();

    where.AttributeName = "name";

    where.Operator = ConditionOperator.Like;

    where.Values = new string[]{txtName.Text.Trim()}; // txtAccountName.Text.Trim() };

    // set the properties of the filterexpression

    FilterExpression filter = new FilterExpression();

    filter.FilterOperator = LogicalOperator.And;

    filter.Conditions = new ConditionExpression[] { where };

    innerJoinOne.LinkCriteria = filter;

    //// create the queryexpression object

    QueryExpression query = new QueryExpression();

    query.EntityName = EntityName.account.ToString();

    query.LinkEntities = new LinkEntity[] { innerJoinOne };

    query.ColumnSet = cols;

    // Retrieve the Accounts

    BusinessEntityCollection addresses = new BusinessEntityCollection();

    addresses = crmService.RetrieveMultiple(query);

    Wednesday, March 10, 2010 11:57 PM

Answers

All replies

  • Hi
    For like operation use "%" for your value , just like in a sql query.
    so your condition expression will become like this.

    ConditionExpression where = new ConditionExpression();

    where.AttributeName = "name";

    where.Operator = ConditionOperator.Like;

    where.Values = new string[]{ "%" + txtName.Text.Trim() + "%" }; // txtAccountName.Text.Trim() };

    Note, the % (two times) in the above.

    i think the rest of the code seems to be fine.


    Muhammad Ali Khan
    My MS CRM blog
    • Proposed as answer by Muhammad Ali Khan Thursday, March 11, 2010 3:11 AM
    • Marked as answer by Jim Glass Jr Thursday, March 11, 2010 4:14 PM
    • Unmarked as answer by CRM2500 Thursday, March 11, 2010 6:41 PM
    Thursday, March 11, 2010 3:11 AM
  • Hi,

    The result is still blank and no address records found.  If the company name is "Test", it should return several addresses such as "Office", "HQ", etc.
    Something needs to be changed, I guess.  Any thoughts?

    Young
    Thursday, March 11, 2010 6:43 PM
  • the result of like operator is like this.
    if you search for %Test% it will search all the word having "Test" in between. but it will not search "office" and "HQ".

    check these links on how to use like operator with %.
    http://msdn.microsoft.com/en-us/library/cc151203.aspx
    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/d26a6d7d-f8ed-48ef-b90f-a8e287325578

    http://blogs.geekdojo.net/richard/archive/2007/06/07/136214.aspx

    Muhammad Ali Khan
    My MS CRM blog
    Thursday, March 11, 2010 6:57 PM
  • If you look at my query, I am doing inner join "account" with "customeraddress" based on the "account id" in account entity and "parent id" in customeraddress entity.  Since the customer address can have multiple addresses with address names.  "HQ" and "office" means the address names not the company names.
    Can you help me show how to retrieve multiple addresses  based on a customer name?
    It has one-to-many relationship here.  Hope this makes sense to you.

    Young
    Thursday, March 11, 2010 9:12 PM
  • You need to swap your entities around. You want all customer addresses but your queryexpression is set to retrieve accounts. You need to set the query expression for customeraddress and chnage the linkentity from and to around. Then you can add a filter to the linkentity that is based on the company name.
    MSCRM Bing'd - http://bingsoft.wordpress.com
    Thursday, March 11, 2010 11:36 PM
    Moderator
  • Hi Aloha.

    show how to retrieve multiple addresses  based on a customer name?

    In condition expression, you can specify the criteria from the primary entity.

    ConditionExpression where = new ConditionExpression();
    where.AttributeName = "name";
    where.Operator = ConditionOperator.Like;
    where.Values = new string[]{"%" + txtName.Text.Trim() + "%"};

    But if you make the customeraddress as the primary entity in the QueryExpression, it will not work on the account name.
    So inorder to achieve this now. you have to do this.

    1) First use the QueryByAttribute to get the AccountId(based on the name attribute) as shown below

    ColumnSet cols = new ColumnSet();
    cols.Attributes = new string [] {"name", "accountid"};

    // Create the query object.
    QueryByAttribute query = new QueryByAttribute();
    query.ColumnSet = cols;
    query.EntityName = EntityName.account.ToString();

    query.Attributes = new string [] {"name"};
    query.Values = new string [] {"name of the account here"};

    // Execute the retrieval.
    BusinessEntityCollection retrieved = service.RetrieveMultiple(query);

    account objAccount = (account)retrieved.BusinessEntities[0];
    Guid accountID = objAccount.accountId;

    2) Once you have the accountId, use the below QueryExpression to reterive the customeraddress.
    ColumnSet cols = new ColumnSet();

    cols.Attributes = new string[] { "customeraddressid", "name", "line1", "line2", "city", "stateorprovince", "postalcode" };

    LinkEntity innerJoinOne = new LinkEntity();

    innerJoinOne.LinkFromEntityName = EntityName.customeraddress.ToString();

    innerJoinOne.LinkFromAttributeName = "parentid";

    innerJoinOne.LinkToEntityName = EntityName.account.ToString();

    innerJoinOne.LinkToAttributeName = "accountid";

    innerJoinOne.JoinOperator = JoinOperator.Inner;

    ConditionExpression where = new ConditionExpression();
    where.AttributeName = "parentid";
    where.Operator = ConditionOperator.Like;
    where.Values = new Guid[]{accountID};

    // set the properties of the filterexpression

    FilterExpression filter = new FilterExpression();

    filter.FilterOperator = LogicalOperator.And;

    filter.Conditions = new ConditionExpression[] { where };

    innerJoinOne.LinkCriteria = filter;

    //// create the queryexpression object

    QueryExpression query = new QueryExpression();

    query.EntityName = EntityName.account.ToString();

    query.LinkEntities = new LinkEntity[] { innerJoinOne };

    query.ColumnSet = cols;

    // Retrieve the Accounts

    BusinessEntityCollection addresses = new BusinessEntityCollection();

    addresses = crmService.RetrieveMultiple(query);


    Muhammad Ali Khan
    My MS CRM blog
    Friday, March 12, 2010 4:21 AM
  • //// create the queryexpression object

    QueryExpression query = new QueryExpression();

    query.EntityName = EntityName.customeraddress.ToString(); // Change this from account to customeraddress

    query.LinkEntities = new LinkEntity[] { innerJoinOne };


    MSCRM Bing'd - http://bingsoft.wordpress.com
    Friday, March 12, 2010 8:16 AM
    Moderator
  • Hi Aloha

    You can modify your Query Expression as below to obtaining your data as required

            AllColumns cols = new AllColumns();             
            LinkEntity innerJoinOne = new LinkEntity();
            innerJoinOne.LinkFromEntityName = EntityName.customeraddress.ToString();
            innerJoinOne.LinkFromAttributeName = "parentid";
            innerJoinOne.LinkToEntityName = EntityName.account.ToString();
            innerJoinOne.LinkToAttributeName = "accountid";
            innerJoinOne.JoinOperator = JoinOperator.Inner;
            ConditionExpression where = new ConditionExpression();
            where.AttributeName = "name";
            where.Operator = ConditionOperator.Like;
            where.Values = new string[] {'%'+ txtAccountName.Text.Trim() + '%' }; 
            // set the properties of the filterexpression
            FilterExpression filter = new FilterExpression();
            filter.FilterOperator = LogicalOperator.And;
            filter.Conditions = new ConditionExpression[] { where };
            innerJoinOne.LinkCriteria = filter;
            //// create the queryexpression object
            QueryExpression query = new QueryExpression();
            query.EntityName = EntityName.customeraddress.ToString();
            query.LinkEntities = new LinkEntity[] { innerJoinOne };
            query.ColumnSet = cols;
            // Retrieve the Accounts
            BusinessEntityCollection addresses = new BusinessEntityCollection();
            addresses = service.RetrieveMultiple(query);

    Note: You wont be able to get account name since you have a attribute with same name "name" in customer address entity.

    If account name is a mandatory requirement you can go for a FTECHXML.

    string fetchxml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'><entity name='customeraddress'><attribute name='name'/><attribute name='customeraddressid'/><attribute name='line3'/><attribute name='line2'/><attribute name='line1'/><order attribute='name' descending='false'/><link-entity name='account' from='accountid' to='parentid' alias='aa'><attribute name='name'/><filter type='and'><condition attribute='name' operator='like' value='&#37;" + txtAccountName.Text.Trim() +" &#37;'/></filter></link-entity></entity></fetch>";

    string address = service.Fetch(fetchxml);     

    Regards
    Vinoth
    Friday, March 12, 2010 9:56 AM
  • Thanks for your help.

    The whole purpose of using join statement is to avoid the query execution twice.  Based on your comment, I have to execute twice that is what I want to avoid.
    In the SQL script below,

    --------------------------------------------------------------------------
    select

     

    c.Name, c.Line1, c.Line2, c.Line3, c.City, c.StateOrProvince

    from

     

    AccountBase a

    inner

     

    join CustomerAddressBase c on c.ParentId = a.AccountId

    where

     

    a.Name = 'Test Company"

    ---------------------------------------------------------------

    Is there any way I can execute with a single QueryExpression?

    Thanks again,

    Young

     

     

     

    Friday, March 12, 2010 7:28 PM
  • Hi Alofa
    I think you can achieved this using Fetch xml.
    Writing fetchxml can be a little messy, so use this tool to generate the fectxml for you.
    http://www.jamesdowney.net/blog/page/Request-FetchXML-Builder-Key.aspx
    http://www.jamesdowney.net/blog/category/FetchXML.aspx
    and then execute the fetch xml like this.

    String result= service.Fetch(fetchxml);
    Muhammad Ali Khan
    My MS CRM blog
    • Marked as answer by Jim Glass Jr Friday, March 12, 2010 8:03 PM
    Friday, March 12, 2010 7:52 PM