Importing data into a table in Dataverse auto-created by a many-to-many relationship proves to be much less straight-forward than it ought to be. For example, PowerApps Dataflows don’t support importing to them. In this post, I’ll showcase two methods to import data using both Power Automate and the Dataverse Web API.
Scenario: The Auto Inventory Manager
I have this Dataverse data model for my Auto Inventory Management solution:
Many Models can belong to a single make. That’s a classic Many-To-One relationship. However, many models come in many different body styles. (Ex: a Honda Civic comes in a hatchback and sedan format, and so does an Acura Integra; both different models). Therefore, a many-to-many relationship between the model and body style is needed. This creates a “hidden” table in Dataverse. Now, it’s absolutely possible to create your own custom join table and create two lookups (many-to-one relationship) between make and body style. In fact, if you need to capture additional columns in that table, that’s a solid argument for one. For this scenario though, simply capturing the association without any extra metadata is sufficient.
Here is my import dataset from excel. It consists of Make and Model Codes which are being used as a alternative key values for those respective tables. This is in addition to the model name, make, year, and the possible body styles comma separated.
Create the Relationship
Within either the Models table or the Body Styles Table, go to relationships and select many-to-many
I’ve already created the relationship as shown below. We’ll revisit the Relationship Name and Relationship table name values later for use in Power Automate and the Dataverse Web API script.
Build the Power Automate
Fetch and Filter Data
Within our Power Automate, use the “List rows present in a table” action to fetch our excel tabular data. In addition, I list rows of the models and Body Styles tables respectively. This step is key because, as we loop through the dataset, we need to filter each item by its Model Code and Body Style Name to find the corresponding record in Dataverse.
Now we are ready to loop through each row in the excel. First, I want to split the body style column since we will need to loop through the resulting array later. Next, I want to filter the models from the prior List Models action where the Model Code equals the model code from the excel. We need to get the model’s primary identifier guid value. I just return the first result since I know in my models table there’s only one model code for the car’s model and year (ex: Mode Code 1 for a 2000 Acura Integra).
After checking to ensure indeed a model was found, we loop again through the split results of the body styles. Filtering on that body style, this time we want to return the @odata.id value for the result instead of the primary identifier (it’ll make sense for the next step!).
Relate Rows
Here’s where the magic happens: Relate Rows. I’ll select the model table, and under relationship, select the relationship created earlier and use the primary identifier for the Model under Row ID and relate with with the Body Style’s @odata.id.
Verify the Results
Adding a Sub-grid to my model driven app on the Model’s Main Form, we can choose the related table and add to the form.
After Publishing a Model Driven App, we can then observe that indeed, many body styles belong to the 2000 Acura Integra model record of hatchback and Sedan
Querying the Many-to-Many Table
Naturally, after relating these records, you probably want to know how to query the many-to-many table. Turns out, this is not that straight-forward! The actual “hidden” table created by Dataverse is not select-able when you go to list rows and select from the table name dropdown. Instead, you need to enter a custom value, know the “Relationship table name” from the relationship created earlier and add the word “set” onto the end of it.
In this example, the relationship table name is: spdy_model_spdy_bodystyleset.
I’ll take it a step further and filter on the model id for a “2000 Integra” (I’m using a prior action to list the models where the name equals this value, and using it’s primary guid identifier). The results of which we can see in the next compose action. We get the 3 guid values: the primary id from the many-to-many table and the primary guid values for both the model and the body style.
These values on their own could allow us to apply to each, loop through, get the the model row and body style row by id, blah, blah,blah… but that method would be slow and consume more api calls. I hear you ask: how can we get the Body Style Name, Model Name, and other details using one single query? The answer is to use fetch XML and join the tables! Using the model table, we join on the many to many and and body style many to one relationships. I strongly recommend using Fetch XML Builder to make creating the query much easier.
Using a select action after that, I can extract the specific values I want:
{
"inputs": {
"from": "@outputs('List_Models')?['body/value']",
"select": {
"Model Name": "@item()?['spdy_name']",
"Body Style": "@item()?['b.spdy_name']"
}
}
}Note above I needed to specify the table alias the property is under. Since I aliased the Body Style table “b” and want to extract the Name, I used item()?[‘b.spdy_name’]
And our end results!
Use the Dataverse Web API to Relate Records
Using Power Shell and the Dataverse Web API, I can effectively perform the same tasks as what my Power Automate is doing.
Note that I am dot sourcing my dataverse access token and environment parameters as I have discussed in a previous post: How to Obtain an Access Token for the Dataverse Web API Using PowerShell
<#
.SYNOPSIS
Relates Rows using the Dataverse API between two tables that have a Many to Many Relationship
.DESCRIPTION
Schange the source and destination tables to the entity set name of the table and update the
Relationship name to that of the "Relationship name" on the many-to-many relationship details
#>
$TableParams = @{
entitysetName_Source = "spdy_models"
entitysetName_Destination = "spdy_bodystyles"
M2M_RelationshipName = "spdy_Model_spdy_BodyStyle_spdy_BodyStyle"
}
# Import Data Files
$FileName = "Model.csv"
$ImporthPath = (Resolve-Path ..\_data).Path
$ModelData = Import-Csv $(join-path $ImporthPath -ChildPath $FileName)
# Begin - GET Access Token
$ErrorActionPreference = 'STOP'
. .\GetDataverseAccessToken.ps1
. .\DataverseParams.ps1
$DataverseParameters = Get-DataverseParams
$Params =@{
ClientId = $DataverseParameters.ClientId
TenantId = $DataverseParameters.TenantId
ClientSecret = $DataverseParameters.ClientSecret
EnvironmentURL = $DataverseParameters.EnvironmentURL
Scope = "$($DataverseParameters.EnvironmentURL)/.default"
}
$authResponseObject = Get-DataverseAccessToken @Params
$HeaderInfo = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
Foreach ($model in $ModelData){
$Name = $model.make + " " + $model.year + " " + $model.model
$BodyStylesArray = $model.body_styles -split ","
# GET the Model based on the Model Code
$ModelParams = @{
URI = "$($Params.EnvironmentURL)api/data/v9.1/$($TableParams.entitysetName_Source)?`$filter=spdy_modelcode eq $($model.ModelCode)"
Method = 'GET'
Headers = $HeaderInfo
}
$ModelResponse = (Invoke-RestMethod @ModelParams).value
If($ModelResponse){
$ModelGUID = $ModelResponse.spdy_modelid
Foreach ($bodystyle in $BodyStylesArray){
# Fetch the Body Style Record for the current item
$StyleObject = @{
URI = "$($Params.EnvironmentURL)/api/data/v9.1/$($TableParams.entitysetName_Destination)?`$filter=spdy_name eq '$($bodystyle)'"
Headers = $HeaderInfo
Method = 'GET'
}
$BodyStyleResponse = (Invoke-RestMethod @StyleObject).value
If($BodyStyleResponse){
$StyleGUID = $BodyStyleResponse.spdy_bodystyleid
# Associate the Many-To-Many between Model and Body Style
$ManyObj = @{
URI = "$($Params.EnvironmentURL)/api/data/v9.1/$($TableParams.entitysetName_Source)($($ModelGUID))/$($TableParams.M2M_RelationshipName)/`$ref"
Headers = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Accept" = "application/json"
"Content-Type" = "application/json"
}
Method = 'POST'
Body = @{
"@odata.id" = "$($Params.EnvironmentURL)api/data/v9.1/$($TableParams.entitysetName_Destination)($StyleGUID)"
} | ConvertTo-Json -Depth 2
}
# Relate the Rows
Invoke-RestMethod @ManyObj | Out-Null
Write-Host "Associated Model $($bodystyle) with $($ModelResponse.spdy_name)" -ForegroundColor Cyan
} else {
Write-Warning "Body Style Name $($bodystyle) not found in Dataverse"
}
}
} else {
Write-Warning "$Name not found in dataverse models table"
}
}
Building off of the prior script, we use Fetch XML to fetch the related Body Styles for the 2000 Integra:
$Name = "2000 Integra"
# Fetch XML Query
$FetchXML = @"
<fetch top="50">
<entity name="spdy_model">
<attribute name="spdy_manufacturer" />
<attribute name="spdy_modelid" />
<attribute name="spdy_name" />
<filter>
<condition attribute="spdy_name" operator="eq" value="$($Name)" />
</filter>
<link-entity name="spdy_model_spdy_bodystyle" from="spdy_modelid" to="spdy_modelid" alias="a" intersect="true">
<link-entity name="spdy_bodystyle" from="spdy_bodystyleid" to="spdy_bodystyleid" alias="b" intersect="true">
<attribute name="spdy_bodystylecode" />
<attribute name="spdy_name" />
</link-entity>
</link-entity>
</entity>
</fetch>
"@
$ModelParamsFetchXML = @{
URI = "$($Params.EnvironmentURL)api/data/v9.1/$($TableParams.entitysetName_Source)?fetchXml= $($FetchXML)"
Method = 'GET'
Headers = $HeaderInfo
}
(Invoke-RestMethod @ModelParamsFetchXML).value
Conclusion
And there you have it! Many-to-many relationships are, in my opinion, some of the trickiest to work with in Dataverse. In many cases, you can even engineer a simpler solution by creating your own custom join table instead.
But if you do use native many-to-many relationships, as in my scenario, you now know some of the nuances involved with importing data into the hidden linking table.
