locked
MS CRM 2011-What is the tablename where OptionSets's Values are stored? RRS feed

  • Question

  • Hi All,

    I am looking for the tablename of OptionSets in MSCRM 2011 are stored...

    I found VIEW - "OptionSetView" where the name of the optionset can be found....but wher can i find the values that are stored???

    i am trying it hard for past few days....wher can i find the MSCRM 2011 database structure information.....

    Thanks in Advance....

    Regards...

     




    Monday, April 18, 2011 11:00 AM

Answers

  • Check this,

    SELECT EV.LogicalName AS Entity, ALV.Name AS Attribute, LLV.Label, APV.Value
    FROM LocalizedLabelView as LLV
    INNER JOIN AttributePicklistValueView as APV ON LLV.ObjectId = APV.AttributePicklistValueId
    INNER JOIN OptionSetView as O ON APV.OptionSetId = O.OptionSetId
    INNER JOIN AttributeLogicalView as ALV ON O.OptionSetId = ALV.OptionSetId
    INNER JOIN EntityView as EV ON ALV.EntityId = EV.EntityId
    where ALV.Name like 'ers_%'
    order by EV.LogicalName, ALV.Name, APV.Value


    Ajai
    • Marked as answer by Red Devils 82 Tuesday, April 19, 2011 9:31 AM
    Tuesday, April 19, 2011 7:10 AM
  • You might be able to modify this query to get what you want: 
    SELECT lbl.Label, AV.Value, A.Name AS Attribute, E.LogicalName AS Entity,
    O.Name as OptionSetName, O.OptionSetId as OptionSetId
    FROM LocalizedLabelView as lbl
    INNER JOIN AttributePicklistValueView as AV ON lbl.ObjectId = AV.AttributePicklistValueId
    INNER JOIN OptionSetView as O ON AV.OptionSetId = O.OptionSetId
    INNER JOIN AttributeLogicalView as A ON O.OptionSetId = A.OptionSetId
    INNER JOIN EntityView as E ON A.EntityId = E.EntityId
    WHERE lbl.LanguageId = 1033 AND lbl.ObjectColumnName = 'DisplayName'

    Monday, April 18, 2011 1:23 PM
  • Hi Chris,

    i tried your query..but still i m not able to find...the custom optionsets we create in MSCRM2011....'s vaules...

    thank you for your query..n reply....

    I m still not finding the best solution for this problem...

    Regards

     

    the values are in the first field Label

    try this :

    SELECT lbl.Label as 'Optionset Values', O.Name as OptionSetName

    FROM LocalizedLabelView as lbl
    INNER JOIN AttributePicklistValueView as AV ON lbl.ObjectId = AV.AttributePicklistValueId
    INNER JOIN OptionSetView as O ON AV.OptionSetId = O.OptionSetId
    INNER JOIN AttributeLogicalView as A ON O.OptionSetId = A.OptionSetId
    INNER JOIN EntityView as E ON A.EntityId = E.EntityId
    WHERE lbl.LanguageId = 1033 AND lbl.ObjectColumnName = 'DisplayName'


    and O.Name = '<your custom optionset name>'

     

     

     



    vishal swami

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

    http://msdynamics4you.blogspot.com

    Tuesday, April 19, 2011 6:42 AM

All replies

  • Have you tried StringMap?

    select * from <orgname>_mscrm.dbo.FilteredStringMap


    Ajai
    Monday, April 18, 2011 12:29 PM
  • yes i hav tried......FilteredStringMap........

    sory not to mention it earlier....but it doesnot display new created optionsets....

    regards.

    Monday, April 18, 2011 12:32 PM
  • Try

    select * from LocalizedLabel where label like '%your text%'


    Ajai
    Monday, April 18, 2011 12:55 PM
  • You might be able to modify this query to get what you want: 
    SELECT lbl.Label, AV.Value, A.Name AS Attribute, E.LogicalName AS Entity,
    O.Name as OptionSetName, O.OptionSetId as OptionSetId
    FROM LocalizedLabelView as lbl
    INNER JOIN AttributePicklistValueView as AV ON lbl.ObjectId = AV.AttributePicklistValueId
    INNER JOIN OptionSetView as O ON AV.OptionSetId = O.OptionSetId
    INNER JOIN AttributeLogicalView as A ON O.OptionSetId = A.OptionSetId
    INNER JOIN EntityView as E ON A.EntityId = E.EntityId
    WHERE lbl.LanguageId = 1033 AND lbl.ObjectColumnName = 'DisplayName'

    Monday, April 18, 2011 1:23 PM
  • Hi Ajaikumari,

    i tried your query...it helped me....but still not able to acheive my moto of getting values / options of the MS CRM 2011 Option Sets.

    thank you for your reply....can u guide me more on this...

     

    Regards...

     

    Tuesday, April 19, 2011 4:49 AM
  • Hi Chris,

    i tried your query..but still i m not able to find...the custom optionsets we create in MSCRM2011....'s vaules...

    thank you for your query..n reply....

    I m still not finding the best solution for this problem...

    Regards

     

    Tuesday, April 19, 2011 4:52 AM
  • Hi Chris,

    i tried your query..but still i m not able to find...the custom optionsets we create in MSCRM2011....'s vaules...

    thank you for your query..n reply....

    I m still not finding the best solution for this problem...

    Regards

     

    the values are in the first field Label

    try this :

    SELECT lbl.Label as 'Optionset Values', O.Name as OptionSetName

    FROM LocalizedLabelView as lbl
    INNER JOIN AttributePicklistValueView as AV ON lbl.ObjectId = AV.AttributePicklistValueId
    INNER JOIN OptionSetView as O ON AV.OptionSetId = O.OptionSetId
    INNER JOIN AttributeLogicalView as A ON O.OptionSetId = A.OptionSetId
    INNER JOIN EntityView as E ON A.EntityId = E.EntityId
    WHERE lbl.LanguageId = 1033 AND lbl.ObjectColumnName = 'DisplayName'


    and O.Name = '<your custom optionset name>'

     

     

     



    vishal swami

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

    http://msdynamics4you.blogspot.com

    Tuesday, April 19, 2011 6:42 AM
  • Check this,

    SELECT EV.LogicalName AS Entity, ALV.Name AS Attribute, LLV.Label, APV.Value
    FROM LocalizedLabelView as LLV
    INNER JOIN AttributePicklistValueView as APV ON LLV.ObjectId = APV.AttributePicklistValueId
    INNER JOIN OptionSetView as O ON APV.OptionSetId = O.OptionSetId
    INNER JOIN AttributeLogicalView as ALV ON O.OptionSetId = ALV.OptionSetId
    INNER JOIN EntityView as EV ON ALV.EntityId = EV.EntityId
    where ALV.Name like 'ers_%'
    order by EV.LogicalName, ALV.Name, APV.Value


    Ajai
    • Marked as answer by Red Devils 82 Tuesday, April 19, 2011 9:31 AM
    Tuesday, April 19, 2011 7:10 AM
  • hello everyone,

    i have actually achieved the result i was looking for. Thanks for all your replies....Chris,Ajaikumari,VishalSwami...

    I encountered a very unpredicted behaviour of MSCRM 2011 OptionSets.

    Following things happened when I was playing around:-

    1) i created a optionset,published it.

    2) i didnot put it on any form of any entity and looked for it through querries...

    3) next , i put it on account form(create a new field-optionset thru main form of account and selected existing)

    4)now i again looked for the optionset with the same quries and the optionset was present...

    anyone if would like to put their comments plz do....

     

    Thanks and Regards...

     

     

    Tuesday, April 19, 2011 9:38 AM