locked
New to CRM, using the service methods available RRS feed

  • Question

  • Hi folks,

    First, let me tell you that this is the first time I'm using CRM code to access the tables, so please bear with me.

    The standard ContactBase table has been altered by the user and has added a few new fields as customization for what the company needs.

    I have the seen the quickstart.cs code to create, update, delete and retrieve rows, but they are all focused on GUID that's been created by CRM.

    Now, my problem is I need to check one of the new fields added before I use the existing built-in "Insert" crm service method to check for row existence. I have tried to use the RetrieveMultiple method, and ran the SQL profiler to see what it actually does. It accesses the ContactBase table with an alias of "contact0". It returns the id that I want, as seen by the code below, but my problem is how to display several more fields (like select * from [tablename]) and why does it throw an error when I add another column in the columnset, when in fact the column is already in the table?

    ConditionExpression condition1 = new ConditionExpression();
                    condition1.AttributeName = "new_field1";
                    condition1.Operator = ConditionOperator.Equal;
                    condition1.Values.Add("123");
    
    
                    FilterExpression filter1 = new FilterExpression();
                    filter1.Conditions.Add(condition1);
    
                    QueryExpression query = new QueryExpression("contact"); ;
                    query.ColumnSet.AddColumns("new_field1", "new_employeeID");
                    query.Criteria.AddFilter(filter1);
    
                    var x = _orgService.RetrieveMultiple(query);
                    foreach (var entity in x.Entities)
                    {
                        Entity testAccount = (Entity)entity;
                        MessageBox.Show(testAccount.Attributes["new_field1"].ToString());
                    }

    This code will throw an exception 

    'Contact' entity doesn't contain attribute with Name = 'new_employeeID.'

     but it is there in the table. If I remove this field, everything works out fine.

    1. What are the entities in CRM and how do the tables being classified under what entity?

    2. How to see the join under the hood when the service methods are called? Can you provide a link to the technical details and usage for coding?

    3. I don't now if using RetrieveMultiple is the right way to go to query row per row, because I have almost 1 million rows to check? Any other way to query the ContactBase?

    Thanks

     

     

     

     

     

     

     

    Wednesday, March 26, 2014 4:50 AM

Answers

  • Hi

    You must use the schema-name, and not the DisplayName or Name of a field in the code. It is the same as the field name, but all lower case.

    The navigation above is in the CRM UI.

    Have you considered using OrganizationServiceContext(OSC) and Linq instead of a query expression? With retrieve multiple you need to consider CRM paging also, since retrieve multiple will not return more than 5000 rows per query, while a linq query using OCS will allow you to enumerate the whole table if you have to.



    Best Regards Jens Egil Evensen


    Thursday, March 27, 2014 11:47 AM

All replies

  • Hello,

    Please see my response

    1. What are the entities in CRM and how do the tables being classified under what entity?

    > You have not mentioned which CRM version you are using, if it is a crm 2011, then ever entity will have two table for example account, there will be accountbase and accountextensionbase table, but if you are using ms crm 2013 there will be just one table, the first one.

    2. How to see the join under the hood when the service methods are called? Can you provide a link to the technical details and usage for coding?

    > I will suggest you to go through Entity Relationship Diagrams

    3. I don't now if using RetrieveMultiple is the right way to go to query row per row, because I have almost 1 million rows to check? Any other way to query the ContactBase?

    > you are using correct method to get your data, but if you are getting error it means column name is not correct, please recheck it


    Our Website | Our Blog | Follow US | My Facebook Page | Microsoft Dynamics CRM 2011 Application Design
    Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    Wednesday, March 26, 2014 5:49 AM
    Moderator
  • I think you are making a bit mistake while co
    QueryExpression query = new QueryExpression();
                    query.EntityName = "account";
                    query.ColumnSet = new ColumnSet();
                    query.ColumnSet.Columns.Add("name");
                    query.ColumnSet.Columns.Add("emailaddress1");
                    query.Criteria = new FilterExpression();
                    query.Criteria.Filters.Add(filter);
    EntityCollection ec = _orgService.RetrieveMultiple(query);
    
        foreach (Entity act in ec.Entities)
        {
           Console.WriteLine("account name:" + act["name"]);
           Console.WriteLine("email:" +act["emailaddress1"]);
           
        }


    ding for your query. I've change the query expression in the following manner.
    Wednesday, March 26, 2014 12:19 PM
  • Thanks, I'm getting somewhere bit by bit. What I don't understand is WHERE the attributes names are coming from. Certainly the naming in ContactBase is different compared to the attribute names. In code, the attribute name is case-sensitive, so if do this:

    MessageBox.Show(testAccount.Attributes["EmailAddress1".ToString()); 

    which is the same capitalization as in the contactbase table, I get the error "Attribute name not found" or something similar, whereas if I do this:

    MessageBox.Show(testAccount.Attributes["emailaddress1".ToString()); 

    it works. I don't understand how to get the right name (including the capitalization) of attributes, I do the debug, then click Shift-F9 to see what other attributes as there in the Contact base, but it's different in code?

    Is this a special class I could use to get all attribute names with all their right spelling?

     QueryExpression query2 = new QueryExpression("contact");
                    query2.ColumnSet.AddColumns("address1_line1");
                    query2.ColumnSet.AddColumns("address1_line2");
                    query2.ColumnSet.AddColumns("address1_freighttermscode");
                    query2.ColumnSet.AddColumns("mobilephone");
                    query2.ColumnSet.AddColumns("emailaddress1");
                    
                    var x2 = _orgService.RetrieveMultiple(query2);
                    foreach (var entity in x2.Entities)
                    {
                        Entity testAccount = entity;
                        MessageBox.Show(testAccount.Attributes["emailaddress1"].ToString());
                    }

    The "entity" variable in foreach loop displays attributes that cannot be used with testAccount.Attributes["columnname"].ToString().

    Any ideas?

    Thanks


    • Edited by LayneDad Thursday, March 27, 2014 4:23 AM
    Thursday, March 27, 2014 4:23 AM
  • Hello,

    You need to make sure fields name that will be used in late bound will be lower case normally, you can navigate to your solution see fields name (Settings-> Customization->Customize your system->Entity->Fields)


    Our Website | Our Blog | Follow US | My Facebook Page | Microsoft Dynamics CRM 2011 Application Design
    Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    Thursday, March 27, 2014 5:13 AM
    Moderator
  • Hello again, thank you for the navigation tip, but unfortunately I'm at a lost how to do that. You see, I have my own solution with multiple projects and the only way I am able to access the service methods of Organization service is because I have added the microsoft.xrm.proxy and 2 other DLLs. 

    So when you say 

    Settings-> Customization->Customize your system->Entity->Fields

    is this applicable to my own VS2013 solution, or only for a certain CRM UI?

    Thanks

    Thursday, March 27, 2014 7:46 AM
  • Hi

    You must use the schema-name, and not the DisplayName or Name of a field in the code. It is the same as the field name, but all lower case.

    The navigation above is in the CRM UI.

    Have you considered using OrganizationServiceContext(OSC) and Linq instead of a query expression? With retrieve multiple you need to consider CRM paging also, since retrieve multiple will not return more than 5000 rows per query, while a linq query using OCS will allow you to enumerate the whole table if you have to.



    Best Regards Jens Egil Evensen


    Thursday, March 27, 2014 11:47 AM
  • I think you are making a bit mistake while co
    QueryExpression query = new QueryExpression();
                    query.EntityName = "account";
                    query.ColumnSet = new ColumnSet();
                    query.ColumnSet.Columns.Add("name");
                    query.ColumnSet.Columns.Add("emailaddress1");
                    query.Criteria = new FilterExpression();
                    query.Criteria.Filters.Add(filter);
    EntityCollection ec = _orgService.RetrieveMultiple(query);
    
        foreach (Entity act in ec.Entities)
        {
           Console.WriteLine("account name:" + act["name"]);
           Console.WriteLine("email:" +act["emailaddress1"]);
           
        }


    ding for your query. I've change the query expression in the following manner.

    THank you brother, much apprecaited
    Tuesday, April 1, 2014 7:26 AM
  • Yes, I have used OSC, thank you for the recommendation,although my dataset is below 5k so I haven't tested if it will return a the full dataset if it exceeds 5k, I guess I will need to bulk create some data.  :)

    Tuesday, April 1, 2014 7:28 AM
  • Thank you Mahender Pal, I'm using 2013 version, I'm almost complete with the code but is limited only to what I have searched from the net, so the code itself is not polished. I will post another thread, I hope you can help. cheers
    Tuesday, April 1, 2014 7:30 AM