Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

Getting Local Choice column labels (Multi select)

I thought I'd post this as I found very little info on the subject.  It started when my Dataflow returned only the integer 'Value' for my Local Choice column.  So I thought I'd add a query to get the...
  • Syndicate_Admin's avatar
    2 years ago

    the WebAPI syntax for multiselect (following the example you posted) should be this one:

     

    https://yourcrm.crm.dynamics.com/api/data/v9.0/EntityDefinitions(LogicalName=’tablename’)/Attributes/Microsoft.Dynamics.CRM.MultiSelectPicklistAttributeMetadata?$select=LogicalName&$filter=LogicalName eq ‘columnname’&$expand=OptionSet

     

    however I personally use another syntax (I post the M code):

    let
    	Source = Json.Document(Web.Contents(DataverseUrl, [RelativePath="/api/data/v9.1/EntityDefinitions(LogicalName='sample_customtable')/Attributes(LogicalName='sample_choices')/Microsoft.Dynamics.CRM.MultiSelectPicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)"])),
    	OptionSet = Source[OptionSet],
    	Options = OptionSet[Options],
    	#"Option Table" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    	#"Expand Columns" = Table.ExpandRecordColumn(#"Option Table", "Column1", {"Value", "Label", "Description", "Color"}, {"Value", "Option.Label", "Option.Description", "Color"}),
    	#"Expand Label" = Table.ExpandRecordColumn(#"Expand Columns", "Option.Label", {"UserLocalizedLabel"}, {"Label.UserLocalizedLabel"}),
    	#"Expand Label UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Label", "Label.UserLocalizedLabel", {"Label"}, {"Label"}),
    	#"Expand Description" = Table.ExpandRecordColumn(#"Expand Label UserLocalizedLabel", "Option.Description", {"UserLocalizedLabel"}, {"Description.UserLocalizedLabel"}),
    	#"Expand Description UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Description", "Description.UserLocalizedLabel", {"Label"}, {"Description"})
    in
    	#"Expand Description UserLocalizedLabel"

     

    and for normal choice

    let
    	Source = Json.Document(Web.Contents(DataverseUrl, [RelativePath="/api/data/v9.1/EntityDefinitions(LogicalName='sample_customtable')/Attributes(LogicalName='sample_choice')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet($select=Options)"])),
    	OptionSet = Source[OptionSet],
    	Options = OptionSet[Options],
    	#"Option Table" = Table.FromList(Options, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    	#"Expand Columns" = Table.ExpandRecordColumn(#"Option Table", "Column1", {"Value", "Label", "Description", "Color"}, {"Value", "Option.Label", "Option.Description", "Color"}),
    	#"Expand Label" = Table.ExpandRecordColumn(#"Expand Columns", "Option.Label", {"UserLocalizedLabel"}, {"Label.UserLocalizedLabel"}),
    	#"Expand Label UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Label", "Label.UserLocalizedLabel", {"Label"}, {"Label"}),
    	#"Expand Description" = Table.ExpandRecordColumn(#"Expand Label UserLocalizedLabel", "Option.Description", {"UserLocalizedLabel"}, {"Description.UserLocalizedLabel"}),
    	#"Expand Description UserLocalizedLabel" = Table.ExpandRecordColumn(#"Expand Description", "Description.UserLocalizedLabel", {"Label"}, {"Description"})
    in
    	#"Expand Description UserLocalizedLabel"

    my tool Dataverse REST Builder has the Power Query (M) syntax for the Retrieve Multiple message, including the code I posted above for getting the choice/multiselect choice labels

    hope it helps