Patch new records to Dataverse with lookup columns and nested records

Are you familiar with using Patch() to create records in Dataverse? Or perhaps you create collections of records in Dataverse tables by patching a collection from your app? Have you ever tried to patch a new record or a collection with a nested object i.e. where you have a lookup column? This gets a bit tricky using Patch() and when trying this myself I was finding myself with the following error:

Invalid argument type: Expecting a record value, but of a different schema.

So it looks like Patch() won’t let us use nested records when creating new records in a table.

A solution with Collect()

So let’s look at an alternative approach. Using Collect() we’re able to write to a collection or directly to the data source to create new records. We can even use it to patch records with nested records, to the data source.

I have a dataverse table which I want to create a new record in, which has a lookup column that I need to populate with another record. Let’s look at how to patch that at once using Collect() rather than patch.

The syntax is as follows.

Collect(Datasource name, record)

But you might be wondering how we handle that column which needs a record value instead of something like a string or a number.

In this case, prior to the Collect() in my formula we will set a variable with the record value which we’ll retrieve using the LookUp() function.

I’m going to use the following syntax to do that

Set(gblRecord, LookUp(Table, Column = "String Value"))

You’ll notice I’m only using 2 parameters or ‘arguments’ in the LookUp function and that is because I want it to return the whole record, not a property of the record which I could retrieve by specifying its name in the third argument.

Now I can use my collect syntax following my Set() statement and reference my global variable in my record for my lookup column when I need it.

An example might look something like this:

Collect(Swimmers, 
{
    Name: "Lewis",
    lcl_currentclass: gblRecord
}
)

In this statement I’m now patching records with nested objects by using the Collect() function instead of Patch().

Creating multiple records – Collection

Now let’s say we’ve created a collection with the data we want to submit to the datasource, what if we now want to submit that collection to the datasource with the lookup column we’re using populated? Very simply we’ll use the following syntax.

Collect(Datasource Name, Collection Name)

Creating multiple records – Gallery or other datasource

Now lets say you want to patch new tables to a record using a number of records from a different table, either from the table with a filter or from a gallery. In this case we can use a ForAll() function to loop through an array we will pass into the function and then create records using Collect() whilst referencing the values of the current record in the loop we’re working on.

We can use the following syntax for this case.

ForAll(arrayName, 
   Collect(
        Datasource Name,
        {
         Record
        }
   )
)

We can still use lookup columns when using the syntax above in the same way that we referenced and populated them in the first example in this post.

If you want to reference and loop through the records displayed in a gallery, you can use galName.AllItems to do this instead of a data source name, filter or collection.

Did you find this post helpful? If you did, be sure to subscribe to get my articles on Low Code and Power Platform delivered straight to your inbox. If there was something you didn’t understand, comment down below or feel free to share an alternative approach.