cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dave007
Frequent Visitor

Nested tables from SQL view

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

1 ACCEPTED SOLUTION

Accepted Solutions
Dave007
Frequent Visitor

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.

View solution in original post

2 REPLIES 2
timl
Super User
Super User

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
Frequent Visitor

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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,062)