In the Dataverse, relationships are defined in a table record's metadata as an explicit reference to another record. These relationships are automatically created when a Lookup column is created, but can also be set up manually. To set these relationships, we'll need our data's IDs.
When setting a relationship on a record, for example, an Account's Primary Contact, we are storing the Unique Identifier of the Contact record along with the fact that it is of the Contact type. This can allow for complex relationship types such as polymorphic lookups which can be set to records from multiple tables, for example, the Regarding field of an activity record.
To establish these relationships during our migration, we'll need to refer to our data diagram and note where data is related. In our example, we have quite a few relationships to both standard and custom tables. In our example SharePoint data source, we used both SharePoint lookup column types as well as storing an ID that relates to another table. Using either method we have access to the ID of the related table on each record. We can set these relationships during import using these IDs. For more complex relationships, we will need to use Power Automate to establish relationships.
To make the lookup columns that we created in the previous configuration article, we have to establish uniqueness in our data by creating keys.
To create these keys, you must first create a new single line of text column called External ID (Deprecated). We will use this column to store our SharePoint ID. I add the deprecated indicator so that it's clear this column should not be maintained post-import. I also name it something generic instead of SharePoint ID so it can be used for other data sources if needed.
Next, under your table go to the Keys section
Create a new Key and reference the External ID (Deprecated) column as the Key column. This key will give us the option to associate our SharePoint data directly during import using only the ID from SharePoint.
If you are using the out-of-the-box User table, you can use the Primary Email column for the key if your data maps cleanly to a user's email address. Also, in our example data source, we are converting the Category and Subcategory choice columns into a single Lookup to a custom Category table. Since those choice values do not contain IDs, our key will be the primary Name column.
Once you have keys created, your lookup columns will be available in the Dataflow and all you have to do is point it to the SharePoint ID of the related table. We'll dive into that part in the import section of this series.
Complex Relationships
As I mentioned before, this only works for basic relationships. What if you have complex relationships that require some more advanced transformation? You have a couple of options; first, try to make it work in the Dataflow using Power Query. Power Query is a very powerful ETL tool and can most likely get the data to a place where you need it. If for any reason that's not going to work (see known limitations here), we'll turn to Power Automate. The most common case for this is the polymorphic Regarding lookup field in activity records.
To create a relationship in Power Automate, I recommend using the When a dataflow refresh completes trigger, but you can also use whichever trigger makes the most sense for your case.
Depending on your case, there may be some data that you need to import into a table using a temporary column the same way we created the External ID column before. For example, if you are storing an external ID for use in an API call to get data from an external data source, you can create custom columns for those IDs.
Regardless, the next step is to establish a relationship. To do this, select the Dataverse connector
Select Update a row assuming the parent record already exists. If not, select Add a new row, the steps are the same.
You may also want to perform this in a loop that loops through all of the Dataverse records in a filtered dataset. To do that, use the Dataverse List rows action before the Update a row action. Then reference the List rows identifier in the update action. Don't forget a filter expression if needed
Now, to set the lookup field, Power Automate wants you to indicate the table and the identifier you are passing into the lookup field. In the example above I'm using the Task table. Task is a standard activity table with a lot of options for its Regarding polymorphic lookup field. Note that all possible options for the Regarding field are listed in the update action. To set the Regarding field to a table record, you must use the corresponding input.
To set one, we need the table's Set Name. To get the Set Name of a table, go to the table in question in your solution, click on the three dots next to it, click Advanced > Tools > Copy set name and you've got it! For most standard tables, it will be the plural name of the table in lowercase, though there are many exceptions. For custom tables, it will be the publisher prefix (with an underscore) followed by the plural name of the table, all lowercase.
Next, you'll need to provide the unique identifier for the related record. This has to be a valid GUID for an existing record. This will usually come from dynamic content, but for my example, I've hardcoded an example GUID value. Note the format of the set name followed by the unique identifier wrapped in parenthesis. This is how the API wants the lookup field formatted when setting those values in Power Automate.
It's impossible to account for all situations where your data may require a Power Automate flow to build relationships if you want to get help with your specific situation, post a comment below and I or someone else may be able to help.
All situations ultimately boil down to this step. Get the ID of the related record and update your main record to include it in the lookup. Whether it's polymorphic, a basic one-to-many, or any other relationship type, it will follow this format. Luckily the vast majority fall into the first category where the relationships will be established during import.
With this knowledge, we now have everything we need to import data! In my next post, we'll go over the Dataflow import process with examples for each SharePoint data type and how to get them into Dataverse.