locked
SQL : Retreieve the DisplayNames for Columns in Filtered Views RRS feed

  • Question

  • I'm using the Filtered views in SQL to bring back all the records in particular entities. The good thing about filtered views is that they show you both the unique identifier and the name (so for example Modifiedby (the unique identifier) and ModifiedByName (the more user friendly string).

    I am using Dynamic SQL to do this for a large number of entities and this works fine but I want to change the column headings in the queries I generate to be the Display Name of the column rather than the SQL Server column name. I have previously used "LocalizedLabelView" to give me the Display Name to column name mappings but that would map ModifiedBy to the display name and I also need to map "ModifiedByName to the display name. Is there a view or table that holds the same level of information for Filtereed Views or is there some logic I can use like "if the column name ends in the word 'name' then find a column called the same thing minus the name and look that up"?

    Many thanks for any help that can be offered... I'm new here!

    Mike

    Thursday, January 3, 2013 12:28 PM

Answers

  • You can use 'LocalizedLabelView' for mapping ModifiedBy to its display name and use 'MetadataSchema.Attribute' table to map ModifiedByName to its display name (Use PhysicalName) field of MetadataSchema.Attribute table for this.

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

    • Proposed as answer by Imran I Abbasi Friday, January 4, 2013 4:19 AM
    • Marked as answer by Imaginax Tuesday, January 22, 2013 12:04 AM
    Friday, January 4, 2013 4:19 AM

All replies

  • Hi,

    I don't know where are you using that, but i give you my experience. In my case i discover the display names through metadata with C#. Check the code:

    IMetadataService mService = context.CreateMetadataService(true);
    RetrieveEntityRequest entityRequest = new RetrieveEntityRequest();
    entityRequest.RetrieveAsIfPublished = false;
    entityRequest.LogicalName = EntityName.contact.ToString();
    entityRequest.EntityItems = EntityItems.EntityOnly;
         
    RetrieveEntityResponse entityResponse = (RetrieveEntityResponse)mService.Execute(entityRequest);    
    EntityMetadata retrievedEntityMetadata = entityResponse.EntityMetadata;
    
    foreach (var attribute in retrievedEntityMetadata.Attributes)
    {
         string displayName = attribute.DisplayName.LocLabels[0].Label;
    }

    You can do the same via Javascript.

    Hope this helps.

    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    Thursday, January 3, 2013 2:16 PM
  • Thanks for the reply but I'm trying to do it all within T-SQL, in the environment I'm in at the moment we can't write any code (C, VB or even Javascript).

    I was hoping that there would be a view that kind of defines how the filtered views are derived... obviously I could check each one by hand but I'm trying to do it all programatically because we have a couple of dozen entities and a day to do it in!

    Thursday, January 3, 2013 3:57 PM
  • The MetadataSchema.Attribute table has an AttributeOf column that links a name attribute to the corresponding lookup (uniqueidentifier) attribute

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, January 3, 2013 5:09 PM
    Moderator
  • You can use 'LocalizedLabelView' for mapping ModifiedBy to its display name and use 'MetadataSchema.Attribute' table to map ModifiedByName to its display name (Use PhysicalName) field of MetadataSchema.Attribute table for this.

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

    • Proposed as answer by Imran I Abbasi Friday, January 4, 2013 4:19 AM
    • Marked as answer by Imaginax Tuesday, January 22, 2013 12:04 AM
    Friday, January 4, 2013 4:19 AM