Common Data Service for Apps -> Manage Entities, Fields, Relationships and Data Import – Part 1

CDS (Common Data Service) for Apps implements Common Data Model which provides set of standard entities (Account, Contact, Lead, Opportunity etc.) and capability to create your own custom entities. Applications built on top of CDS can leverage this common data model to securely store their data and enhance their business processes. To know more about CDM, refer link

We will cover following topics as part of this blog:

• Explore standard entities
• Create custom entities, fields
• Define relationships among entities
• Import data from excel

Explore standard entities:

CDM provides set of almost all standard business entities that represent most commonly used entities across business and applications such as Account, Appointment, Contact, Business Unit, Email, Product etc.

• Login to powerapps.com. Select Data from left navigation and clinic on Entities. You will be shown with list of standard entities available

• By default, you will see few entities. Change filter on top from Default to All to see all entities.

• Similar to entities, CDM also provides list of standard global option-sets to use among entities. Select Option Sets in left navigation

Create custom entity:

• Select entities and click on + New Entity. You will be asked to enter name and description. Once entered Click on Next

• Click on Primary Name to change name of your primary field attribute. Note – You cannot change data type for Primary field

• Add new fields as required. To add a new field click on + Add Field option in top. I have added 3 more fields as below:

• Click on Save Entity to finally create your entity. It will take some time as it creates default views, forms in the background.

Define relationships:

CDM allows you to relate entities together through relationships which is similar to relationships in SQL Server. In our case we will link Company entity which we just created with standard Account entity which is by default available in CDM.

• Select Relationships and click on + Add Relationship under Entities -> Company. Select type of relationship as One-to-Many, Many-to-One, Many-to-Many

• Select Primary entity and lookup field name and click Done

Import Data in Company Entity:

I have an excel file uploaded to my one drive and looks like below. We will use this excel to load data in Company entity we created above.

Company	Website	Phone	Account
HCL	www.hcl.com	43743874	Test1
Microsoft	www.microsoft.com	47474744	Test2
Power Objects	www.powerobjects.com	48773737	Test3

• Click on Get Data, which will bring up Power Query windows to select your data source. Click on Excel

• You need to provide URL for excel in one drive. To get URL, go to File menu in excel and just before excel sheet name (in my case its excel is named as “Company”) click on “OneDrive-Microsoft” and select Copy path to Clipboard

• Paste copied URL under “File path or URL” and remove ?web=1 from the end of URL else Power Query throws Invalid URL exception. Select “Organization account” for Authentication kind and sign in with your one-drive credentials. Once signed in, you will see “You are currently signed in” message . Click on Next then

• Once connected to your excel data source, it will ask for excel sheet to select. You can select multiple sheets too. Once selected Click Next
• You will see excel data in Power Query window. Power Query is so powerful that you can transform your data with no efforts required such as Add or remove columns, filter data, change data types of columns etc. Click on Next again

• You will then be asked to select your entity. You can select Load to Existing or new Entity (will allow you to create new entity). Selected Load to Existing entity in my case as I already have Company entity created. Select Company from drop down. Since it’s a custom entity, entity name in drop down will appear with prefix. Once entity is selected, all fields will be loaded on right side to map them with excel columns

Note: Currently Power Query does not support incremental or delta data updates. It will override and refresh whole entity data

• Map entity columns with excel columns and Click Next. Make sure field data type in entity and column format in excel matches.

• Data will be imported. Click on Data tab, you will see data in Company entity

Part 2