Lookup Columns? Let Your Copilot Handle It

Introduction

If you’ve ever tried to patch a lookup column in Dataverse you know this can be quite tricky. The same goes for Copilot Studio. But what if I told you you can actually use orchestration and Dataverse action to let your agent figure it out itself? It’s a clever trick that showcases just how powerful agents are going to become in the near future.

The Use Case

We have a customer service desk that logs customer complaints. Each complaint is associated with a customer via a many-to-one (N:1) relationship—meaning multiple complaints can link to a single customer.
To enable our Customer Service Agent to log complaints, we need to patch a lookup column on the Complaints table.

The problem

When using the Dataverse action “Add a row to selected environment,” simply providing the record name for a lookup column won’t work. Doing so throws an error—see the image below.

The Solution

The correct way to patch a lookup column in Dataverse via Power Automate is by passing the GUID of the record within the logical entity name in brackets—e.g., fsvtech_customers(<GUID>).
I’ve seen complex implementations in Copilot using variables to fetch the GUID, but there’s a better way.
By turning on orchestration, your agent can identify and chain the necessary actions itself.

First we set up our “Search Customer GUID action”. This will likely be called “Tools” in preview mode (copilotstudio.preview.microsoft.com).

1. We add a new action

2. 

   We look for the list rows from selected environment action

3. 

   We connect to Dataverse and we add our action right away after which we are going to make our adjustments.

4. We then proceed to open the action and fill in the following parameters for the Details page. See below image

5. We then move to the Inputs page where we will add some input parameters we need.

For now we will change the Environment input to Set as a value and select our environment of choice. And for our table we also opt for Set as a value and we select our Customers (fsvtech_customers) table. Though the add button (pointed out in image) we will add three more input parameters.

We will additionally add the input parameter Row Count, Select Columns and Filter rows

For Row Count we opt for set as a value again and set the value to 1.

For Select Columns we will also opt for Set as a value and choose fsvtech_customerid. This combination will only return the GUID we are looking for.

Lastly, for the Filter Rows input we will let the agent decide. Therefore, we opt for Dynamically fill with best option (default). In our “Description” we state: This is the name of the customer that needs to be filtered from the customer table. Use fsvtech_name eq the user provides to filter the row.

That’s it for our Search Customer GUID action.

6. Now we create our add complaint action. We create a new action and we choose the add a new row to selected environment actio

   

7. Once we have added this action we add the following on the Details page.

Now for the Inputs page we set the environment and table name as a set value again, choosing Complaints as the table name this time.

For our primary name column we opt for the following parameters.

And now for our LookUp Column, our hard to patch one, we fill in the description the following sentence: The related customer to this complaint. Use /fsvtech_customer() for input for this field.

Now let’s see this in action. We have our customer called John Doe, and when we want to add a complaint for him, first we check if John Doe is in the database already. Our agent checks using the Search Customer GUID action and does not find a record.

We then proceed to add a record for John Doe.

And now we add a complaint for John Doe in our database tying all our actions together. As you can see in the image below our agent uses the Search Customer GUID again to find the GUID and the Add complaint action uses that GUID to fill in the LookUp column.

You can use these actions in topics or agent flows to make it more streamlined but this makes it easy to patch those nasty LookUp columns!

Closing words

Copilot is extremely powerful but you need to know how to use it. And the improvements are following each other rapidly. As I was writing this blog new changes in the UI went live. That is how fast it goes. The new orchestration functionality helps to increase the power of copilot but you need to understand how to use it. There are still some quircks but this is definitely going to change the way we will input our data in the future. Start using it as soon as possible and try it out for yourself so you can get ahead of the pack!