Power Platform Community Forum Thread Details

I'm creating an agent that have 3 Dataverse tables as knowledge source.

This can be a short summary of the tables

- product table

- price table

- processes table

The product table have a lookup to the price table (N:1 relation)

The processes table have a lookup to the price table (N:1 relation)

I've tried any sort of prompt, instruction or description to let the agent be able to reason on these tables, but I am never able to have a result for my questions.

One of the question i would like the agent to be able to answer/solve is: "Between all the products that have the field X populate with the value Y give me which is the one that have the max an minimum price.". The chain of thought for this question should be: query the product table with the filter "field X = Y" and then for the set of data retrieved taking in consideration all the related rows in the table "price table" and then going with the max - min for a specific row.

Am I giving to much capabilities to copilot studio or I am probably doing something wrong

Thanks so much

Categories:

General topics

Hello Community member!

Copilot Studio supports multiple Dataverse tables (up to 15 per knowledge source) as knowledge, including those with N:1 lookups like your products and processes linking to prices. However, the generative AI for knowledge retrieval uses semantic search, which handles simple queries well (e.g., "Products where field X = Y") but often fails on complex relational tasks like joins and aggregations (max/min prices), as it doesn't natively execute SQL-like logic across tables.

This explains your prompt issues the AI retrieves from individual tables but can't reliably chain lookups or compute aggregates without explicit guidance or orchestration.

You're not overextending capabilities; for such reasoning, use a hybrid approach: knowledge sources for grounding + custom topics/flows for logic. This is best practice for relational Dataverse in production agents.

Optimized Setup

Enhance Knowledge Sources:
- Add all three tables to one source with detailed descriptions: "Products table links via N:1 lookup to Price for pricing; use price lookup for calculations like max/min when filtering products."
- Add synonyms (e.g., "field X" for your column) and glossary terms: "Max price: Highest value in related Price table."
- Test basics: "List products with field X = Y" should retrieve data; if not, refine descriptions.
Custom Topics + Power Automate for Complex Queries:
- Create a topic triggered by "max min price products field X Y."
- Use Generative Answers node (grounded in knowledge) to extract parameters (X, Y).
- Call a cloud flow for the chain: Filter products → join prices → aggregate.
  - Flow Steps:
    - Trigger: Power Apps.
    - List rows (Products): Filter field_x eq '@{Y}', expand lookup $expand=price($select=pricevalue).
    - Select: Extract prices to array.
    - Compose: Max/min via expressions max(outputs('Select')?['pricevalue']) and min.
    - Return JSON: { "maxProduct": "...", "minProduct": "...", "maxPrice": Z, "minPrice": W }.
- In topic: Message node formats: "Max price product: [max], value Z; Min: [min], value W."
Dataverse MCP Alternative:
- Add Dataverse MCP server as a tool for AI-generated queries: Prompt "Filter products X=Y, join price, return max/min." Good for dynamic needs, but test for accuracy (limits: ~20 rows).

Tips

Prompt Engineering: Agent instructions: "For price queries, filter table → lookup related prices → compute max/min." Use chain-of-thought.
Limits: 1000 rows retrieval; flows ensure precision.
Debug: Review Test pane for retrieved data.

This setup delivers reliable results. Share schema/prompts for tweaks!

Best regards,
Jerald Felix