Data Export Service: How to Handle Lookup Columns

In this blog, we will uncover how primary fields and look up fields are created and synchronized in the Microsoft Azure SQL Database, in a customer-owned Microsoft Azure subscription using Data Export Service.

Unlike Dynamics 365 On-Premise installations where developers and administrators have some level of control on backend databases and can facilitate an organization's reporting with ETL teams to query data directly from a SQL server for reporting, a Dynamics 365 Online implementation does not allow users to query a CRM database directly in online environments. To overcome this challenge, Microsoft has come up with a Data Export add-on service available on Microsoft AppSource that adds the ability replicate Dynamics 365 data to an Azure SQL Database. This helps facilitate reporting and allows ETL teams to consume data for reporting and analytic purposes.

The Data Export Service add-on transforms data columns while replicating data from Dynamics 365 to an Azure SQL database. Here are a few points that need to be considered:

Get Lookup Values from the Data Export Service Database

The Data Export Service synchronizes lookup fields slightly differently in the Azure SQL database tables than in the same columns created by the Dynamics 365 on-premises database.

Data Export Service creates two columns for each lookup in Dynamics 365 as opposed to three columns in the on-premises database.

  1. <Lookupfield>ID - This column is similar to the id column created by Dynamics 365 in on-premises databases. It holds the GUID of the primary id of the referenced table
  2. <Lookupfield>_entitytype - This column holds the entity schema name of the referenced entity. This is different from Dynamics 365 on-premises database, where the database is used to store the referenced entity's ObjectTypecode in <Lookupfield>Type column.
  3. <Lookupfield>name: Data Export Service never copies the 'name' column in the Azure SQL Database; so it is always empty for every lookup field.

Example: Refer to the screenshot below to compare the differences between the same CustomerId look up field created by Data Export Service in Azure SQL Database vs. the on-premises Database created by Dynamics 365.

Dynamics 365 On-Premises Database:

Data Export Service

Azure SQL Database Look up field created by Data Export Service:

Data Export Service

TargetMetadata Reference Tables for Lookup

The Data Export Service creates a TargetMetadata master table to hold all the referenced entities and attributes for lookup fields. Each row represents the lookup between the Primary (Referencing) entity and the referencing attribute to Referenced entity and attribute.

Example: The screenshot below shows all the referenced look ups for the contact entity. Here the CreatedBy on contact table is referencing the system user table's systemuserid attribute.

Data Export Service

The Data Export Service is only available for Microsoft Dynamics 365 Online, version 8.0 and onwards.

Learn more about Microsoft Dynamics 365 in the PowerObjects blog!

Happy Dynamics 365'ing!

Microsoft’s New Power Apps Makes Building Business Apps Easier

Power Apps is a new service for building and using custom business apps that connect to your data. It also works across the web and mobile - without the time and expense of custom software development. Today's blog will walk you through the process of generating an app using a trial version of Dynamics 365 online.

Let's start off with a little background. Power Apps connects to existing data sources and services including Dynamics 365, OneDrive, Microsoft Azure, SharePoint, Salesforce, Excel, Google Drive, and Dropbox, to automatically generate a meaningful business app for your business. You can instantly publish apps via web and mobile including laptops, tablets, and phones (Android, iOS, and Windows). It also allows you to work in a visual designer to customize a fully-functional app and build apps that use device capabilities including cameras, GPS, and pen control. And the best part is that you get all of this without having to code!

Power Apps Works for Employees and Developers

Perks for employees:

Perks for developers and IT professionals:

Generating an App Automatically

You can quickly generate apps using data sources such as:

Generating an App Using a Trial Version of Dynamics 365 Online:

Open Microsoft PowerApps and Select New

Microsoft’s New PowerApps

Select Dynamics 365
Microsoft’s New PowerApps

Select New Connection

Microsoft’s New PowerApps

Select Dynamics CRM Online & Select Connect

Microsoft’s New PowerApps

Select Power Objects (Note: this will connect to the Dynamics CRM Instance associated with you Power Apps login)
Displays all entities for the CRM instance
Microsoft’s New PowerApps
Select an Entity & select Connect (Note: for this blog, I have chosen the contacts entity)
Microsoft’s New PowerApps
Building the Phone App Templates
Microsoft’s New PowerApps
Screen Configurations
Microsoft’s New PowerApps

Power Apps OOB display 3 Screen templates

  • Browse Screen
  • Detail Screen
  • Edit Screen

Each Template will have multiple corresponding Screen Layouts.

  • Browse Items, One line Description
  • Browse Items, One line Description, Rating
  • Browse Items, Thumbnail image, Header and Description
  • Browse Items, Header and Description
  • Browse Items, Header, Sub Header and Description
  • Browse Items, Header, Sub Header, Description and Footer
  • Browse Items, Header, Description and Link
  • Browse Items, Grid with Pictures
  • Browse Items, Header, Pictures, Description and Footer
Layout Configuration
Microsoft’s New PowerAppsMicrosoft’s New PowerAppsMicrosoft’s New PowerAppsMicrosoft’s New PowerAppsMicrosoft’s New PowerApps
Fields can be added to the Detail Screen layout by drag and drop Entity Fields
Individual Fields can be formatted

Microsoft’s New PowerApps

You can add any control in a variety of categories by clicking the Insert tab of the toolbar and selecting any of the following categories.

You can add a variety of controls to your app, and configure their properties directly, from the toolbar, or in the formula bar.
Microsoft’s New PowerAppsMicrosoft’s New PowerApps
You can also add any control in a variety of categories by clicking the Insert tab of the toolbar and selecting any of the following categories.

  • Text Box
  • Button
  • Text
  • Controls
  • Gallery
  • Forms
  • Media
  • Charts
  • Icons
    • Add a screen
    • Add a data connection
    • Show a list of items
    • Add a form
    • Show the current user
    • Add a list control
    • Add a chart
    • Add multimedia
    • Scan a barcode
    • Add a flow
    • Add a scrolling screen
    • Build a global app

And there you have it! For more information on PowerApps, check out the Microsoft resources below.

Happy CRM'ing!