How to build Model-driven App in Power Apps - Part II

• Introduction to Microsoft Dataverse
• Table Characteristics
• Dual-write vs. virtual tables
• Create a Microsoft Dataverse table
• Create a table and import data into your Microsoft Dataverse database
• Create a custom table and import data
• Summary

Introduction to Microsoft Dataverse

Microsoft Dataverse lets you securely store and manage data used by business applications.

Tables

Data within Dataverse is stored within a set of records called tables. A?table?is a set of records used to store data, similar to how a table stores data within a database.

Dataverse includes a base set of standard tables supporting common business scenarios connected to Dynamics 365 application data. You can also create custom tables specific to your organization and populate them with data that you import from lists in SharePoint, from Excel, or from PowerQuery. App makers can then use Power Apps to build rich applications using this data.

Dynamics 365 applications, such as Dynamics 365 Sales, Service, and Marketing, use Dataverse to store and secure data used by the applications. This means you can build apps using Power Apps and Dataverse directly against your core business data already used within Dynamics 365 without the need for manual integration.

Note - Dynamics 365 Finance, Dynamic 365 Supply Chain Management, and Dynamics 365 Retail currently require the configuration of Dual-Write to make your business data available within Dataverse.

For most organizations, it's a good idea to use the standard tables and columns as they were intended. But to meet your business needs, you can extend the functionality of standard tables by creating one or more custom tables to store information that's unique to your organization.

Logic and validation

Entities within Dataverse can leverage rich server-side logic and validation to ensure data quality. You can also reduce repetitive code in each app that creates and uses data within a table.

• Business rules: Business rules validate data across multiple columns in a table, and provide warning and error messages, regardless of the app that's used to create the data.
• Business process flows: Business process flows to guide users to ensure they enter data consistently and follow the same steps every time. Business process flows are currently supported only for model-driven apps.
• Workflows: Workflows automate business processes without requiring user interaction.
• Business logic with code: Business logic supports advanced developer scenarios that extend the application directly through code.

Security

Data in Dataverse is securely stored so that users can see it only if you grant them access. Role-based security, based on the Dynamics 365 system allows you to control access to tables for different users within your organization.

Table characteristics

Microsoft Dataverse is designed to let you quickly and easily create a data model for your application, based on the tables and the table metadata that you include in your app.

Tables describe the kinds of data that are stored in the Dataverse database. Each table corresponds to a database table and each column (also known as an attribute) within a table represents a column in that table.

In Dataverse, metadata (data about data), is a collection of tables. The table metadata is what controls the kinds of rows you can create and what kind of actions can be performed on them. When you use customization tools to create or edit tables, columns, and table relationships, you are editing this metadata.

The apps that your customers use to interact with the data in your environment depend on the table metadata, and they adapt as you customize the metadata.

When to use standard tables, and when to create new tables

Dataverse comes with many standard tables that support core business application capabilities. Each table also contains many metadata columns that represent common data that the system needs to store for that table. We recommend that you become familiar with the catalog of standard tables, and use them where possible because any applications written with standard tables will work as you expect in your environment without extra effort.

For minor changes, you might not have to create a custom table:

• To change the display name of a column, you can edit the table. You don't have to create a new table.
• You can't delete standard tables, but you can hide them. To hide a standard table, change the security role privileges for your organization to remove the Read privilege for that table. This will remove the table from most parts of the application.

If standard tables don't work for your business needs, and if they can't be edited to meet those needs, consider creating a new table, column, or table relationship. If a standard table almost meets your business needs, you can use it as the basis for a new table.

Table relationships

Table relationships define the different ways table rows can be associated with rows from other tables or the same table. Table relationships are metadata. They let queries retrieve related data efficiently. Use table relationships to define the formal relationships between tables.

When you look at the solution explorer you might think that there are three types of table relationships but actually, there are only two, see below:

• One-to-many relationships: In a one-to-many (1:N) table relationship, many related table rows are associated with a single primary table row in a parent/child relationship.
• Many-to-many relationships: In a many-to-many (N: N) table relationship, many table rows are associated with many other table rows. Rows that are related through N: N table relationships are considered peers.

The N:1 (many-to-one) relationship type exists in the user interface because the designer shows you a view grouped by tables. 1:N relationships actually exist between tables and refer to each table as either a Primary/Current table or Related table. The related table, sometimes called the child table, has a lookup column that allows storing a reference to a row from the primary table, sometimes called the parent table. An N:1 relationship is just a 1:N relationship viewed from the related table.

Besides defining how rows can be related to other rows, 1:N table relationships also provide data to address the following questions:

• When I delete a row, should any rows that are related to that row also be deleted?
• When I assign a row to a new owner, do I also have to assign all related rows to the new owner?
• How can I streamline the data entry process when I create a new related row in the context of an existing row?
• How should people who view a row be able to view the related rows?

Table types

Before creating or editing tables in Dataverse, you should understand the different types of tables that you can create. After a custom table is created, the table type can't be changed.

Types of table owners

When you create a custom table, the options for ownership are?User or team-owned, or?Organization-owned. After a table is created, you can’t change the ownership.

• User or team owned: Actions that can be performed on these rows can be controlled at the user level.
• Organization-owned: Access to the data is controlled at the organization level.

Activity tables

An?activity?is an action that a calendar entry can be made for. Activities have these characteristics:

• They have time dimensions (start time, stop time, due date, and duration) that help define when the action occurred or will occur.
• They have data (like a subject and description) that help define the action that the activity represents.
• They can be opened, canceled, or completed. Several sub status values will be associated with the?Completed?status of activity to clarify how the activity was completed.

Activity tables can be owned only by a user or team. They can't be owned by an organization.

The following default activity tables are available:

• Appointment: A commitment representing a time interval that has start/end times and duration.
• Email: An activity that's delivered by using email protocols.
• Fax: An activity that tracks the call outcome and number of pages for fax. The activity can optionally store an electronic copy of the document.
• Letter: An activity that tracks the delivery of a letter. The activity can store an electronic copy of the letter.
• Phone Call: An activity that tracks a telephone call.
• Recurring Appointment: The master appointment of a recurring appointment series.
• Task: A generic activity representing work that must be done.

Custom activity tables

You can create new custom activity tables. The metadata values of activity tables differ from the metadata values of other tables. For example, the?primary?column is set to?Subject.

Business Rules

Business rules provide a simple interface to implement and maintain fast-changing and commonly used rules. The business rules defined for a table apply to both canvas apps and model-driven apps if the table is used in the app.

By combining conditions and actions, you can do any of the following with business rules:

• Set column values
• Clear column values
• Set column requirement levels
• Show or hide columns
• Enable or disable columns
• Validate data and show error messages
• Create business recommendations based on business intelligence.

Differences between canvas and model-driven apps

Model-driven apps can use all actions available on business rules, however not all business rule actions are available for canvas apps at this time. The following actions are not available on Canvas apps:

• Show or hide columns
• Enable or disable columns
• Create business recommendations based on business intelligence.

Dual-write vs. virtual tables

Microsoft Dataverse allows for many different connections to external data sources. Dual-write and virtual tables (or virtual entities) allow Dataverse to access this data and write back to the original data source.

Dual-write

Dual-write is an out-of-box infrastructure that provides near-real-time interaction between Dataverse and Finance and Operations apps. When data about customers, products, people, and operations flow beyond application boundaries, all departments in an organization are empowered.

Dual-write provides tightly coupled, bidirectional integration between Finance and Operations apps and Dataverse. Any data change in Finance and Operations apps causes writes to Dataverse, and any data change in Dataverse causes writes to Finance and Operations apps. This automated data flow provides an integrated user experience across the apps.

Find more information on?configuring Dual-write .

Virtual tables

Virtual tables (also known as virtual entities) enable the integration of data residing in external systems by seamlessly representing that data as tables in Microsoft Dataverse, without replication of data and often without custom coding.

Virtual tables replace previous client-side and server-side approaches to integrating external data, which required customized code and suffered from numerous limitations, including imperfect integration, data duplication, or extensive commitment of development resources. In addition, for administrators and system customizers, the use of virtual tables greatly simplifies administration and configuration.

Find more information on?configuring virtual tables .

When to use Dual-write vs. virtual tables?

Both Dual-write and virtual tables offer useful data integration functionality. It is important to understand when you need to use each tool.

Dual-write should be used when you are working with Dynamics 365 apps and need near real-time integration. Dual-write will duplicate the data in both directions (to and from Dataverse).

Virtual tables should be used when you are connecting to data sources outside of Dynamics 365. There may be built-in connectors for these data sources, or you may have to use a custom connector.

Create a Microsoft Dataverse table

In this unit, you'll create a table and then customize key components, like columns, relationships, views, and forms. You'll learn how to:

• Create a custom table.
• Add custom columns to your table.
• Add a table relationship.
• Customize a view.
• Customize a form.

The tutorial follows the Contoso company, which is a pet grooming business that grooms dogs and cats. Contoso needs an app for client and pet tracking that can be used by employees on various devices.

Create a custom table

Sign in to?Power Apps ?and follow these steps to create a new custom table.

• In the left navigation pane, expand?Dataverse/Data, select?Tables, and then select?+ New table.
• Under New table, enter the following:

- Display name: Pet

• In the Primary Column section, enter the following:

- Display name:?Pet Name

• At the bottom, select?Create.

You will notice in our example, the new table and primary column begin with?cree0_. More columns created for this table will also begin with?cree0, this is specific to our demo environment. When testing in your own environment this may look different.

Add and customize columns

• In the list of Tables, select the?Pet?table that you created in the previous section.
• On the?Columns?tab, on the table designer toolbar, select?Add column.
• In the?Column properties?pane, enter the following values:

- Display name:?Species

- Data type:?Choice

- Choice set:?+ New choice

- Searchable:?Yes

• Create the choice set:

- Replace?New choice?with?Dog.

- Select?Add new item.

- Replace?New choice?with?Cat.

- Select?Save.

• Make sure?Searchable?is selected, and then select?Done.
• On the table designer toolbar, select?Add column.
• In the?Column properties?pane, enter the following values, and then select?Done:

- Display name:?Breed

- Data type:?Text

- Searchable:?Yes

• On the table designer toolbar, select?Add column.
• In the?Column properties?pane, enter the following values, and then select?Done:

- Display name:?Appointment date

- Data type:?Date and Time

- Searchable:?Yes

1. Select?Save table.

Add a relationship

• On the?Relationships?tab, on the table designer toolbar, select?Add relationship, and then select?Many-to-one.
• In the right pane, in the?Related?list, select?Account.
• Select?Done.
• Select?Save table.

Notice that when you add a many-to-one relationship, an?Account?column of the?Lookup?data type is automatically added to your list of columns on the?Columns?tab.

Customize a view

• On the?Views?tab, right-click?Active Pets?view and select?Open Link in New Tab. If you don't see the?Active Pets?view, select?Remove filter.
• In the view designer, select?+ View Column, select the following columns, and then select?OK:

- Account

- Appointment date

- Breed

- Species

• Select the?Created On?column, select?Remove.
• To arrange the columns, select the column to move, and then select?Move Left?or?Move Right?until your view looks like this. You could also drag and drop the columns to arrange the order as well.

• On the view designer toolbar, select?Save.
• Select?Publish.

Customize the main form

• In the left navigation pane, expand?Data, select?Tables, and then select?Pet.
• On the?Forms?tab, select?Information?next to the?Main?form type to open the form editor.

• In the form editor, drag the?Species,?Breed,?Appointment date, and?Account?columns from the?Column Explorer?pane to the?General?section of the form canvas, so that the form looks like this.

• Select?Save.
• Select?Publish.
• Select the back arrow in your browser to close the form designer.

Create a table and import data into your Microsoft Dataverse database

You can import data into your Microsoft Dataverse database in bulk from Microsoft Excel or CSV files.

Every table has required columns that must exist in your input file. We recommend that you create a template. A template will save you time and effort. First, export data from the table. You'll use the same file (updated with your data) to import data into the table.

Create a file template

You can do a one-time data export from a standard table or a custom table, and you can export data from more than one table at a time. If you export data from more than one table, each table is exported into its own Microsoft CSV file. In this example, you'll see how to export the?Pet?table but remember you could select several tables to export if you would like.

• On?powerapps.com , in the left navigation pane, expand?Data, select?tables, select?Data?at the top, and then select?Export data.
• Select the?Pet?table, and then select?Export data.
• After the export is finished, select?Download exported data, and save the file.

Copy data into your template

When you add data to a template file, you must make sure the data is unique. You can use either?primary keys?or?alternate keys.

• Open the CSV file that you created in the previous section.
• Add at least one new row of data but you only need to add information to the following columns below.

- Appointment date

- Breed

- Pet Name

- Species

• Save the file.

Import the file

• In the left navigation pane, expand?Data, and then select?tables.
• Select the?Pet?table, select the drop-down arrow next to getting Data, and then select?Get data from Excel.
• Click?Upload?and select the file that you updated and saved from the previous section.
• After the file is uploaded, click?Map columns.
• Set the following Pet columns Source values to?None.

- Import Sequence Number

- Owning Business Unit

- Pet

- Status Reason Value

- Status Value

- Time Zone Rule Version Number

- UTC Conversion Time Zone Code

- Version Number

• In the upper right, click?Save changes.

• You will notice under Mapping status, it states "Mapping warnings exist". The reason for this mapping status is that we set some of the columns to None or Not. This is fine because we didn't want to include those columns so this warning can be ignored.
• In the upper right, click?Import.
• After the data has been successfully imported, you'll see the total number of inserts and updates. Now let's go take a quick look at the imported data
• On the left, select?tables.
• Select the?Pet?table.
• On the table designer toolbar, select?Data.

Notice that the Account column is empty, this is because when you updated the Excel export file and update columns you were not instructed to update this column. The reason you were not instructed to update the Account column is that you cannot set a lookup value when importing data from Excel, this must be done from Power Apps.

Create a custom table and import data

Scenario

The current sales process for your company is manual and updates are only provided each Friday. To simplify this process, minimize the opportunity for mistakes, and improve visibility, you have decided you want to create a new app to track sales leads and automatically calculate the forecasted revenue. You want to use Common Data Service to store the list of potential customers.

Creating a Custom table

• Go to the?Power Apps home page ?and sign in to Power Apps.
• On the menu, expand?Data?and Select?tables.
• Select?New table.
• Enter the following information:

- Display name:?Prospects

• In the Primary Name column section, enter the following information:

- Display name:?Prospect Name

• Select?Create.
• Select?Add column.
• Enter the following information:

- Display name:?Stage

- Data Type:?Choice

• For?choice, Select the dropdown and select?New choice. Enter the following information and select?Save.

- Display name:?Prospect Stage

- Name:?prospectstage

- Add the following items/options:?Lead,?Opportunity,?Won,?Lost

• Once the choice has been saved, continue entering the following information for the new column:

- Default value:?Lead

- Change?Required?dropdown to?Required

• Select?Done.
• Select?Add column.
• Enter the following information and then select?Done.

- Display name:?Contract Amount

- Data Type:?Currency

• Select?Add column.
• Enter the following information and then select?Done.

- Display name:?Probability

- Data Type:?Whole Number

• Select?Save table, in the bottom-right corner.
• Select?Add column.
• Enter the following information and then select?Done.

- Display name:?Forecasted Revenue

- Data Type:?Currency

- Select?+Add?for Calculated or Rollup

- Select?+Calculation

• On the popup, select?Save.
• A new browser window will open. Select?Add action?near the bottom.
• Enter the following formula, but do not copy and paste, type it in as your column names will not be exactly the same as the example below since the?crXXX_?will be specific numbers and letters that define your environment. The formula entry will auto-suggest your options as you start to type the column names.

crXXX_contractamount * (crXXX_probability / 100)        

• Select the checkmark to save your changes. You may need to scroll right to see it.
• Select?SAVE AND CLOSE.
• Select?Done.

Add a Business Rule

• On the table designer toolbar, Select?Business rules.
• Select?Add business rule, a new browser tab will open.
• Select on the?Condition New Condition?from the design pane.
• In the right-hand pane, for?the field?choose?Contract Amount.
• For the Operator, choose?contains data.
• Select?Apply.
• In the right-hand pane, Select?Components.
• Select and hold?Set Business Required?and drag to the plus symbol to the right of the purple checkbox in the design pane.
• In the right-hand pane, for?field?choose?Probability.
• For the Status, choose?Business Required.
• Select?Apply.
• Select the dropdown next to Prospects New business rule and set the Business rule name to Make Probability Required in the top left of the screen.
• Select?Save?in the top-right corner of the screen.
• Select?Activate?to activate the rule.
• Select?Activate?to confirm activation.
• Close the browser tab.
• Now back on the table management screen, select?Done.

Importing Data from an Excel file

You will use the Excel spreadsheet named?Prospects ?for this exercise. Open the link, and select the Download button and save it locally.

• Open the file. Notice the “Stage” column is empty, you will need to look these up and enter them manually.
• Go to the?Power Apps home page ?and sign in to Power Apps.
• Go back to your Excel file and in the Stage column enter the values as below:

- Contoso Flooring: Won.

- Fabrikam Inc: Won.

- Adventure Works: Lead.

- Adatum: Lead.

- VanArsdel: Lost.

- Relecloud: Opportunity.

• Save and close the Excel file.
• Continue with the?Prospects?table.
• Select the drop-down arrow to the right of?getting data?and select?Get data from Excel.

Note - If you receive an error when selecting the?Get data from Excel?option, then your Power Apps license does not allow for importing Excel data.

• Select?Upload, locate the Prospects Excel file, and select?Open.
• Select?Map columns. Map the following Prospect columns to the associated Source values:

- Contract Amount:?ContractAmount

- Prospect Name:?Name

- Stage Value:?Stage

- Probability:?Probability

• Select?Save Changes?at the top.
• Select?Import.
• Select?tables.
• Select the?Prospects?table and Select?Data.
• Ensure that the data has been successfully imported.

Summary

Dataverse lets you securely store and manage data that are used by business applications. Standard and custom tables within Dataverse provide a secure and cloud-based storage option for your data.

Let's review what you've learned:

• Dataverse stores data in tables.
• If you can, it's preferable to use as many default tables as possible to make your system easy to maintain.
• Creating new tables is easy.
• The best way to get data into a table is to export a template, add data, and upload it.

This article is part of Microsoft Certified: Power Platform App Maker Associate and will be one of many in the series of articles on my journey to become a Microsoft Power Platform Architect:

1. PL – 900 – Power Platform Fundamentals
2. PL – 100 - Microsoft Power Platform App Maker
3. PL – 200 - Microsoft Power Platform Functional Consultant
4. PL – 400 - Microsoft Power Platform Developer
5. PL – 600 - Microsoft Power Platform Solution Architect

Until next time,

Nikola Basta