By 
In this “Create Generative Answers in Copilot” article, we will learn about how to create generative answers based on the values that come from SharePoint Online List dynamically. Here, I have taken the employee leave management automation using the Copilot bot as an use case. I have designed the Leave Request application using the adaptive card designer, then integrated it in the Copilot Topic, where I have passed parameters from the SharePoint Online list and sent leave request details to the SharePoint Online Leave Management list dynamically.
So, today’s article will be very interesting, and you will learn something new today. So let’s get started without delaying it.
Introduction: Create Generative Answers in Copilot With SharePoint Online List Data
By the end of this article, you will learn the following topics about Copilot Studio and Power Automate Flow:
- Step-by-step guide on creating a Custom Copilot in Copilot Studio.
- Learn how to build a custom topic within Copilot Studio.
- Discover how to call a Power Automate flow directly from Copilot Studio.
- Configure a dynamic data source using a SharePoint Online List for Create Generative Answers.
- Query logged-in user details using the User Profile action in Power Automate.
- Retrieve and display logged-in user leave information dynamically from a SharePoint Online Leave Management list.
- Calculate the difference between two dates efficiently using Power Automate.
- Utilize the sub function in Power Automate to compute the difference between two numbers.
- Dynamically parse the Get Items output in Power Automate for more flexible data handling.
- Format date columns in both Power Automate and Copilot Studio with ease.
- Seamlessly send Power Automate flow output to Copilot Studio for automated responses.
- Design an adaptive card for an employee leave application in Copilot and integrate dynamic values from a SharePoint Online list.
- Construct dynamic formulas in Copilot Topic by leveraging Power Automate output and passing it to the Create Generative Answers node.
- Integrate your custom Copilot with a Microsoft Teams channel to streamline collaboration.
- Deploy your custom Copilot in Microsoft Teams, and perform thorough testing for optimal functionality.
Create Generative Answers in Copilot With SharePoint Online List Data
In the following section, I will explain each step involved in this custom Copilot bot.
Create Leave Management List in SharePoint Online
This is my leave management list I have created in my demo site, which maintains leave details of each employee like total annual leave, number of days leave taken, remaining leave, etc.
Create Power Automate flow for Copilot Studio
In this demo, I have created two Power Automate flows; one flow is used to get the employee leave details and another one to create leave requests in the SharePoint Online Leave Management list dynamically.
Flow Number 1: Get Employee leave Details
Below is my get employee leave details flow, which is responsible for getting the logged-in user leave details dynamically from the SharePoint Online leave management list.
Description of the above flow:
Let’s expand each action to see what it offers.
Explanation:
Basically, this flow is querying the employee leave management list based on the logged-in user email ID and sending back the output to Copilot Studio for Generative AI to process the user question. To get the logged-in user email dynamically, I have used the “Get my profile (V2)” action.
Then, based on the logged-in user email ID, querying the logged-in employee leave details. Then, parsing the output using the Parse JSON action, finally sending the output to the Copilot Studio bot.
You may use the below sample JSON to parse the output, or you may create your own sample JSON:
{ "value": [ { "ID": 1, "Title": "Leave Request", "NumberofDaysLeave": 1, "TotalLeave": 30, "NumberofRemainingLeave": 30, "LeaveStatus": { "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPChoice", "Value": "Submitted" }, "Comment": "Test", "FromDate": "2024-10-08", "ToDate": "2024-10-10", "EmployeeName": { "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", "Claims": "i:0#.f|membership|mym365labadmin2@ztncx.onmicrosoft.com", "DisplayName": "Lab Admin 2", "Email": "MyM365LabAdmin2@ztncx.onmicrosoft.com", "Picture": "https://ztncx.sharepoint.com/sites/DemoSite/_layouts/15/UserPhoto.aspx?Size=L&AccountName=MyM365LabAdmin2@ztncx.onmicrosoft.com", "Department": "IT", "JobTitle": "SharePoint Admin" } } ] }
Flow Number 2: Send Leave Request From Copilot
This flow is responsible for creating leave requests from Copilot Studio based on the user request.
Description of the Create Leave Request flow:
Let’s expand each action to see what it offers.
Until the “Get Items” action, this flow is the same as the previous get employee leave details flow, except the input parameters. This flow has three input parameters: “From Leave Date,” “To Leave Date,” and “Leave Comment.” These details will be sent from Copilot Bot.
Then, checking the condition whether the employee exists in the employee leave management list, if yes, performing the update operation.
To check whether the current logged-in employee exists in the SharePoint Online list, I have used the below formula: if the length of the Get Items output is greater than 0, the current logged-in user is found in the SharePoint Online Leave Management list.
length(outputs('Get_items')?['body/value'])
Passing the below parameters in the Update item action:
Site URL, List Name, ID and Total Leave from the Get Items action output.
For the From Date, pass the below formula where “date” is the “From Leave Date” input parameter:
formatDateTime(triggerBody()['date'], 'yyyy-MM-dd')
For the “To Date”, pass the below formula where “date_1” is the “To Leave Date” input parameter:
formatDateTime(triggerBody()['date_1'], 'yyyy-MM-dd')
To calculate the number of days leave applied, I have passed the below formula where it finds the difference between the “To Leave Date (date_1)” and “From Leave Date (date)“, then passes this to the “Number of Leave Days” column.
div(sub(ticks(triggerBody()?['date_1']), ticks(triggerBody()?['date'])), 864000000000)
Then, calculating the “Number of Remaining Leave” using the below formula, where “date_1” is the “To Leave Date” and “date” is the “From Leave Date. It basically subtracts the number of days leave taken from the total leave.
sub(
items('Apply_to_each')?['TotalLeave'],
div(
sub(
ticks(triggerBody()?['date_1']),
ticks(triggerBody()?['date'])
),
864000000000
)
)
Then, passing the leave comments from the input parameter variable, and for Total leave, just assigning the same value from the Get Items output.
This flow doesn’t need to send any values to Copilot, so you can ignore the “Respond to Copilot” configuration. Though I have created a sample text type variable, just say “Leave request created successfully.”.
By now, I am done with the flow and SharePoint part; next, I will move to Copilot Studio.
Create Custom Topics in Microsoft Copilot and Configure Create generative answers with custom data
I have created a custom Copilot with the two custom topics. One topic is responsible for getting the employee leave details, and another topic is responsible for creating a new leave request in the SharePoint Online Leave Management list.
Topic Number 1: Leave status
This is my topic to get leave status dynamically for the logged-in user from the SharePoint Online list.
In this topic, I have added an action to call my Power Automate flow to get the leave details. Here is my “Get Employee Leave Details” flow.
The output is stored in a string variable.
Then, parse this string output to a record. To do this, first select the data type as “from sample data,” then pass the sample JSON schema. You can pass the sample JSON mentioned at the top of this article, and then this will be converted to a record data type.
I have stored the parse value output to a global variable.
Then, I have added a “Create generative answers” node where, for the input, I have passed “Activity.Text” from the system variable section.
Then, for the data source, I have passed the below formula for the custom data (you will get it from Edit data source -> Classic Data -> Custom data menu).
ForAll(
Global.varRcdParsedEmployeeDetails.value,
{
Content:
EmployeeName.DisplayName & " has applied for leave from " & FromDate &
" to " & ToDate &
" for "&Text(NumberofDaysLeave)&" day(s)."&
" Total leave balance is " & Text(TotalLeave) &
". Remaining leave balance is " & Text(NumberofRemainingLeave) &
". Leave Status: " & LeaveStatus.Value &
". Comment: "&Comment,
ContentLocation: Blank()
}
)
If you try to pass the global record variable in the form of a table, you will not get any errors while saving the Copilot, but this will not work; at run time, you will get error. So, we need to pass the value in the form of a formula using the ForAll loop.
With the above value, at run time, we will get the below error:
Error Message: Missing or invalid assignment for variable ‘searchResult.Content’. Error Code: MissingOrInvalidRequiredProperty Conversation Id: 83a43bee-729e-460c-8207-59c1bb5b28f4 Time (UTC): 2024-10-09T13:30:43.482Z
Now, I will move on to create a leave request in the SharePoint topic.
Topic Number 2: Apply leave
Before sending a new leave request in SharePoint, first I am calling my get employee leave details flow to get the logged-in employee display name and remaining leave details from the SharePoint list and displaying these details in the leave request adaptive card dynamically.
Then, I will call my other flow to create new leave request details in the SharePoint Leave Management list.
This topic is the same as the other one until parsing the get employee leave details topic. So, I am skipping that steps.
I will continue from the “Adaptive card” action.
Leave Request adaptive card in Copilot Studio TopicI have used the below JSON for this adaptive card:
{ "$schema": "http://adaptivecards.io/schemas/adaptive-card.json", "type": "AdaptiveCard", "version": "1.4", "body": [ { "type": "Container", "style": "emphasis", "items": [ { "type": "TextBlock", "text": "Employee Leave Request", "weight": "Bolder", "size": "Large", "color": "Accent", "horizontalAlignment": "Center", "spacing": "Large", "wrap": true }, { "type": "TextBlock", "text": "Employee:", "weight": "Bolder", "wrap": true, "color": "Good" }, { "type": "Input.Text", "id": "employeeName", "placeholder": "Employee Name", "value": "John Doe", // Placeholder, replace with dynamic value if available "isReadOnly": true, "spacing": "Medium" }, { "type": "TextBlock", "text": "From Date:", "weight": "Bolder", "wrap": true, "color": "Good" }, { "type": "Input.Date", "id": "fromDate", "placeholder": "Select From Date", "spacing": "Medium" }, { "type": "TextBlock", "text": "To Date:", "weight": "Bolder", "wrap": true, "color": "Good" }, { "type": "Input.Date", "id": "toDate", "placeholder": "Select To Date", "spacing": "Medium", "validation": { "necessity": "required", "rules": [ { "type": "ValueComparison", "comparison": "greaterThan", "compareToId": "fromDate", "message": "To Date must be greater than From Date" } ] } }, { "type": "TextBlock", "text": "Available Leave (from SharePoint):", "weight": "Bolder", "wrap": true, "spacing": "Small", "color": "Attention" }, { "type": "Input.Number", "id": "availableLeave", "value": 0, "isReadOnly": true, "weight": "Lighter", "spacing": "Small", "separator": true }, { "type": "TextBlock", "text": "Comments:", "weight": "Bolder", "wrap": true, "spacing": "Small" }, { "type": "Input.Text", "id": "comments", "placeholder": "Add your comments here", "isMultiline": true, "style": "text", "spacing": "Small", "borderColor": "Light", "borderThickness": "1" } ], "spacing": "Large", "backgroundColor": "#f3f6f9", "borderColor": "#dde1e6", "borderThickness": "1" } ], "actions": [ { "type": "Action.Submit", "title": "Submit Leave Request", "id": "submitLeaveRequest", "style": "positive" } ], "style": "emphasis", "padding": "Medium" }
Then, open your adaptive card in edit formula mode from the adaptive card properties menu.
For employee display name, pass the below formula:
First(Topic.varRcdApplyLeaveParseValueEmployeeDetails.value).EmployeeName.DisplayName
Similarly, for the remaining leave value, pass the below formula, then rest all and keep as is:
First(Topic.varRcdApplyLeaveParseValueEmployeeDetails.value).NumberofRemainingLeave
The final step in this demo is to call my send leave request from Copilot flow.
All the parameters I have passed in the above flow action from the output of the leave request adaptive card.
That’s it; I am done with the topic set up.
Finally, I have deployed this Copilot in my Microsoft Teams channel.
YouTube Video Demo: Create Generative Answers in Copilot With SharePoint Online List Data
You can watch this complete walk-through from my YouTube channel.
Summary: Pass SharePoint Online List Dynamic Data to Create Generative Answers in Copilot
Thus in this article, I provided a detailed step-by-step guide on how to create a Custom Copilot in Copilot Studio. I explained how to build a custom topic, call a Power Automate flow directly from Copilot, and configure dynamic data sources using a SharePoint Online List for generative answers. The article covered how to query user details with the User Profile action in Power Automate, retrieve and display leave information from a SharePoint Leave Management list, and calculate date differences.
I also discussed using the “sub” function to compute values, parsing the Get Items output, formatting dates in Power Automate and Copilot Studio, and sending flow outputs to Copilot for automated responses. Finally, I showed how to design an adaptive card for a leave application, construct dynamic formulas for generative answers, integrate the Copilot with Microsoft Teams, and deploy it for team collaboration and testing.
About Post Author
