Add values in a many to many tables

(0) Share

Report

Posted on by forstera

331

Hello all,

We have to import Data using PowerShell script from our ERP to the Dataverse and we created several tables.

We have a table 'Persons' and a table 'Lessons'

1 person can be subscribed to several lessons and 1 lesson can have more than 1 person. So, according to the fact we have many to many relation, we created a new table 'PersonsLessons'

So we have now the following schema

When importing our data from our ERP, we first import the persons. The IDPerson comes from our ERP and is in text format.

We then import the Lessons and here again, the IDLesson field comes from our ERP system and is in text mode.

We created in the Dataverse the links between the different tables like the schema here above.

finally, we are importing the subscriptions data form our ERP which contains the IDPerson and the IDLesson but it seems we cannot simply paste the values in the corresponding fields of our table

PersonsLessons. IT seems we first have to make lookup on each table (persons and lessons) to get the value of the column type 'Unique identifier' and then create our record with these 2 'unique identifier' values in the table PersonsLessons.
Can you confirm that ? or maybe is there a way to insert our values into PersonsLessons without having to make a lookup in both tables ?
Thanks for your help 🙂

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Mahendar Pal 191 on at

Like (0)

Report

Hi,

What you have created (PersonsLessons) is a manual n:n relationship, which means you can create record of this entity without entering lookup unless you have make them required? While you are entering value into the lookup field it should be the primary field value for example let's say IDPerson is the primary field from the Person table then it will resolve value when you will enter IDPerson which is present in the person table, but if it is a non primary field then you need to use other options.

Hope it will help.

Was this reply helpful? Yes No
forstera 331 on at

Like (0)

Report

Ok thank you. So, I I understand correctly (sorry for my bad english) and according to the fact that my table is the result of a many to many relationship, I should have 2 primary fields : IDPerson and IDLesson.
In this cas, in my powershell script, I could simply create a new record in my talbe PersonLessons by copying my 2 IDs (IDLesson and and IDPerson) in the corresponding fields without having to make a lookup ?

thanks very much 🙂

Was this reply helpful? Yes No
Mahendar Pal 191 on at

Like (0)

Report

Hi,

Sorry I missed one point from your question you are using powershell, you can create record of your entity but in order setup lookup you need the GUID of the records from Person and Lesson.

While creating Person and Lesson - are you letting system generate GUID or you are passing GUID from the ERP in your script, if yes then record in the dataverse have the same GUID from the ERP, you should be able to refer them directly but if not you need to query query GUIS based on the some property and need to set them.

Was this reply helpful? Yes No
forstera 331 on at

Like (0)

Report

Ok thank you for the precisions. When creating the Persons and Lessons, I get the IDs from the ERP (in the ERP, these are the primary keys). So I should define them as Unique Identifier in my Dataverse Table and it should work ?

Was this reply helpful? Yes No
Verified answer

Mahendar Pal 191 on at

Like (0)

Report

1. Yes, and it will be easy for update and query, and you can use same keys while creating record for your custom n:n entity.
or
2. Setup a field let's say ERP Id and make this field Key (alternate key) which you can use for upsert if this is ongoing integration. https://learn.microsoft.com/en-us/power-apps/developer/data-platform/define-alternate-keys-entity

Was this reply helpful? Yes No
forstera 331 on at

Like (0)

Report

Thanks very much for your help , I'll have a look in that direction 🙂

Was this reply helpful? Yes No