Dataverse: adding rows to N-N relationship table

(0) Share

Report

Posted on by JS-05081236-0

I have a datamodel in Dataverse with two main tables

Documents
Roles

There is a many-to-many relationship between these two tables, and this is done (behind the scenes) by creating a relationship table. In my case it is called "gp_Document_gp_Role_gp_Role".

This table is not directly exposed in the Dataverse connector, but I have discovered through the Web API that the table is exposed as "gp_document_gp_roleset" and I use that to iterate over the items using the normal "List rows" action.

It works like a charm 😊.

However - I also need to add rows to that table, but it is causing me some problems. When I use the "Add a new row" action and type in "gp_document_gp_roleset" as custom value, I need to specify a "Body" property:

I thought is would just be a JSON fragment like

{
 "field 1": "value 1",
 "field 2": "value 2",
}

But it doesn't work and I cannot find any documentation about how to use this property.

Can anyone help me figure out what the format of the content in the "Body" property should be?

Thanks 😊

Jesper

Categories:

Using connectors

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Matthy79 4,180 Super User 2024 Season 1 on at

Like (0)

Report

Why don’t you want to use “relate” and “unrelate” action to do this?

Was this reply helpful? Yes No
JS-05081236-0 83 on at

Like (0)

Report

Hi @Matthy79

Well, I have looked at it a few times, and I did not use it since the documentation said that it was to be used for "one-to-many" relationships, and my challenge is with a "Many-to-many" relationship.

I just looked at the documentation again, a now I see that it also mentions N-N relationships. So either Microsoft changed the documentation or I misread it earlier (I tend to lean towards the last option 😁).
So I tried it out, and it works! There was a bit of an issue with the version of the Web API needed, but the error description was well suited to point me in the right direction.

My full flow is this:

Thank you for your help, @Matthy79 !

😊
/Jesper

Was this reply helpful? Yes No
Matthy79 4,180 Super User 2024 Season 1 on at

Like (0)

Report

Hmm… it is sad that you have to use the full path with this action. Other Dataverse action work fine without the need of a full path. I would recommend to store that information in an environment variable, otherwise you will get troubles after deploying it to a different environment. (Just in case you are working with solutions and deployment)

Was this reply helpful? Yes No
JS-05081236-0 83 on at

Like (0)

Report

Yes, it is super annoying 🤬. I have already started looking into this and created the environment variable. We use Azure DevOps Pipelines for deployment, and I can see that I can create a "deployment settings file", so I need to look into how to make this work.

Thank you, again 😊

/Jesper

Was this reply helpful? Yes No
JS-05081236-0 83 on at

Like (1)

Report
Hi @Matthy79
A quick update from here: I have been working on getting my Azure Pipelines to update the Environment variable - but have not been able to solve it. But I figured out another way to get the Web API URI. It ocurred to me that I have a previous action listing rows in Dataverse and upon inspection of the output I could see that the URI was indeed part of the JSON chunk of data. So with a little bit of expression magic, I am now able to figure out what the Web API URI is for the current environment - at runtime. So right now I do not need the Environment variable ... it seems.

substring( outputs('List_rows')?['body']['@odata.context'] ,0 ,nthIndexOf( outputs('List_rows')?['body']['@odata.context'] ,'/' ,3 ) )
It's a bit messy ... but fairly stable and gets the job done.
😊

Was this reply helpful? Yes No