{"id":853,"date":"2018-12-17T10:24:54","date_gmt":"2018-12-17T18:24:54","guid":{"rendered":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/option-sets-and-many-to-many-relationships-for-canvas-apps\/"},"modified":"2025-06-11T08:05:48","modified_gmt":"2025-06-11T15:05:48","slug":"option-sets-and-many-to-many-relationships-for-canvas-apps","status":"publish","type":"post","link":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/option-sets-and-many-to-many-relationships-for-canvas-apps\/","title":{"rendered":"Option Sets and Many-to-Many Relationships for Canvas apps"},"content":{"rendered":"

We continue to expand our Canvas support for the Common Data Service for Apps, on the road to CDS + Canvas = Awesome.\u00a0 \u00a0With this installment we have refined how Option Sets and Two Option data types work and have added support for Many-to-Many relationships.<\/p>\n

These features are available now with the CDS Experimental Connector.\u00a0 \u00a0To enable it, go to the File menu, App settings, Advanced Settings, and scroll to the bottom of the list where you will find this switch:<\/p>\n

\"\"<\/p>\n

As the name implies these features are\u00a0Experimental<\/a>.\u00a0 They are not done, you will experience bugs and gaps in functionality, and you should not deploy production solutions based on this work.\u00a0 We will let you know when it is safe to depend on these features, the primary indication being when we move this switch to Preview<\/a> and it is on by default for new apps.<\/p>\n

As always, we would love to hear your feedback on the community forum<\/a> or at the end of this blog post.\u00a0 This is the final stage before we turn this feature on by default, now is the time!<\/strong><\/p>\n

Option Sets<\/h1>\n

Until now Option Sets have been very difficult to use.\u00a0 For a single category<\/strong> field there were two fields exposed\u00a0_category_label<\/strong> and _category_value<\/strong>.\u00a0 To compare values you had to either take a hard dependency on the label, which is not a good idea as they can be localized which I’ll discuss later, or you had to look up the numerical value in the portal.<\/p>\n

No longer.\u00a0 Now a\u00a0category<\/strong> is just a category<\/strong>.\u00a0 An enumeration is brought in for each Option Set making it easy to compare values in a safe manner independent of localization and without you needing to know or care about the numerical value.<\/p>\n

Let’s take a look.\u00a0 My goal with these screenshots is to show how simple it is.\u00a0 You should be completely underwhelmed by the complexity.<\/p>\n

Here’s a screen showing the Products entity in a Gallery<\/strong> control<\/a>, part of the sample data that is included when you check the box to “add sample apps and data” when creating an environment:<\/p>\n

\"\"<\/p>\n

This gallery is showing three different Option Sets.\u00a0 First we have your basic single select Option Set showing the Category<\/strong> field:<\/p>\n

\"\"<\/p>\n

This field appear in the portal’s entity viewer, with the options available for this Option Set expanded:<\/p>\n

\"\"<\/p>\n

An Option Set value will coerce to a text string automatically making them easy to display to the user.\u00a0 The icon next to the label is an image control that will show a different icon for each of the possible categories:<\/p>\n

\"\"<\/p>\n

The Switch<\/strong> function<\/a> compares the Category against Laptops, Keyboards, Mice, and Monitors and uses the appropriate icon for each.\u00a0 Laptops<\/strong> in the formula is an enumeration value, similar to Red<\/strong> or Green<\/strong> if you have ever used the Color<\/strong> enumeration<\/a> in a Canvas app.\u00a0 If you click into the formula it becomes a little clearer about where these values are coming from:<\/p>\n

\"\"
\n‘Category (Products)’<\/strong> is the name of the enumeration that contains all the possible options for the Category<\/strong> Option Set.\u00a0 This is a local Option Set defined for this field of this entity only.\u00a0 To disambiguate it from other uses of the word Category we add the name of the entity in parenthesis after the field name.<\/p>\n

Our Switch function is missing an entry for Monitors <\/strong>– let’s add that.\u00a0 As we start to type Category<\/strong> we see the enumeration name among other names that start with “Category” (the red wavy lines are because we haven’t finished typing the formula):<\/p>\n

\"\"<\/p>\n

After we pick our enumeration and add a dot, we see the values for this enumeration, which are the options in the Option Set:<\/p>\n

\"\"<\/p>\n

We select Monitors<\/strong> and complete our formula:<\/p>\n

\"\"<\/p>\n

Multi Select Option Sets<\/h2>\n

Moving on, let’s look at the next field Size<\/strong>.\u00a0\u00a0Yes, computer hardware is typically not sold by size, please go with it this is only for illustration.\u00a0 This is an example of a Multi Select Option Set.\u00a0 In the portal’s entity viewer, with the Option Set values expanded:<\/p>\n

\"\"
\nSize<\/strong> is an example of a global Option Set this can be shared across entities and managed through the Option Sets entry point under Data in the portal:<\/p>\n

\"\"<\/p>\n

Since this field is multi valued, when in a Canvas app the\u00a0Size<\/strong> field is a single column table of Option Set values.\u00a0 Each record contains a single value in the\u00a0Value\u00a0<\/strong>column.\u00a0 We can use the Concat<\/strong> function<\/a>\u00a0to aggregate the values in the table together with comma separators into a single text string to show in a label control:<\/p>\n

\"\"<\/p>\n

The Text<\/strong> function<\/a> is needed since coercion to string is not yet working yet in the Concat<\/strong> function – we are still experimental, you will find functional gaps like this here and there.<\/p>\n

Two Option<\/h2>\n

Finally, let’s look at a Two Option field.\u00a0 Two Option fields are very similar to Option Sets but are backed by a Boolean value (true<\/em>, false<\/em>, or blank<\/em>) instead of a numerical field.\u00a0 In the portal’s entity viewer we have added a Discontinued<\/strong> field:<\/p>\n

\"\"<\/p>\n

Here again we are using the coercion to a text string for end user display:<\/p>\n

\"\"<\/p>\n

For the conditional coloring, we use the Color<\/strong> property of the Label<\/strong> control<\/a>.\u00a0 At present we need to compare this to the enumeration that for\u00a0Discontinued<\/strong>.\u00a0\u00a0\u00a0But since this is a Boolean value under the covers, in the future, you will be also able to just test it for true\/false directly without the comparison.\u00a0 Both forms will be valid:<\/p>\n

\"\"<\/p>\n

If we click into the formula, we’ll see the enumeration more clearly, just as we did for Category<\/strong>:<\/p>\n

\"\"<\/p>\n

The check mark and X icons are two different icon controls with conditional visibility (note which icon control is selected in the navigation pane on the left, only one of which is visible on the canvas at a time):<\/p>\n

\"\"
\n\"\"<\/p>\n

Writing Changes<\/h2>\n

Making a change to an Option Set’s value is done just as we do any other column.\u00a0 We can use an Edit Form<\/strong> control<\/a>, with the Item property wired to the Gallery control’s selection:<\/p>\n

\"\"<\/p>\n

At the time of this writing, the Form control will insert View data cards by default for Option Sets.\u00a0 You can change this to an Edit data card by selecting the field in the data pane and picking a different card:<\/p>\n

\"\"<\/p>\n

Each of the Option Sets appear in different Data Cards, each with a Combo Box<\/strong> control<\/a> within.\u00a0 \u00a0The key properties here are the DefaultSelectedItems<\/strong> which determines which item is currently selected:<\/p>\n

\"\"<\/p>\n

And the Items property, which determines what the available Categories are (in this case).\u00a0 We use the Choices<\/strong> function<\/a> just as we do for relational Many-to-One lookups, in this case with the Option Set enumeration name:<\/p>\n

\"\"<\/p>\n

The other two combo boxes are very similar, the only difference being the DefaultSelectedItems<\/strong> property on for Multi Select Option Sets is already a table, so it need not be wrapped in another table (no need for the extra “[” and “]” around it):<\/p>\n

\"\"<\/p>\n

You can of course also use the Patch<\/strong> function<\/a> directly:<\/p>\n

\"\"<\/p>\n

At the time of this writing, there was a bug preventing the saving of a Multi Select Option Set either with the Patch<\/strong>\u00a0or SubmitForm<\/strong> functions.\u00a0 We are working on a remedy and it will eventually work as described here.<\/p>\n

Direct String Comparisons<\/h2>\n

I mentioned earlier that, although it is tempting, it was a bad idea to compare an Option Set value to a string.\u00a0 In fact it is such a bad idea that we block you from doing it where we can.\u00a0 \u00a0The reason is simple: Option Set names and labels can be localized.<\/p>\n

Let’s look at a quick example.\u00a0 The following screenshots are of the same app, the left being what an English author would see and the right being what a Spanish author would see:<\/p>\n

\"\"<\/p>\n

Note that the column name Preferred Day<\/strong>\u00a0in the formula bar is localized.\u00a0 Note too that the Option Set’s labels are also localized: “Monday” to “Lunes”, “Sunday” to “Domingo”, etc.\u00a0 \u00a0It is this later localization that is the problem.\u00a0 In the first checkbox we compare against a string which is properly showing an error and not functioning:<\/p>\n

\"\"<\/p>\n

As written, had we not blocked it, that formula might have worked in Spanish but not in English.\u00a0 This is true for the end users too, even if all your authors were using Spanish, the first English end user would see a problem if we allowed this.\u00a0 The answer is to instead compare against the enumeration value, which is based on the underlying numerical value, as is done in the second checkbox:<\/p>\n

\"\"<\/p>\n

You will notice that this is properly showing the same check mark pattern in both languages.\u00a0 This formula is language independent.\u00a0 We do support coercion to a string so you can localize the checkmark’s label to be more consistent with the current language:<\/p>\n

\"\"<\/p>\n

Displaying Option Set labels is fine, that’s why they are there.\u00a0 Just don’t compare them.<\/p>\n

Many-to-Many Relationships<\/h1>\n

Ok, let’s shift gears to relationships.\u00a0 Over the summer we added Many-to-One relationships and in early November we added One-to-Many relationships<\/a>.\u00a0 It’s time to complete our relationship support with Many-to-Many.<\/p>\n

Many-to-Many relationships are as the name implies, Many Students can have a relationship with Many Teachers:<\/p>\n

\"\"<\/p>\n

This differs from an Orders to Customers relationship, where an Order can only be for one Customer (a Many-to-One relationship).\u00a0 It also differs from an Order to Order Lines relationship, where an Order Line can only belong to one Order (a One-to-Many relationship).<\/p>\n

Unlike One-to-Many or Many-to-One relationships, there is no lookup field on either side of the relationship.\u00a0 Instead, CDS maintains a separate hidden entity on behalf of the relationship, sometimes referred to as an associative entity or join table<\/a>.\u00a0 There is no direct access to this entity.<\/p>\n

Fortunately we can simplify things considerably if we pick a side.\u00a0 From a single Student’s perspective this looks like a One-to-Many relationship and we can access the information exactly as we do any other One-to-Many relationship:<\/p>\n

\"\"<\/p>\n

In CDS we can create Students and Teachers entities and relate them through a Many-to-Many relationship:<\/p>\n

\"\"<\/p>\n

In a Canvas app, let’s create a screen with two galleries, one for Students:<\/p>\n

\"\"<\/p>\n

And one for the Teachers related to the selected Student, using the same One-to-Many syntax we use elsewhere:<\/p>\n

\"\"<\/p>\n

Now as we move through students, we’ll see the Teachers list update automatically:<\/p>\n

\"\"<\/p>\n

Now let’s reverse the direction and look at this relationship from the Teacher’s perspective:<\/p>\n

\"\"<\/p>\n

Let’s duplicate our Student driven screen and have the first gallery show Teachers and the second gallery show the Students related to that Teacher:<\/p>\n

\"\"
\n\"\"<\/p>\n

And now we can move through the Teachers and see the related Students automatically displayed:<\/p>\n

\"\"<\/p>\n

Great, we can now navigate a Many-to-Many relationship from either direction.\u00a0 But what do we do if we want to add a Student to a Teacher’s list of Students?\u00a0 Since there are no exposed lookup fields we need something new.<\/p>\n

Unfortunately, all we have at the moment is the ability to read Many-to-Many relationships.\u00a0 We are actively working on the ability to relate two records through a Many-to-Many relationship which we expect to ship in January or early February.\u00a0 We’ll keep you posted on our progress and will announce when it is ready here in the blog.<\/p>\n

As a preview, the design calls for two new functions Relate<\/strong> and Unrelate<\/strong>\u00a0which you use to relate a Teacher to a Student:<\/p>\n

\u00a0 \u00a0 \u00a0 \u00a0 Relate( SelectedTeacher.Students, SelectedStudent )<\/strong>
\nUnrelate<\/strong> reverses the process.\u00a0 Note that since there can be more than one Many-to-Many relationship between two entities that we need to know which relationship is desired, hence we relate a new item to an existing One-to-Many projection of a Many-to-Many.<\/p>\n

In the meantime, you can create a Model-driven app to relate and unrelate records.\u00a0 I’ll leave the steps required to create this app as an exercise for the reader, but it is actually fairly simple: create an app with Students and Teachers entities as “subareas” in the Site Map and the Model-driven UI automatically exposes the Many-to-Many relationship under “Related”:<\/p>\n

\"\"<\/p>\n

The video adds Mr. Blake and Mrs. Jones as teachers for Glenn Smith.\u00a0 And if we go back to our Canvas app we will see the result in both directions:<\/p>\n

\"\"
\n\"\"<\/p>\n

Road Ahead<\/h1>\n

Besides delivering Option Sets and Many-to-Many relationships, this release marks a turning point for us.<\/p>\n

As you can see we have simplified Option Sets.\u00a0 We were not happy with the previous dual field approach both for those and for polymorphic lookups (Owner, Customer,\u00a0 and Regarding fields).\u00a0 From your many questions and frustrations you weren’t happy with them either.<\/p>\n

But this improvement comes at a price: we are making a breaking change.\u00a0 Formulas written to the old style will need to be updated.\u00a0 We never take breaking changes lightly and this one is no exception.\u00a0 This is why we are currently using an experimental switch that must be explicitly be turned on per app.<\/p>\n

The good news, and the turning point, is that we believe we have\u00a0made all the breaking changes we will need to make.\u00a0 You can move your apps to the experimental connector with confidence that you won’t need to update them again.\u00a0 \u00a0This also enables us to move to Preview in January and turn the new connector on by default for new apps.<\/p>\n

To ensure that we wouldn’t break anyone later we have temporarily disabled some features that are not yet ready.\u00a0 All of these are actively being worked on and we expect will land in late January or early February:<\/p>\n

    \n
  • Polymorphic Lookups.<\/strong>\u00a0 Owner, Customer, and Regarding fields are not available in a Canvas app.\u00a0 Like Option Sets, these were represented as two different fields that were very painful to use.\u00a0 Doing it properly is challenging for Canvas apps as the formula language is strongly typed with no generic object or variant data type.\u00a0 We do have a design in hand and will be adding these back in as a single field with the ability to determine the type and cast to a specific type.\u00a0 These were so hard to use that we believe most apps will not be impacted.<\/li>\n
  • Time Zone Independent Fields.\u00a0\u00a0<\/strong>There is a bug that prevents Date Time and Date Only fields that have the “Time zone independent” behavior from being saved properly.\u00a0 This should not impact most apps as this is not the default setting for standard entity fields or fields created in the portal.\u00a0 When this bug is fixed we’ll turn these fields back on.<\/li>\n<\/ul>\n

    Feedback Please!<\/h1>\n

    Now is the time to give us your feedback while we are still experimental.\u00a0 When we move to Preview it becomes significantly harder to make changes.\u00a0 \u00a0I realize that many take time off at the end of the year.\u00a0 \u00a0But if you can fit in a few minutes to try these concepts out and drop us a note we would very much appreciate it.<\/p>\n","protected":false},"excerpt":{"rendered":"

    We continue to expand our Canvas support for the Common Data Service for Apps, on the road to CDS + Canvas = Awesome. With this installment we have refined how Option Sets and Two Option data types work and have added support for Many-to-Many relationships. These features are available now with the CDS Experimental Connector.<\/p>\n","protected":false},"author":86,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ms_queue_id":[],"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","_alt_title":"","ms-ems-related-posts":[],"footnotes":""},"audience":[3378],"content-type":[3424],"job-role":[],"product":[3473],"property":[],"topic":[3421],"coauthors":[2104],"class_list":["post-853","post","type-post","status-publish","format-standard","hentry","audience-it-professional","content-type-news","product-power-apps","topic-application-modernization"],"yoast_head":"\nOption Sets and Many-to-Many Relationships for Canvas apps - Microsoft Power Platform Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Option Sets and Many-to-Many Relationships for Canvas apps - Microsoft Power Platform Blog\" \/>\n<meta property=\"og:description\" content=\"We continue to expand our Canvas support for the Common Data Service for Apps, on the road to CDS + Canvas = Awesome. With this installment we have refined how Option Sets and Two Option data types work and have added support for Many-to-Many relationships. These features are available now with the CDS Experimental Connector.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft Power Platform Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-17T18:24:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-11T15:05:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png\" \/>\n\t<meta property=\"og:image:width\" content=\"579\" \/>\n\t<meta property=\"og:image:height\" content=\"138\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Greg Lindhorst\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Greg Lindhorst\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\"},\"author\":[{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/greg-lindhorst\/\",\"@type\":\"Person\",\"@name\":\"Greg Lindhorst\"}],\"headline\":\"Option Sets and Many-to-Many Relationships for Canvas apps\",\"datePublished\":\"2018-12-17T18:24:54+00:00\",\"dateModified\":\"2025-06-11T15:05:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\"},\"wordCount\":2510,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png\",\"keywords\":[\"Dataverse\",\"Formulas\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\",\"name\":\"Option Sets and Many-to-Many Relationships for Canvas apps - Microsoft Power Platform Blog\",\"isPartOf\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png\",\"datePublished\":\"2018-12-17T18:24:54+00:00\",\"dateModified\":\"2025-06-11T15:05:48+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage\",\"url\":\"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png\",\"contentUrl\":\"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Option Sets and Many-to-Many Relationships for Canvas apps\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/\",\"name\":\"Microsoft Power Platform Blog\",\"description\":\"Innovate with Business Apps\",\"publisher\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization\",\"name\":\"Microsoft Power Platform Blog\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png\",\"contentUrl\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png\",\"width\":194,\"height\":145,\"caption\":\"Microsoft Power Platform Blog\"},\"image\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/person\/dbd4cb8af4503e696f240353831f05d4\",\"name\":\"Greg Lindhorst\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/d2e26bc357423265c4eeeb6a4ed51bd71fb29b8eced3e31f4bc1ccd5bedaf80f?s=96&d=mm&r=g0133d144d5ed416197bd3b29ccd9a59c\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d2e26bc357423265c4eeeb6a4ed51bd71fb29b8eced3e31f4bc1ccd5bedaf80f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d2e26bc357423265c4eeeb6a4ed51bd71fb29b8eced3e31f4bc1ccd5bedaf80f?s=96&d=mm&r=g\",\"caption\":\"Greg Lindhorst\"},\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/gregli\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Option Sets and Many-to-Many Relationships for Canvas apps - Microsoft Power Platform Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/","og_locale":"en_US","og_type":"article","og_title":"Option Sets and Many-to-Many Relationships for Canvas apps - Microsoft Power Platform Blog","og_description":"We continue to expand our Canvas support for the Common Data Service for Apps, on the road to CDS + Canvas = Awesome. With this installment we have refined how Option Sets and Two Option data types work and have added support for Many-to-Many relationships. These features are available now with the CDS Experimental Connector.","og_url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/option-sets-and-many-to-many-relationships-for-canvas-apps\/","og_site_name":"Microsoft Power Platform Blog","article_published_time":"2018-12-17T18:24:54+00:00","article_modified_time":"2025-06-11T15:05:48+00:00","og_image":[{"width":579,"height":138,"url":"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png","type":"image\/png"}],"author":"Greg Lindhorst","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Greg Lindhorst","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#article","isPartOf":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/"},"author":[{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/greg-lindhorst\/","@type":"Person","@name":"Greg Lindhorst"}],"headline":"Option Sets and Many-to-Many Relationships for Canvas apps","datePublished":"2018-12-17T18:24:54+00:00","dateModified":"2025-06-11T15:05:48+00:00","mainEntityOfPage":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/"},"wordCount":2510,"commentCount":0,"publisher":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization"},"image":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage"},"thumbnailUrl":"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png","keywords":["Dataverse","Formulas"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/","name":"Option Sets and Many-to-Many Relationships for Canvas apps - Microsoft Power Platform Blog","isPartOf":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage"},"image":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage"},"thumbnailUrl":"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png","datePublished":"2018-12-17T18:24:54+00:00","dateModified":"2025-06-11T15:05:48+00:00","breadcrumb":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#primaryimage","url":"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png","contentUrl":"https:\/\/powerapps-blog.azurewebsites.net\/web\/\/app\/uploads\/2018\/12\/2018-12-16_21h40_05.png"},{"@type":"BreadcrumbList","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/12\/17\/option-sets-and-many-to-many-relationships-for-canvas-apps\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/"},{"@type":"ListItem","position":2,"name":"Option Sets and Many-to-Many Relationships for Canvas apps"}]},{"@type":"WebSite","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/","name":"Microsoft Power Platform Blog","description":"Innovate with Business Apps","publisher":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization","name":"Microsoft Power Platform Blog","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png","contentUrl":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png","width":194,"height":145,"caption":"Microsoft Power Platform Blog"},"image":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/person\/dbd4cb8af4503e696f240353831f05d4","name":"Greg Lindhorst","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d2e26bc357423265c4eeeb6a4ed51bd71fb29b8eced3e31f4bc1ccd5bedaf80f?s=96&d=mm&r=g0133d144d5ed416197bd3b29ccd9a59c","url":"https:\/\/secure.gravatar.com\/avatar\/d2e26bc357423265c4eeeb6a4ed51bd71fb29b8eced3e31f4bc1ccd5bedaf80f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d2e26bc357423265c4eeeb6a4ed51bd71fb29b8eced3e31f4bc1ccd5bedaf80f?s=96&d=mm&r=g","caption":"Greg Lindhorst"},"url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/gregli\/"}]}},"bloginabox_animated_featured_image":null,"bloginabox_display_generated_audio":false,"distributor_meta":false,"distributor_terms":false,"distributor_media":false,"distributor_original_site_name":"Microsoft Power Platform Blog","distributor_original_site_url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog","push-errors":false,"_links":{"self":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts\/853","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/users\/86"}],"replies":[{"embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/comments?post=853"}],"version-history":[{"count":1,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts\/853\/revisions"}],"predecessor-version":[{"id":130981,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts\/853\/revisions\/130981"}],"wp:attachment":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/media?parent=853"}],"wp:term":[{"taxonomy":"audience","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/audience?post=853"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/content-type?post=853"},{"taxonomy":"job-role","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/job-role?post=853"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/product?post=853"},{"taxonomy":"property","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/property?post=853"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/topic?post=853"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/coauthors?post=853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}