One-to-Many Relationships for Canvas apps

Over the summer we made it easy to work with Many-to-One relationships.  With a single dot you could walk across a relationship from one entity to another.  First(Orders).Customer.Name is all that is needed to retrieve the Name column from the related Customer entity that is associated with the first Order.

I’m very pleased to announce that we have now added One-to-Many relationship support.  First(Customers).Orders returns the table of Orders associated with the first Customer, effectively walking backward across the Many-to-One relationship.

This makes app development over relational data much easier.  Not only is the formula syntax easier to read and write but the related entities don’t even need to be added to the app as Data Sources.

CDS + Canvas = Awesome

That is our mission.  One-to-Many is the first of many features coming in the months ahead as we enhance our support for the Common Data Service for Apps in Canvas apps.  It is our goal to expose all of the rich functionality of CDS to Canvas authors, making CDS + Canvas a great choice for building your next relational business app.  Features on the horizon include:

• Better Optionset and Two option support
• Many-to-Many relationships
• Better Polymorphic lookups
• Better data and time handling

There will be a small amount of pain involved: supporting CDS well may require some updates to the formulas in your existing CDS apps.  For this reason, this functionality is under an Experimental switch requiring an explicit opt-in.  There is no impact to existing apps.  We will slowly roll this out to everyone and will provide much more information as we go about what needs to change.  For now, be aware that optionsets as you know them today will not work properly with this experimental switch turned on.

As always, your feedback is invaluable to us.  Please jump on these changes and let us know what you think of this new functionality.  There is still time to adjust what we are delivering. 

Finally, remember that these features are experimental.  They can change at any time.  Please do not use them in production, we’ll let you know what it is appropriate, the main signal being the upgrade from Experimental to Preview status.  Please report any problems you encounter and your overall experience in the community forum.

Entity hopping with dots: Many-to-One

Let’s dive in and take a look.  If you check the box to install the sample data and apps when creating a new CDS database, among others it will install two related entities.  The Reviews entity has a Many-to-One relationship with Products through the ‘Associated Product’ lookup field:

First, we’ll add the Reviews entity as a data source and bind it to a Gallery control:

Now, let’s expand each of these items and see more information about the Product that the Review is about:

Note the formulas.  ThisItem.’Associated Product’ is the lookup field from Reviews to Products (see the entity graph above).  We automatically expand this field into a record, making all the information about the Product available, such as the ‘Product Image’, Name, and ‘Product Description 1’.  Notice that Label3 is bound to ThisItem.’Associated Product’.Name in the Data pane on the right hand side. 

A simple dot walks from the Reviews over to the Products entity.  No Filter or LookUp function calls are required.  We didn’t even need to add the Products data source.

Don’t be concerned that we bring in more data than we should, that we are expanding lookups on everything.  As you build your Canvas app it is being constantly analyzed to see what data you actually reference and we limit ourselves to only expand what we need.

Reversing the Relationship: One-to-Many

For every Many-to-One relationship, there is a corresponding One-to-Many when coming from the other direction.  Making the One-to-Many relationship easily accessible is the feature we are introducing this week.

To get started, go to the File menu, App settings, Advanced settings, scroll to end of the list, and turn this switch on:

Now, let’s create a new screen and add the Products data source, again in a Gallery control:

Now, let’s add a second gallery to view all the Reviews for each product.  We’ll set its Items property to Gallery2.Selected.Reviews:

And that’s it.  As the end user changes the selection of Product, the list of Reviews automatically updates to display the filtered list for that Product.  No Filter function is involved.  We didn’t need to add the Reviews data source.  Binding within the second gallery is based on the schema for Reviews.

In summary, we can write these formulas with explanations:

First( Reviews ).’Associated Product’.Name

1. Take the first Review.
2. Use the ‘Associated Product’ lookup field to walk the Many-to-One relationship over to Products.
3. Return the value of the Name column from the Product.

First( First( Products ).Reviews ).Comment: 

1. Take the first Product.
2. Using the ‘Associated Product’ lookup field on the Reviews entity, find all the Reviews that are associated with this Product.
3. Take the first of those filtered Reviews.
4. Return the value of the Comment column of the Review.

Northwind Traders

Let’s now turn to a more complicated example.  Microsoft Access ships with the well known Northwind Traders sample database.  Let’s bring that into CDS and see how it plays with relationships.  This relationship graph is a little more complicated:

By utilizing One-to-Many and Many-to-One relationships, we can create a basic Order entry screen.  I’ve highlighted in the graph above the entities that we will be using on this screen:

Let’s take a closer look at each section of this screen.

Left hand gallery of Orders

• Gallery control with Items = Orders.  This is one of the data sources we needed to add.
• Customer name label has Text = ThisItem.Customer.Company. We are walking across a Many-to-One relationship to retrieve information from the Customer’s entity. 
• Order total label control Text = Sum( ThisItem.’Order Details’, Quantity * UnitPrice ) (wrapped in a Text function for currency format).  We ran an aggregate function over the One-to-Many relationship from the Orders entity to the Order Details entity.  Note that this is not yet delegable, one of the things we are working on.

Top right form for an Order’s details

• Form control with DataSource = Orders and Item =  Gallery1.Selected.
• Customer data card with Default = ThisItem.Customer.  This card is going to work with an entire record from the Customers entity.  It contains a Combo Box control with DefaultSelectedItems = Parent.Default and Items = Choices( Orders.Customer ) which returns the full list of Customers to choose from.  The card output the selected customer with Update = CustomerCombo.Selected.  We are working with the Many-to-One relationship between Orders and Customers.
• The same is true for the Employee selection and Shipper selection.  In the case of the Employee selection, we also added an Image control with Image = EmployeeCombo.Selected.entityimage.  All of these cards and Combo Box controls are operating on the full record, a fact we can use to access any of the columns of the current selection directly.  A bonus advantage is that as the end user changes the Employee selection, the picture will automatically update.
• The rest are standard cards on Orders columns.

Middle right list of Order Details

• Gallery with Items = Gallery1.Selected.’Order Details’.
• Product details, such as the image and name, are taken from the Products table.  The image control has Image = ThisItem.Picture.  If you are keeping score, we just walked across TWO relationships: Orders One-to-Many to ‘Order Details’ and then Many-to-One to Products.  You can walk across as many One-to-Many relationship as you like and can always end with a Many-to-One relationship.

Bottom right new item entry form for Order Details

• One of our current limitations is that we can’t write back through a relationship.  We can’t yet add something to the order directly.  Instead, we have a few controls here that gather up the details of something to add to the order and then use the Patch function directly against the ‘Order Details’ entity, with the Order lookup field appropriately pointing to this Order.  Operative word here is “yet”, we will be adding this support so you don’t need to do this long term.  Until now, we didn’t need to add any other data sources besides Orders.

A big step for relational data

We just built a screen that:

• Drew information from 6 different related entities.
• Walked through multiple Many-to-One and One-to-Many relationships.
• In some casee, we walked through relationships in series such as the Orders One-to-Many to ‘Order Details’ and then Many-to-One to Products.
• No Filter or Lookup function calls were required.
• No foreign keys were referenced.  You’ll note that my relationship graphs don’t even show keys, that knowledge isn’t required by authors.
• We only had to add 2 data sources (Orders and ‘Order Details’).  Long term it will be only 1 (Orders).

We did discover some limitations along the way:

• Aggregate functions are not yet supported.
• Write back through the relationship is not yet supported.
• You can’t walk from a Many-to-One relationship to another Many-to-One relationship yet.

Again, key word is “yet.”  🙂