Developing with the Microsoft Common Data Model

I recently started using the common data model in a PowerApps solution of mine. The Common Data Model is part of the Common Data Service and can be used to store information like in a Database. It is designed to act as a central location to store information and to be used by PowerApps, Power BI, Microsoft Flow and all kinds of services through connectors and gateways.

commondatamodel

It contains predefined entities, the entities contain fields, all defined by Microsoft. The entities are from a general character so they are applicable for most companies.

In the following articles I describe some very nice functionalities that I used quite a lot during my developments.

Import data from an Excel template to a Common Data Model Entity
Export Common Data Model data to an Excel file
“Live” editing Common Data Model data with excel

 

 

Advertisements

Import data from an Excel template to a Common Data Model Entity

For a PowerApps solution of mine I wanted to use the Common Data Model (CMD) as the storage location. During development I had to import information in my entities quite often. In this article I describe how to get an Excel template how to import the information with this template.

So, when you want to import existing content in a custom or default entity, you can easily import this from an Excel template.

Go to your Common Data Service environment. In my case it is in my PowerApps Environment.

Open and login in https://web.powerapps.com/
Beneath the Common Data Service select Entities, find your entity and open it.
At the right top you see an action menu, select ‘Export template’

CDM_ExportTemplate

Select the fields you want to use for the import, you can be very specific in choosing your columns, select only the required fields or you can add all fields.

CDM_SelectFields

I choose the relevant fields only relevant for me, click ‘Export to Excel’, you can choose to export to Excel or Export as .csv. I choose Excel.

A template is generated, when finished you can download the template.

CDM_TemplateComplete

Save the file and open it with Excel in edit mode.
Now you can add your existing content in the file and save it, make sure that the information types match, otherwise the import will fail!

CDM_filledExcelTemplate

Now you are ready to import your data. Open your entity and select ‘Import data’ in the action menu.

CDM_ImportData

Select your file, the columns in the excel file will be matched and mapped to the columns in the entity. (if you get an error try it again, I sometimes get an error, the second time the matching succeeds).

To check the mappings click ‘Show Mapping’, then you will see how the columns are mapped. If necessary you can update the mapping.

CDM_MappingFields

When ready click ‘Import’.

CDM_ImportDataFile

If the import succeeds your content is added, if the import fails you get an link to a report, in here you can analyze what causes the import failure, fix it and try again.

If the import is succeeded you see.

CDM_ImportDataSucces

Now you are ready, when you go to your entity and select the data tab you will see your imported content.

CDM_ImportResult

 

Export Common Data Model data to an Excel file

For a PowerApps solution of mine I wanted to use the Common Data Model (CMD) as the storage location. During development I had to update some columns in my entity. Unfortunately you cannot change an existing column when it contains content, therefore you need to export the content to an Excel file, remove all the content, do your modifications/updates and import the data again. In this article I will describe the steps I take to Export the data to an Excel file and how to clear the content before updating your entity.

Exporting the content works quite simple, navigate to your entity. And select “Export data”.

CDM_ExportData

When the export is successful, you get the message “Export completed” with the link to a zip file containing the exported data.

CDMExportSucces

When you extract the file you will see it contains three files: a xlsx file with the name of the exported entity, a manifest.xml and a PackageHeader.xml

I am not sure what to do with the xml files, I think they can be used to create new entities from (New entities from data). In my situation I only needed the xslx file which contains the data.

Now you have exported the content can be deleted from the entity, I found two ways for deleting the data:

  1. Clear all data: watch out, this will clear the data from all entities!
  2. Using the “Open in Excel” for “live” editing the data from Excel (remove all rows and publish)

In this case I use option 1, option 2 is this article.

Go to the home screen of the entities, in the right side of the window select the dots. Select the “Clear all data” from the menu.

CDM_clearalldata

You will be asked to confirm you action, select Delete data.

CMD_ClearAllData

When finished all data is removed from all entities.

Now you can update your entity, when ready, import the data as described here, select the exported xslx file as import source (depending on the updates you make on the entity you maybe should update the xslx file data to keep the same data types ).

Now you are ready, you’ve got all your content and your entity is updated.

 

 

 

“Live” editing Common Data Model data with Excel

For a PowerApps solution of mine I wanted to use the Common Data Model (CMD) as the storage location. During development I had to edit the content stored in a entity. I found out that the CDM has a great option to edit the content with Excel. This article describes what steps to take if you want to update content in you entity with Excel.

When you open an entity from the Common Data Model (CMD), you can select the data tab to see the content currently available in the entity. To work with the data in the entity Microsoft has created a great feature to directly edit/add/delete content in the entity. This can be done by the feature “Open in Excel”.

CDM_OpeninExcel

When you click this button the browser offers you a xslx file.
Click ‘Open’ to open the file in excel.

CMD_DownloadXslxFile

You probably will get a security pop-up, click ‘Allow’

CDM_SecurityPopUp

Excel is opened in protected view, click ‘Enable Editing’

CDM_ExcelProtectedView

On the left site, the content is loaded and on the right side a Panel is shown, it is possible that you have to sign in, do this with your O365 credentials.

CDM_Excel

When you are logged in, you can easily create new content , edit or delete existing content.

When you want to apply the updates, press the Publish link at the right bottom. You will get a summary of changes, to apply them, select Yes.

CDM_ConfirmUpdates

You will get a successful message and the workbook will be updated.
Now the content in the entity is updated.

 

Saved by versioning in PowerApps

I am so glad PowerApps keeps a version history.

The version history is great during development, each time you save your app a version is stored. Only when you Publish the app that version becomes Live and can be used by your users.

The version history saved me a couple of times,  when I unintentionally deleted or broke something.

To restore a previous version:

  1. Login to your PowerApps environment: https://web.powerapps.com
  2. Open the Apps section
  3. Select the dots (ellipsis?)
  4. Click settings

Versioning1

  1. Select Versions
  2. Click “Restore” from the version you would like to restore

Versioning2

  1. You will be asked to confirm your action
  2. The version will be restored as a new version.

Versioning3

Using Global Variables in PowerApps

I recently started creating an app for a proof of concept. During development of the app I had to store some information like user Id, a true or false value. At first I added this information in text fields in hidden screens, this worked but did not really felt like the correct method. When variables became available storing information like this became much easier and using variables feels like the way to go.

I use the Set method to create a Global variable and directly add the required value, in this case a user Id.

This action is done add the On start of the app.

To create and set the variable at startup take the following steps:

  1. Click the default screen
  2. Select the On start button in the Ribbon (or in the drop-down next to the formula field)
  3. Add the set variable formula like “Set(variableName, value)”

Now when the app is started the variable is initialized and filled your value, you can use the variable anywhere in you app.

GlobalVariable1

To update the variable with another value, you can use the Set method again. This can be done on any action/trigger mostly the OnSelect action.

So the formula will be like “Set(variableName, NewValue)”

GlobalVariable2

Hopefully this article helps someone setting up a PowerApp and using variables.