cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SKH19
Level: Powered On

Creating Parent-Child Relationship for Custom Entities

Hi,

I am having a lot of trouble doing something that should be simple, and I think it has to do with the fact that CDS tries to do everything behind the scenes. 

I have 2 custom entities. An Employees entity with various employee details and a Tests entity for various test records, with each employee having multiple records on the Tests entity. So there is a 1-many relationship from Employees (parent) to Tests (child). These two tables are stored in excel spreadsheets.

So how do I get these into CDS custom entities? Conventional wisdom is that I need an index or GUID on the employees, then I join that GUID to the Tests entity. But it seems like CDS is trying to create the GUID behind the scenes and asks me to create a lookup column for the relationship. But how does that lookup column use the GUID from the Employee entity on the Tests Entity, and how does it know on import which records to map to which employees?

If anyone knows how to create custom entity parent-child relationships and import from excel I would appreciate the help.
Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
SKH19
Level: Powered On

Re: Creating Parent-Child Relationship for Custom Entities

@Drrickryp 

Both the Excel files for Employees and Tests had employee name fields. So I imported the excel data for Employees into my Employees custom entity. Then I exported that back out into an excel file that contained the automatically generated GUID for the entity. Then I used Power Query to merge the GUID to the Tests excel file and removed the names, so the Tests excel file now had the GUIDs to represent each employee instead of the names. The new Tests excel file was imported into the Tests entity, with the GUID column in the excel file mapping to the Lookup to Employees.

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Creating Parent-Child Relationship for Custom Entities

Hi @SKH19 ,

Could you please share a bit more ahout your scenario?

Do you want to load data from your Excel table into the custom entities in your CDS?

Further, do you also want to specify the lookup column use the GUID from the Employee entity on the Tests Entity when importing data?

 

If you want to load data from your Excel tables into the custom entities in your CDS, I think the "Get data from excel" option and "Power Query" functionality in CDS could achieve your needs.

More details about importing data from Excel to CDS Entity, please check the following article for help:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-import-export

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-cds-newentity-pq

 

In addition, you also want to specify the lookup column use the GUID from the Employee entity on the Tests Entity when importing data, I afraid that there is no way to achieve your needs in PowerApps currently.

When importing data from Excel table into CDS Entity, there is no way to specify a lookup column on one Entity to use the GUID from another entity. In other words, there is no way to do the LookUp field match when importing data.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SKH19
Level: Powered On

Re: Creating Parent-Child Relationship for Custom Entities


In addition, you also want to specify the lookup column use the GUID from the Employee entity on the Tests Entity when importing data, I afraid that there is no way to achieve your needs in PowerApps currently.

When importing data from Excel table into CDS Entity, there is no way to specify a lookup column on one Entity to use the GUID from another entity. In other words, there is no way to do the LookUp field match when importing data.


@v-xida-msft 

What I ended up doing, which seems to work, is I imported the Employee entity, then joined the GUID it generated on import to the names in my Tests excel table. So when I imported the excel table with the Tests data I matched the new GUID column to the Lookup field.

That seems to work. Unless you see some way the logic is wrong and this will break in the future.

Super User
Super User

Re: Creating Parent-Child Relationship for Custom Entities

Hi Sean
Could you please specify how exactly you "matched" the guid to the lookup column. Did you do this using power query?
SKH19
Level: Powered On

Re: Creating Parent-Child Relationship for Custom Entities

@Drrickryp 

Both the Excel files for Employees and Tests had employee name fields. So I imported the excel data for Employees into my Employees custom entity. Then I exported that back out into an excel file that contained the automatically generated GUID for the entity. Then I used Power Query to merge the GUID to the Tests excel file and removed the names, so the Tests excel file now had the GUIDs to represent each employee instead of the names. The new Tests excel file was imported into the Tests entity, with the GUID column in the excel file mapping to the Lookup to Employees.

View solution in original post

Super User
Super User

Re: Creating Parent-Child Relationship for Custom Entities

Hi @SKH19 

I'll give this one a try.  If it works, it will be the answer to my prayers.

Super User
Super User

Re: Creating Parent-Child Relationship for Custom Entities

Hi @SKH19  & @v-xida-msft 

Using your suggestion, I made a community blog post will an illustrated step by step guide for importing a database to CDS using only Excel/Power Query and PowerApps processes. https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Importing-an-external-relational-databa... It is cumbersome and in my experience the data integration process for large datatables can take quite a while, sometimes several hours. 

IMHO, the adoption of Model Driven Apps for PowerApps has not been more widespread because of the difficulty of integrating relational databases and maintaining the relationships from the external data to the CDS entities.  Hopefully, a more efficient method will be developed to faciliate the process.  I have spoken to @TopShelf-MSFT  in the past regarding this issue and have spent several months looking for solutions. 

SKH19
Level: Powered On

Re: Creating Parent-Child Relationship for Custom Entities

@Drrickryp 

I actually found a better method.

Since CDS is the backbone to Dynamics, all of the data import/manipulation features of Dynamics can actually be used.

  • Go to admin.powerplatform.microsoft.com
  • Click on Environments on the left menu, select the environment you want to build in and click the "More Environment Actions" elipsis. From there you can select:
    1. Open Environment. When the environment opens, click the gear in the top right. Select Advanced Settings. From the top menu, select the arrow next to Settings and then Customizations. Then select Customize the System. This will put you in the standard Dynamics customization panel, which is much more robust and reliable for building out entities, fields and relationships.
    2. Settings. From here you can use the Dynamics Data Import Wizard, as well as bulk deletion, Duplicate detection and other features. The import wizard from here in Dynamics allows you to map lookup fields more explicitly and map option set values. It can also handle empty values here, which the CDS for PowerApps version can't do.

Basically if you want to use CDS, follow these steps and sneak back into the Dynamics side to do all your work. It is much more reliable.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Users Online
Currently online: 194 members 6,152 guests
Please welcome our newest community members: