Creating Parent-Child Relationship for Custom Entities

(0) Share

Report

Posted on by SeanHenderson

1,104

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

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (1)

v-xida-msft on at

Like (0)

Report

Hi @SeanHenderson ,

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,

Was this reply helpful? Yes No
SeanHenderson 1,104 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

SeanHenderson 1,104 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @SeanHenderson

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

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @SeanHenderson & @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-database-into-Common-Data/ba-p/364452 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 @Anonymous in the past regarding this issue and have spent several months looking for solutions.

Was this reply helpful? Yes No
SeanHenderson 1,104 on at

Like (1)

Report
@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:
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.
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.

Was this reply helpful? Yes No