filtering with virtual tables and relationships

(0) Share

Report

Posted on by asafweis

181

Hi,

I've been experimenting with virtual tables linked to SQL tables. Specifically, I'm creating 1:N relationships to dataverse tables in the hope that I could access nested data in a filter. It seems that there is a limitation on this and was wondering if anyone knows what the best practice for achieving the same result. I know that I can just keep making lookups of the SQL table, but i wanted something a little more elegant and lower overhead.

Any thoughts?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @asafweis,

Could you please share a bit more detail about your specific requirement?

If your goal is to work with nested or related SQL data, note that Power Apps has a direct SQL Server connector that you can use to call stored procedures or views. This approach is usually preferred since doing joins or relationship lookups directly inside Power Apps can lead to delegation limitations and performance overhead.

Using SQL views or stored procedures helps you handle relationships efficiently on the server side, while keeping your app lightweight and responsive.

Was this reply helpful? Yes No
asafweis 181 on at

Like (0)

Report
Copy link

Link copied!

The idea is to use relationships between a dataverse table and an SQL table or view in the same way as you might have between two or more dataverse tables.

I’m actually trying to delegate the overhead to the database rather than processing in the app.

When I try the formula below I keep getting an error message even though I bhave a relationship between these two tables.

Filter(DataverseTable, ID=SQLTable.ID)

Was this reply helpful? Yes No
Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @asafweis,

Can I know what’s the ID data type on both sides?

Is the DataverseTable.ID a GUID, a number, or text, and what about SQLTable.ID?

If the data types don’t match, the app will show errors because Power Apps can’t directly compare two fields from different sources unless they are the same type.

Was this reply helpful? Yes No
Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Also, note that there are many limitations with virtual tables. For example, when I connected Snowflake tables as virtual tables, even simple Filter() functions didn’t work as expected. So relying on relationships or filters directly through virtual tables may not behave the same as native Dataverse tables.

Was this reply helpful? Yes No
asafweis 181 on at

Like (0)

Report
Copy link

Link copied!

I’m comparing two string or text types so this is not the problem. I think I’m just against the limitations of virtual tables. But I am wondering what the best practice is when using sql table in conjunction with dataverse tables in the same canvas app.

Was this reply helpful? Yes No
Suggested answer

Ram Prakash Duraisamy 5,714 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @asafweis

As per OOB Dataverse, we cannot query across Virtual Tables (1:N or N:1), because relationships are not fully queryable the join should happen in the Virtual table here (like Provider level).

Please mark as answer if my suggestion helps.
Subscribe here for More Useful videos : https://www.youtube.com/@rampprakash3991

Was this reply helpful? Yes No
asafweis 181 on at

Like (0)

Report
Copy link

Link copied!

@Ram Prakash Thanks for your help. Can you elaborate about "the join should happen in the Virtual table here (like Provider level)."

I don't quite understand this comment. Thanks.

Was this reply helpful? Yes No