Dynamically Querying SharePoint Paths with Copilot Studio Agents

Introduction

During a discussion in a WhatsApp community group, a question arose about configuring a Copilot Studio agent for HR-related queries. The scenario involved country-specific policies stored in separate SharePoint folders. The challenge was ensuring that the agent dynamically retrieves content relevant to the user’s location. For example, if someone from the UK asks about maternity leave, the agent should only pull information from the UK folder, not from other countries.

Thanks to the labs I did during Enterprise Copilot Studio Agent Hackathon, I had a solution ready to share.

Scenario Overview

In this example, leave policies are stored in a SharePoint library, with folders organized by country.

SP library

To dynamically reference the correct folder based on the user’s location, we use a global variable (Global.Country) to identify the user’s country and configure the agent accordingly.

Step-by-Step Solution

1. Create a Generative Action

To dynamically query the correct SharePoint folder, follow these steps:

Step 1: Create a New Topic

  • Navigate to the Topics tab and create a new topic named Leave Policy.

Step 2: Add a Trigger Description

  • Provide the following description for the trigger:
    “Use this tool for questions about leave policy and time off.”

Step 3: Add a Generative Answer Node

  • Add a new node by selecting Advanced > Generative Answer.

Gen Ans

Step 4: Configure Input

  • For input, select the Activity.Text system variable.

Activity Text

Step 5: Set Search Properties

  • Go to the properties of the Create Generative Answers node.
  • Select Search only selected sources (ensure no sources are selected).

Step 6: Configure SharePoint Data Source

  • Under Classic Data, toggle Manual Input to Formula for SharePoint.

Step 7: Add a Dynamic Formula

  • Select the ... button and navigate to the Formula tab.
  • Use the following formula to dynamically reference the SharePoint folder based on the user’s country:
  "https://<site>.sharepoint.com/Shared Documents/Leave policies/" & Global.Country

Step 8: Save and Test

  • Save your topic and refresh the test pane.
  • Select a country and ask, “What’s the leave policy?”
  • Validate the results for different countries.

Formula

Example Queries

Searching for leave policy for India Leave Policy for India

Searching for leave policy for France Leave Policy for France

Conclusion

By leveraging Copilot Studio’s dynamic querying capabilities, you can create intelligent agents that provide locationspecific responses.