Working with Data Sources

Now that you’ve seen some of the basics of creating a Canvas application, I want to dive in a little bit deeper with working with data in CDS. The first place I want to start is actually connecting to CDS. You have a few different options that I want to go over. But first, let’s talk about how you get there. You go to View and then “data sources.” This is where you’re going to see all the connectors that you have configured for the application, not only the Common Data Service, but if you have SharePoint, SQL, anything else, even custom connectors, they’ll show up here if they’re associated with the application.

This is where you can go to add and remove them, as well as refresh them if necessary. The next decision you have to make is around which connector to use for connecting to Dynamics 365 or the Common Data Service.

This might seem obvious: if you were building a model-driven app, you’d use the Common Data Service. If you were doing something for Dynamics 365, where you had the app, you’d use Dynamics 365. But it’s really not that simple. You have to come back and think about this, what you’re trying to accomplish. For the most part, the connectors are virtually the same. The big difference is that the Dynamics 365 one allows you to specify the instance or environment that you’re connecting to each time that you use the connector. So it means you could pull some data from one environment, as well as some other data from the other environment, as long as the user had user access to both those environments.

Generally speaking, most applications are best using the Common Data Service. The reason for that is it connects to the default environment, which is the one that the application is deployed to. Not to be confused with the default environment that you have in the tenet. So that’s important. So when you move the application, and you create an application in test, and then you move that to the production environment, there’s no changes required to any of the configuration on the application if you use the Common Data Service connector. However, if you did the same thing with the Dynamics 365 connector, even when you moved it to production environment, it would still be pointing for data to the test environment.

So generally speaking, unless you need to go between different organisations, maybe a marketing and a sales that were in isolated and different environments, you generally want to use the Common Data Service connector. Now, once you’ve picked the connector, you’re going to be presented with a list of entities that are available in that organisation. You can use the search as a quick way to find them. You’re going to select them. I like to do this all up-front, selecting all the ones that I know I’m going to need and connect to them. So I don’t have to keep going back to this, add connection each time I need to add another one in there.

The other time you might come back to this list of connections is if you need to refresh any of them. Let’s say I added a custom field to the account entity. I’d come back to the View, “data sources.” On the dot-dot-dot action on it, there’s a refresh command that will pull in the new metadata, letting me see that field that I just added to that entity. The easiest way to configure and work with data is to use the galleries as well as the forms to basically interact with it. The galleries work with the concept of a template for each item that’s going to be shown.

In this case, I’ve got two labels that are visible, plus an icon that is the navigation to the detail item on there. We’ll talk about how to get the data populated, and how to actually get that search items to work, as we get a little bit further. Once a user wants to drill down, the easiest way to gives them some detail is using the display form. The display form is a collection of data cards. We’ll talk about configuring the data cards in a little bit. But this gives a nice structured way of looking at data, almost very similar to a model-driven form that you would use to display the data and edit it as well.

Now, speaking of editing, you can also use the Edit form to provide the new record experience, as well as the modification experience, for that same data. This is designed to basically let the user interact with it, do lookups, we’ll see that there’s data cards that you can have on there for using dropdowns and other custom controls, as well as the basic things like textbox, like you’re seeing here. When you’re working with forms, particularly the Edit form, there’s some key functions that you should be aware of. The first one is, let’s say you were updating or creating a new record, and you’re ready to complete that edit. You notice I’ve got the checkbox highlighted there selected.

And on OnSelect, I have a function that I’m calling SubmitForm. What that’s going to do, if it’s a new record, it will create the new record. If it’s an update, it will issue the update commands necessary for the fields that have been changed. Before I navigate to this EditForm, I might want to prepare the form for being able to be used by the user. If I was creating a new record, I would call the new form. That would create it as a default, all blank entries, ready for me to create the record. If I was going to edit the data, I want to kick it into edit mode, that would be EditForm.

And if I just want to reset it to the initial states, get rid of the edits that have been done, that would be ResetForm to allow that to happen. Now, I told you we’d come back to how we get the data for the gallery. So in this example, I’m showing the simplest thing that you can do. I can connect items to the accounts. The accounts is my data entity that I’ve connected through the CDS connector. This would essentially show all the accounts that were available, without any sorting or searching or filtering being done on it. It’s just a raw list of accounts.

If I wanted to allow searching, which we did on the prior example, I can combine that account entity with the search to basically go ahead and pull in the criteria which was in TextBox1 text, and I can tell it what fields I want to search. emailaddress1, address city, as well as the name. Basically saying, I want to search those three fields and find any matching records. If I don’t have any criterion in, then show all. But that’s basically chaining the data table for accounts into my search and I’m feeding that in. I could also provide a philtre on that, which would first philtre accounts before the searching was done.

Now, we’ll look at a more complex example here in a second. In this example, I’ve actually combined not only the search on the accounts, but I’ve also combined it with the sort by column, so it takes the results from the search and it basically takes that table that’s the result of that and sorts it by the columns. And I used an if in there. So you’ll see the if, which checks my selector of whether I want ascending or descending, and returns the appropriate descending or ascending on the sort by columns. Now, let’s talk a little bit more about filtering.

I can use the philtre function to apply a formula to each record, so it can use equal, less than, all the common operators. You can even combine the philtres together. Essentially, I can do a simple philtre. Like here, I’m saying if the statecode is equal to 0, so every record has a state code that’s either active or inactive. If it’s an active record, I want to include it. I can also combine that with other criteria. So in this example, I’ve said statecode equals 0, comma, account name equals Contoso. That would be an and condition between statecode and account name, and it would return if both those were true.

Now if I did something like this, add another comma in there and said, name equals Contoso, comma, name equals Fabrikam, I would not get any results because the name can’t be Fabrikam and Contoso at the same time. It’s a perfectly valid check, but it is not one that will return any data. So you do have to be aware of– So you’ll want to basically make sure, if you wanted to do that, that what you would do is you do a statecode equals zero, account name equal Contoso, use the or sign, the two bars, and then the name equal Fabrikam. So this would return whether the name was Contoso or Fabrikam, as long as it was an active record.

Let’s make that a little bit more complex and show that in our example with the search. So let’s say I wanted to philtre out and only make available for searching records that had Contoso and Fabrikam and were active. I would do that philtre before I handed the data off to the search. And I could even wrap that in the sort by, like we had before. So you’re starting to see how you can chain these things together to provide your overall criteria, sorting, and searching capabilities on the data that you show in a gallery. Now, normally, those criteria are sent off to the server, and it’s processed before the results come back to the client.

And that’s important because it’s more performant, and it also makes sure you get the full result set. I’m going to use a term called delegation, which describes handing that off to the server for it to process and giving you the results back. You want to avoid delegation issues. What do I mean by delegation issues? These are highlighted in the editor for you. So in this example, I’m doing a philtre on statecode equals 0, and on the created date that it’s less than today. You’ll notice it has a blue squiggly line under it, and there’s a little yellow exclamation point on the left hand side.

Now, the thing to be aware of is, when that happens, what it’s telling you is that it’s not able to send the criteria for that part off to the server. It means that you’ll get all active records back, and then on the client, it will philtre. The risk you run is because, by default, you only get 500 records back. You can up that to 2,000 in the settings. You’re not going to get the full results that might be appropriate in some scenarios. Generally speaking, you want to avoid as best as possible having this. Now, an easy way in this example would be to essentially not pass the function for date and time.

So in my OnVisible, what I would do for the screen is I would set a variable called MyToday, that I would collect the date in the format already. And then in my philtre, I would show that as created on less than MyToday. And you’ll notice that this no longer has the squiggly because it’s now able to delegate that to the server, and the work is done in the appropriate place. Watch out for delegation issues. They can lead to unpredictable results when you’re not expecting it. Now, using the forms are far the easiest way to update data and add new records back to CDS.

But sometimes you need to update a single record, you want to create a single record, you want to update multiple records. In fact, in this example, I want to update all the records that qualify for my criteria, the ones that I’ve selected, and I want to update the number of children that those contacts have. So I have an Update button and on the OnSelect in that Update button, I’m using the Patch function. The Patch function lets you update one or more records or create new records on a one-off example.

So in this case, it’s going to find all the records that match the selected contact ID in the gallery that I have, and it’s going to update the number of children on there. Patch is a little bit more advanced, but you should be aware that it exists, because there are some scenarios. In fact, in the practise, you’ll use Patch in a couple of places that we update some data. I also want to talk about related data, because related data is one of the powerful features that CDS offers, is connecting real-world relationships between the entities. In this case, I’ve got a BrowseGallery that has accounts. Now, accounts have contacts that are associated with them, so it could be multiple accounts.

So this is a one-to-N relationship. I don’t have to do anything complex. I could do a philtre to philtre these down. So I can say philtre accounts, comma, where the account ID is equal to the account that I’ve selected. But an easier way to do that, let’s say I wanted to show a gallery that showed all the contacts for an account. I could simply bind items to BrowseGallery1 Selected.Contacts. Now, what it will do is use the relationship information to go get all the contacts associated with that account, making it easy, just using the dot syntax, to navigate down through the relationships to show the data. I can also do that the other way.

So let’s say I had an N-to-one relationship. This case, I have a primary contact for the account, and on the account card, I want to show the name of the primary contact for that account. I don’t have to go do a lookup, because that is another way you could do it. You could do a lookup and get the full name column from the contact. In this case, what I’m doing is using the relationship. So I’m saying ThisItem, which is the account, dot Primary Contact, which is the contact that is the relationship that is the primary contact relationship, dot Full Name. And that’s going to do the work to retrieve the proper record and allow displaying that.

Now, these are read-only relationships right now. They are looking at being able to add right back through. So if you needed to update the contact, that’s not something you’d be able to do through the navigation paths like this. You would still have to do lookup on the record and do the appropriate form or patch to do that. But that is something they’ll be looking at adding in the future.