Loading...

Multi-select choices and Dataverse storage

Multi-select choices and Dataverse storage
Multi-select choices and Dataverse storage Stephane Pelhatre Thu, 03/14/2024 - 18:51
Body

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:

form display

 

Choices field with the focus inside:

field display 2

 

Choices field with no focus inside. Note that items are separated by a comma.

field 1

 

You can also add a Choices column in a view. Note that items are separated by a semicolon.

view

 

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:

In summary:

Go to admin.powerplatform.microsoft.com and select your environment  

Then click on Settings and navigate to Product > Features

admin_feature

 

Check that TDS endpoint is activated

TDS

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 :

SQL fields

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:

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.

Synapse

 

    Published on:

    Learn more
    Featured Articles | Dynamics Chronicles
    Featured Articles | Dynamics Chronicles

    Welcome to our blog, the content is entirely dedicated to Microsoft Dynamics 365, CRM, Power Platform, Common Data Service (CDS) but also Azure. Follow us !

    Share post:

    Related posts

    Microsoft 365 & Power Platform Call (Microsoft Speakers) – June 11th, 2024 – Screenshot Summary

    Call Highlights   SharePoint Quicklinks: Primary PnP Website: https://aka.ms/m365pnp Documentation & Guidance SharePoint Dev Videos Issues...

    4 days ago

    Power Platform | Copilot environment strategy

    A recent blog post on the Power Apps blog highlighted a new whitepaper and a Microsoft Learn resource dedicated to developing a tenant environ...

    4 days ago

    Elevating Enterprise Architecture with Microsoft's Power Platform Agility

    This video discusses how Microsoft's Power Platform Agility can help organizations elevate their enterprise architecture. The Power Platform A...

    4 days ago

    European Power Platform Conference, Brussels, Belgium, June 11-13 2024

    The 3rd European Power Platform Conference (EPPC) is set to be held from June 11-13 in Brussels, Belgium. This is a significant event that bri...

    5 days ago

    Using Power Platform CLI with Podman

    If you're looking to use Dev Containers but can't use Docker on your work machine due to licensing terms, this post may have the solution you ...

    5 days ago
    Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
    * Yes, I agree to the privacy policy