Nested tables from SQL view

[Dave007]

Hi, 

I would like to know if it's possible to get table from SQL view with nested table like sharepoint and dataverse.

I'm able to do it from a stored procedure, get JSON from it, than parse it in microsoft flow and return it to powerapps.

But it will be far simple and delegatable if it's possible do it from SQL view. Anyone know if it possible ?

Scuse my english but information are very limited in french 🙂

Thanks

[timl]

Hi @Dave007 

I don't believe it's possible to create a view with nested tables and to return that to Power Apps.

The alternative way would be to build a view that returns the expanded resultset by joining the parent and child records. You can then call the GroupBy function in Power Apps to effectively create your child nested tables based on the view.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-groupby

[Dave007 · Accepted solution]

Hi @timl 

Thanks for your response.

The problem when I use GroupBy, it's the warning for the delegation and the 500 limit.

After some research, I've create SQL view with nested table as json in a column.

It's not a perfect solution but I parse the JSON with  matchall function and I collect it in a temporary collection to past it to my component.

I've try to set my record in a variable, but the nested table in the variable fail when I reload the app but work fine in a collection.

With this, I can get delegated view from SQL and when I select item in my gallery, I use matchall function to create a temporary collection with needed nested table and finaly I past it to my component.

Thanks.