locked
What table are new custom fields for contacts stored in, and how to link back to the contacts table? RRS feed

  • Question

  • Hi,

    We are using Dynamics CRM 2011, and I am connecting to the database through the CRM SDK with LINQ.

    I have added a custom field to the Contacts entity, and I want to write some C# code to search in the database for some contacts and then to update the custom field for each of them.

    I am using the following code from the SDK (note there is more code for authentication to the server which I am not including here):

    OrganizationServiceProxy _serviceProxy;
    IOrganizationService _service;
    ServiceContext svcContext = new ServiceContext(_service);

    From this I can see all the contacts in "svcContext.ContactSet".

    However I am not sure which table the custom fields are stored in? 

    And how to link that table back to the contact table?

    Any help would be appreciated thanks.
    Friday, September 20, 2013 12:45 AM

All replies

  • Everything (system and custom fields) can be accessed the same way - if you look at this example from the SDK - you could modify it to use a custom field.

    Sample: Create a LINQ Query

    var accounts = from a in svcContext.AccountSet
         select new Account
         {
              Name = a.Name,
              Address1_County = a.Address1_County,
              new_Description2 = a.new_Description2
         };

     

    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Friday, September 20, 2013 1:31 AM
    Moderator
  • Every entity in CRM 2011 has 2 tables in the database. A Base table and an ExtensionBase table. While Base table has the OOB fields, Custom fields on any entity will be stored in the ExtensionBase table. In your case it will be ContactExtensionBase table.

    It is always preferable to use views instead of tables directly. You could use the Contact view in your query. This view will give you all the attributes of cotact entity.


    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    Friday, September 20, 2013 1:38 AM
  • Hi,

    Thanks for the info. Yes I have been using the sample code from the CRM SDK as a base for the LINQ code I am writing.

    Under "svcContext" I can see that there is a "ContactSet" which is the list of all the Contact records and I can see them fine. however I want to be able to see the extra columns which have been added to the Contacts table, for example we have a column called "xmas party" which is a custom field that was added to the Contacts table.

    I want to be able to use some LINQ code to update this custom field "xmas party" for a large number of contacts, as I don't want to have to go into each Contact record manually and change it, in the CRM front end GUI as that would take a very long time.

    When I look at "svcContext" I cannot see anything underneath it called for example "ExtensionBaseSet" of "ContactExtensionBaseSet" etc.

    I can see "FieldPermissionSet" and "FieldSecurityProfileSet" but they seem to be the only thing related to Fields.


    For example I would be writing some code like:

                    var myContactsWithAllFields =
                        from contact in svcContext.ContactSet
                        join contactCustomColumns in svcContext.XXXX on contact.ID equals contactCustomColumns.ID
                        select myContactsWithAllFields;


    where "XXXX" is the name of the other table or "set" which I need to join to.

    If you can just point me in the right direction that would be greatly appreciated.

    Thanks.

    David


    • Edited by David Aston Friday, September 20, 2013 2:44 AM
    Friday, September 20, 2013 2:38 AM
  • As Jason explained in the above reply, though the custom fields are stored physically in a separate table, the ContactSet will provide you access to all attributes system as well as custom. You do not need any join to access custom attributes.

    If you do not see your custom attributes recreate your early bound classes b executing the crmsvcutil.

    Also you cannot do a bulk update like

    update table contact set set xxx = yyy where condition

    You need to loop through the records found through your query and then set the values for individual contacts.

    HTH

    Sam


    Dynamics CRM MVP | Inogic | http://inogic.blogspot.com| news at inogic dot com

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Proposed as answer by Sam - Inogic Friday, September 20, 2013 3:20 AM
    Friday, September 20, 2013 3:20 AM