Forum Discussion
Getting Local Choice column labels (Multi select)
- 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
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