How to populate lookup columns using Copilot Studio’s Dataverse Connector

Today I was building an agent using Copilot Studio and one of the things that I was trying to do was using Dataverse knowledge for the Agent to access information from a table, reason over it, and use the Dataverse connector to create a new record if needed. The problem that I was facing was that I needed to populate two lookup fields to create a new record and after trying for several hours I decided to see if I could find a solution online. Unfortunately I couldn’t, so with some help of my friend and coworker Sahib I was able to figure it out! Since there was no info on how to do this online, I figured I would write an article about it!

Scenario

I want to build an agent that helps users find the best vendor pricing for a specific product. When a user engages with the agent, it needs to review a table in Dataverse that stores supplier product information. In this table I am storing things like the name of the supplier, the product, the unit price for that product etc. This is a table that is updated regularly, as this data changes as frequently. The agent needs to give me the best option to order from, meaning I need it to look at the unit price, delivery fee and delivery time and decide which supplier is best to order from. Once it does that, it needs to give me that information and then ask if I want to create a purchase order for this product, for the supplier it selected. If I say yes, I want it to generate a new purchase order (in my custom purchase order table) and give me all the details on the purchase order it just created.

Dataverse Tables

I created 5 tables in Dataverse to store this information. First I created a ‘Supplier’ table with columns for the supplier name, phone number and email address. Then I created a ‘Product’ table with columns for the product name and product number, which acts as a unique ID. I then created a manual many-to-many table called ‘Supplier Products’ where I created a lookup column to the ‘Supplier’ and ‘Product’ tables, and columns for unit price, delivery fee and delivery time. I then created my ‘Inventory’ table, which has a lookup to my ‘Product’ table and columns for Current QTY, Reorder Point and Max Stock Level. Lastly I created the ‘Purchase Order’ table with lookup columns to the ‘Supplier’ and ‘Product’ table, and columns for unit price, quantity and total price. (The total price is a calculated field (quantity X unit price). I added some data for the agent to use.

Agent Configuration

Now that the tables have been created and data has been added, I am ready to create and configure my agent. In copilotstudio.microsoft.com I select ‘Agents’ from the sitemap on the left and click on ‘+New Agent’. (Make sure you’re in the correct environment!). When you click a new agent, it will take you to a screen where you can describe what you want your agent to do. I want to manually create and configure my agent, so I click on ‘Configure’ instead. I call the agent ‘Inventory Agent and in the description I enter: ‘An AI-powered agent assisting staff to select suppliers based on price, delivery fees and delivery times.’ I leave the rest of the canvas blank and click the ‘Create’ button. Once the agent is created, make sure you disable the ‘Web Search’ on the overview tab, then click on ‘Settings’ to disable general knowledge.
The first thing I am going to do is add Dataverse knowledge. I click on the ‘Knowledge tab and click ‘Add knowledge’. I select ‘Dataverse’ from the screen and select the Product, Purchase Order, Supplier and Supplier Product tables. The agent will use this table to review the data in the supplier product table to see which supplier has the best offer for a particular product.

The next step is to configure the Dataverse connector to allow the agent to create a new purchase order for the product that triggered the agent. I navigate to the tools tab on the top of the page and click ‘Add tool’. From the screen that pops-up I select the Microsoft Dataverse connector, and I select the ‘Add a new row to selected environment action. I then click ‘Add and configure’ and the connector configuration screen opens up. In the name field I enter ‘Create a new purchase order‘ and in the description field (this is the field that explains to the agent what this action does) I enter ‘Create a new purchase order row in dataverse. Populate the product field with the related Product Number from the previously selected best supplier product.’
In the input section I change the ‘Fill using’ field to ‘Custom value’ as I don’t want the agent to decide which environment to create the purchase order in. I then select the environment I want it to use. I do the same for the table name, I don’t want the agent to have to figure out which table to create a new row in, so I set the ‘Fill using’ field to ‘Custom value’ and select the ‘Purchase Order” table. I add additional columns for the agent to update by clicking on the ‘Add input’ button and selecting the ‘Unit Price’ column. I repeat this for the ‘Quantity’, ‘Product’ and ‘Supplier columns’. Since I am l want the agent to fill those columns, I leave the ‘Fill using’ settings to ‘Dynamically fill with AI’. I need to explain to the agent what each of these columns represent, which I can do by clicking on the ‘Customize’ icon next to each column. I enter the following for each column and click the save button.

Unit Price | Description: Unit price of the product you selected
Quantity | Description: Always ask the user
Product(Products) | Description: Product GUID
Supplier(Suppliers) | Description: Supplier GUID

I know that this is not going to work, because the Dataverse knowledge that I configured previously doesn’t contain unique row id’s (aka GUIDs), so I need to add an agent flow to get the GUIDs for the ‘Supplier’ row and the ‘Product’ row the agent recommended, so it can create the purchase order. I navigate back to the Tools tab, click ‘Add tool’, and select ‘Agent Flow’. This opens the agent flow designer. I click on the first node in the flow (When an agent calls a flow) and add two text inputs called ‘product name’ and ‘supplier name’. In the ‘Please enter your input’ section I enter ‘Name of the product’.
I add a Dataverse ‘List rows‘ step for the products table and under ‘select columns’ I enter the system name of the product name. Below the ‘Filter rows’ I enter: rsm_name eq and select the product name input I added in the first step. Don’t forget to put this variable in single quotes and parenthesis around the whole thing!

I then add a ‘Compose’ step, which you can find under Data Operation and rename it to ‘ComposeProductID’. Since I want the only record the list rows step is going to return, I create the following expression for the ‘Input’ field: first(outputs(‘List_rows’)?[‘body/value’])?[‘rsm_productid’] This will make sure I get ONE ROW that holds the GUID for the product. I need to repeat the same steps for the Supplier table so I add another ‘List Rows’ step, which is automatically named ‘List Rows 1’. I select the suppliers table and under select columns I enter the system names for the supplier name column and the supplierid. Under filter rows I enter (rsm_suppliername eq ‘supplier name variable from the first input‘) as shown in the screenshot below.

I then add a second ‘Compose’ step, which rename it to ‘ComposeSupplierID’. I create the following expression for the ‘Input’ field: first(outputs(‘List_rows’)?[‘body/value’])?[‘rsm_supplierid’] Just like before, this compose actions will make sure I get ONE row returned that holds the GUID for the supplier row. You can see what the expression looks like in the screenshot below. The last step is to pass the data back to the agent. However, passing the GUIDs alone is not going to allow the agent to populate the correct values when it’s creating a new Purchase Order using the Dataverse connector. If you are familiar with building power automate flows where you create dataverse records, you might be aware of the fact that to set the value of a lookup column, you will need to include the plural name of the table first! The same goes for the connector. In order to populate the product and supplier lookup, we need to add this information before passing it to the agent..

Click on the ‘Respond to the agent’ step and add two text outputs. The first output is called Product GUID and in the ‘enter a value to respond with’ I enter /rsm_products (which is the plural name of the custom product table) followed by the output of the ComposeProductID step in parentheses. In the description field I enter: ‘GUID of product row’. In the second output I enter ‘Supplier ID’ for the name, in the ‘Enter a value to repond’ I enter /rsm_suppliers (this is the plural name of the custom suppliers table) followed by the output of the ComposeSupplierID step in parentheses. Doing it this way will allow the agent to grab all the information needed to create a new purchase order. Click on ‘Save Draft’ to save the flow. After it’s saved, you can rename your flow by navigating to the ‘Overview’ page and clicking ‘Edit’. I named my flow ‘Get Product ID and Supplier ID’. Go back to the designer tab to publish your flow. Feel free to test it!

Navigate back to agents and open the agent. Navigate to the tools tab and click ‘+Add a tool’. Search for and then select the flow you just created, then click the ‘add and configure’ button. In the description section make sure you explain what this flow does. I entered: ‘This tool gets the product ID and supplier ID’. You’ll notice that the inputs are already configured, so there is nothing else left to do here.
Now that we have configured the knowledge and tools for the agent, it’s time to enter the instructions. You can enter the instructions for the agent by navigating to the ‘overview’ tab of the agent. In the instructions field I enter what I want it to do and what tools it should use and when. You can add the tool to the description by typing / and selecting the tool. Below is a screenshot of the agent description field.

After you saved the instructions your agent is done! All you have to do now is make sure to test, test and test! I hope you enjoyed this article! Be sure to check in again next week or subscribe here to never miss another post!