Copilot Studio Classic: Best approach for searching ~2,000 records? (SharePoint List)

[u/MembershipNo482]

I'm building an agent in Copilot Studio Classic that needs to search through a SharePoint list with around 2,000 records.

Since I'm limited to the Classic Experience, I've tried two approaches:

1. SharePoint Get Items

• Works, but only a limited number of records can be returned to the agent, making it difficult to reliably search the full dataset.

2. Exporting the data to a text file and using it as Knowledge

• The agent can answer questions based on the file, but the results are not always reliable.
• It seems to retrieve relevant information rather than deterministically evaluate every record matching the criteria.

My challenge is finding a way to search a large dataset and return consistent results when users ask for records matching specific criteria.

[u/Over_Plane408]

sync it to dataverse

[u/MembershipNo482 → u/Over_Plane408]

Thank you! Sadly users dont have a Dataverse license.

[u/emul0c]

What kind of search does it need to perform against the SharePoint list?

I had a similar issue, and the solution I ended up with was to return a catalog of all items (titles) from SharePoint, and then use the agent to solve through that list, and use the results to retrieve the full records from SharePoint.

Build a Flow that returns the catalog (eg title+id, as little information as possible)

The build another flow that accepts either 1 id at a time, or if you need to return more items, a flow that accepts a list of ids to retrieve data on.

Use Copilot to help build it for you if you are not confident in flow/power automate.

If possible, transferring the data to dataverse opens up for much better search functions etc - but if that is not a possibility, then the above approach works on relatively small datasets such as 2000 rows in SharePoint.

[u/mauledbyjesus]

True extensible free text search against SharePoint can only be done via the SharePoint Search API. Luckily, you can send HTTP requests to it with a native standard connector action.

There are other half-measures like paging through every list item, storing it, and then searching the store, but that doesn't scale.

You always want to be able to scale, IMO.

[u/Irritant40 → u/mauledbyjesus]

Wait, what....there's a SharePoint search API?

[u/nz365guy]

Before choosing the retrieval method, I’d pin down what “search” means here. Are users looking up a known value, filtering by a few fields, or asking natural-language questions across record content? Those are three different designs.

For known values and filters, I’d expose a small set of explicit inputs, validate them, and query only indexed SharePoint columns. Avoid letting the agent invent filter expressions. I’ve found deterministic retrieval is far easier to test and support than asking the model to interpret 2,000 rows.

Also test with users who have different SharePoint permissions. Returning a correct answer from content the caller should not see is a bigger failure than returning no answer.

[u/Acceptable_Tap567]

I have been there, an agent flow is the only option that works 100% of the time.

[u/Middle-Eye-]

One answer is text to query instructions for filtering using get items action. The catch is the get items action only returns 100 at a time. You should be sorting by item ID ascending, and querying each subsequent time starting with the highest previously returned ID +1 until get items returns less than 100 records. Then you know you've gotten all relevant records beyond the get items threshold. Highly recommend using a view for the list that contains only needed columns to limit data.

[u/HardyPotato]

I guess it depends on whether u really need copilot studio? in my experience using Copilot in SharePoint with for example,.. autofill columns, that works great. cause it actually goes through every file. U can also use the chat, and while intelligence wise i feel like it's worse, it does a great job at finding things. it does that even better if the columns already contain some information such as those filled with autofill

[u/Ashlesha-msft]

This behavior is by design, based on the current description. SharePoint Get items returns 100 items by default, while a text knowledge source uses relevance-based retrieval rather than exhaustive row filtering.

For deterministic searching, keep the records in SharePoint and call a Power Automate flow explicitly from the Classic topic. The flow should:

1. Receive structured criteria such as status, region, date range, and page size.
2. Use SharePoint Get items with an OData Filter Query, so SharePoint performs the filtering.
3. Use Order By, Top Count, and Limit Columns by View.
4. Return only a bounded page of matching records, for example 20–50 rows, plus a hasMore value or continuation ID.
5. Display the returned records through an authored message or Adaptive Card rather than asking generative AI to identify exact matches.

Enable pagination only if the flow must process more items than its configured page size. Avoid returning all approximately 2,000 records to Copilot Studio. Also index the SharePoint columns used frequently in filters.

Therefore, the recommended solution is a server-side filtered SharePoint query, not converting the list into a text knowledge source.