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.   To 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:

As the name implies these features are Experimental.  They are not done, you will experience bugs and gaps in functionality, and you should not deploy production solutions based on this work.  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 and it is on by default for new apps.

As always, we would love to hear your feedback on the community forum or at the end of this blog post.  This is the final stage before we turn this feature on by default, now is the time!

Option Sets

Until now Option Sets have been very difficult to use.  For a single category field there were two fields exposed _category_label and _category_value.  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.

No longer.  Now a category is just a category.  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.

Let’s take a look.  My goal with these screenshots is to show how simple it is.  You should be completely underwhelmed by the complexity.

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

This gallery is showing three different Option Sets.  First we have your basic single select Option Set showing the Category field:

This field appear in the portal’s entity viewer, with the options available for this Option Set expanded:

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

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

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

Our Switch function is missing an entry for Monitors – let’s add that.  As we start to type Category 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):

After we pick our enumeration and add a dot, we see the values for this enumeration, which are the options in the Option Set:

We select Monitors and complete our formula:

Multi Select Option Sets

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

Size 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:

Since this field is multi valued, when in a Canvas app the Size field is a single column table of Option Set values.  Each record contains a single value in the Value column.  We can use the Concat function to aggregate the values in the table together with comma separators into a single text string to show in a label control:

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

Two Option

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

Here again we are using the coercion to a text string for end user display:

For the conditional coloring, we use the Color property of the Label control.  At present we need to compare this to the enumeration that for Discontinued.   But 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.  Both forms will be valid:

If we click into the formula, we’ll see the enumeration more clearly, just as we did for Category:

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):

Writing Changes

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

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

Each of the Option Sets appear in different Data Cards, each with a Combo Box control within.   The key properties here are the DefaultSelectedItems which determines which item is currently selected:

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

The other two combo boxes are very similar, the only difference being the DefaultSelectedItems 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):

You can of course also use the Patch function directly:

At the time of this writing, there was a bug preventing the saving of a Multi Select Option Set either with the Patch or SubmitForm functions.  We are working on a remedy and it will eventually work as described here.

Direct String Comparisons

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

Let’s look at a quick example.  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:

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

As written, had we not blocked it, that formula might have worked in Spanish but not in English.  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.  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:

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

Displaying Option Set labels is fine, that’s why they are there.  Just don’t compare them.

Many-to-Many Relationships

Ok, let’s shift gears to relationships.  Over the summer we added Many-to-One relationships and in early November we added One-to-Many relationships.  It’s time to complete our relationship support with Many-to-Many.

Many-to-Many relationships are as the name implies, Many Students can have a relationship with Many Teachers:

This differs from an Orders to Customers relationship, where an Order can only be for one Customer (a Many-to-One relationship).  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).

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

Fortunately we can simplify things considerably if we pick a side.  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:

In CDS we can create Students and Teachers entities and relate them through a Many-to-Many relationship:

In a Canvas app, let’s create a screen with two galleries, one for Students:

And one for the Teachers related to the selected Student, using the same One-to-Many syntax we use elsewhere:

Now as we move through students, we’ll see the Teachers list update automatically:

Now let’s reverse the direction and look at this relationship from the Teacher’s perspective:

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:

And now we can move through the Teachers and see the related Students automatically displayed:

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

Unfortunately, all we have at the moment is the ability to read Many-to-Many relationships.  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.  We’ll keep you posted on our progress and will announce when it is ready here in the blog.

As a preview, the design calls for two new functions Relate and Unrelate which you use to relate a Teacher to a Student:

        Relate( SelectedTeacher.Students, SelectedStudent )

Unrelate reverses the process.  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.

In the meantime, you can create a Model-driven app to relate and unrelate records.  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”:

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

Road Ahead

Besides delivering Option Sets and Many-to-Many relationships, this release marks a turning point for us.

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

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

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

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

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

Feedback Please!

Now is the time to give us your feedback while we are still experimental.  When we move to Preview it becomes significantly harder to make changes.   I realize that many take time off at the end of the year.   But if you can fit in a few minutes to try these concepts out and drop us a note we would very much appreciate it.