Multi-select choices and Dataverse storage
Introduction
You're probably familiar with the "Choice" data type in Power Apps which allows you to select a single item from a drop-down list.
You may also be familiar with multi-select column (called Choices) which allows you to select multiple items in a drop-down list.
While a single Choice column is stored in the underlying Azure SQL database as 2 fields (one for the label and another for the value), what about the storage of a multiple-select column?
You will have the answer in this article.
Summary of Choices column features
When you add a multi-select column, you can specify multiple values that will be available for users to select. When users fill out the form they can select one, multiple, or all the values displayed in a drop-down list.
Multi-select columns can be used with read-only grids, editable grids, and most forms. But they have several limitations and can't be used with:
- Workflows
- Business process flows
- Actions
- Dialogs
- Business rules
- Charts
- Rollup columns or calculated columns.
See below a Choices field in a form:
Choices field with the focus inside:
Choices field with no focus inside. Note that items are separated by a comma.
You can also add a Choices column in a view. Note that items are separated by a semicolon.
Enable TDS endpoint
To be able to see how a multi-select column is stored in the underlying Azure SQL database, you must enable the TDS endpoint (Tabular Data Stream) for your Dataverse environment.
You can read the following articles on TDS:
- https://dynamics-chronicles.com/article/how-access-dynamics-365-online-sql-server-database
- https://dynamics-chronicles.com/article/dynamics-365-dataverse-tds-endpoint-control-access-and-rights
In summary:
Go to admin.powerplatform.microsoft.com and select your environment
Then click on Settings and navigate to Product > Features
Check that TDS endpoint is activated
Storage in Azure SQL database
To illustrate the storage of a multiple-select column I created 2 columns in my Dataverse environment: the 2 columns have the 'Choice' type but one has the multi-select option activated and the other does not.
- Column with logical name = 'elca_singlehobby' : Choice column with the multi-select option disabled
- Column with logical name = 'elca_hobbies' : Choice column with the multi-select option enabled
Then with SQL Server Management Studio (SSMS) we can connect to the database and view the result of the storage of the 2 columns :
As you can see 2 fields are created in the SQL database to store the column 'elca_singlehobby': one field with the type int and another field with the type nvarchar .
And for the multi-select column 'elca_hobbies' only one field of type nvarchar is created in the SQL database.
We can see the values of the fields in the SQL database by running a simple SQL query:
We can see that the muti-select value is the list of values separated by commas.
What about Azure Synapse Link?
And if you try to retrieve a multi-select column with Azure Synapse Link, what will you get?
As you can see below you will get the list of values ... but separated by semicolons.
Published on:
Learn moreRelated posts
Microsoft Copilot / AI – A day in the life of a power platform developer
AI, particularly Microsoft Copilot, has significantly impacted daily work, enhancing productivity and streamlining processes. It has been used...
Announcing Power Platform Well-Architected
Announcing Power Platform Well-Architected, a comprehensive framework designed to help you maximize the value of your investments in modern ap...
Power Platform – May 2024– Screenshot Summary
The Power Platform May 2024 Monthly Community Call was packed with exciting updates and demos. The highlights of this call included presentati...
Effectively manage site security from the Power Platform Admin Center
Learn how to effectively manage site security with the Power Platform Admin Center in this informative post. Security is a top priority for an...
Simplify administration with Environment Groups in Power Platform
If you're looking for an easier way to manage and administer your Microsoft Power Platform environments, then this blog post is for you. Here,...
Power Platform – Environment groups and environment routing general availability
The Power Platform is set to improve its governance capabilities through the release of Environment groups and improvements to Default Environ...
Microsoft 365 & Power Platform Call (Microsoft Speakers) – May 14th, 2024 – Screenshot Summary
This post provides a summary of the May 14th, 2024 Microsoft 365 & Power Platform community call, featuring updates and demos from Microsoft s...
From Traditional Coding to Power Platform Prowess: The Developer's Guide to Innovation and Adapta...
Join James Weaver in an insightful discussion, where he delves into the intersection of traditional coding and low-code development, highlight...
Power Platform admin center – Power Apps maker welcome content is now Generally Available
Power Platform admins now have the ability to customize the welcome content that Power Apps maker portal users see when logging in, thanks to ...
Power Pages activity logging | Power Platform Shorts
Looking for a way to enhance your Power Platform admin skills? Check out the latest video in the "What's New" series, which explores the conce...