Skip to main content
Microsoft logo
Power Apps
    • AI Builder
    • Automate processes
    • Azure + Power Apps
    • Build apps
    • Connect data
    • Portals
  • Pricing
    • Overview
    • Become a Partner
    • Find a Partner
    • Find partner offers
    • Partner GTM Resources
    • Blog
    • Customer stories
    • Developer Plan
    • Documentation
    • For IT Leaders
    • Roadmap
    • Self-paced learning
    • Webinars
    • App development topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Power Apps Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Apps
    • Building Power Apps
    • Microsoft Dataverse
    • AI Builder
    • Power Apps Governance and Administering
    • Power Apps Pro Dev & ISV
    • Power Apps Portals
    • Connector Development
    • Power Query
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Community Blog
    • Power Apps Community Blog
    • Galleries
    • Community Connections & How-To Videos
    • Community App Samples
    • Webinars and Video Gallery
    • Canvas Apps Components Samples
    • Kid Zone
    • Business Value Webinars and Video Gallery
    • Emergency Response Gallery
    • Events
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Ideas
    • Power Apps Ideas
    • Community Engagement
    • Demo Extravaganza 2021
    • Demo Extravaganza - Components 2021
    • Power Apps Community Demo Extravaganza 2020
    • Experimental
    • Error Handling
    • Power Apps Experimental Features
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Power Apps Community
    • Galleries
    • Webinars and Video Gallery
    • Re: Type and Run a SQL Query inside PowerApps

    Re: Type and Run a SQL Query inside PowerApps

    01-23-2019 21:26 PM

    sarveshshinde15
    Advocate IV
    26836 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    mr-dang
    mr-dang Community Champion
    Community Champion
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Type and Run a SQL Query inside PowerApps

    ‎05-04-2018 01:16 PM

    Knowing how to return an array to PowerApps will make many more connectors useful to you. The existing "Execute a SQL query" action in Flow is now capable of sending its resulting array back into the app where it was triggered. This allows you to leverage the SQL language from within your apps and retrieve thousands upon thousands of records. To put this into perspective: you can type a query to filter objects in a gallery or dropdown menu in addition to the current capabilities in PowerApps.

     

     

     

     

     

     

    watch?v=K_H5r0nzwy4

    Microsoft Employee
    @8bitclassroom
    Preview file
    264 KB
    Labels:
    • Labels:
    • Community Tips & Tricks
    • SQL Magic
    Message 1 of 11
    29,441 Views
    10 Kudos
    Reply
    • All posts
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
    • Next »
    DavesTechTips
    Super User DavesTechTips
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎11-02-2018 08:26 AM

    Very nice Mr Dang.

     

    I have a situation where the SQL table has more than 2000 records, and the only option I can see it to filter the data according to a list of ids from another collection. The problem is that the list of ids that should be used is not in a table in SQL so the 'in' operator is not delegatable to SQL, which means that only 2000 records become eligable for the filter operation.

     

    You solution above will do the trick to return more than 2000 records, I am just concerned about the number of flow runs that would be required if this is the primary way to populate the PowerApp gallery with data, instead of using the SQL connector directly from PowerApps.

     

    Any thoughts on the above will be welcome!

    Message 2 of 11
    28,188 Views
    0 Kudos
    Reply
    mr-dang
    mr-dang Community Champion
    Community Champion
    In response to DavesTechTips
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎11-02-2018 05:11 PM

    Hi Dawid,

    In general, it's not a good practice to bring everything in anyway 🙂 For galleries, bringing in 16,000 records means showing the labels and images for all of them. This video is mostly a solution for times when you just need to overcome the basic limitations and it's not a frequent occurrence. 

     

    I have been running SQL views for out of the box transformation on my original SQL tables. I changed the amount of data I needed to bring in and run all aggregate functions in Views.

     

    For your situation with ids, I'd try to send the list of ids to Flow as a string of JSON. Parse the string so that it is formatted as JSON. Then run an Apply to Each action that appends records to an array variable. Send that array back to PowerApps via Request - Response.

     

    This way, you do not need to use ForAll in PowerApps and run the flow multiple times--you effectively tell Flow to do it instead. Let me know if I understood your situation correctly.

    Microsoft Employee
    @8bitclassroom
    Message 3 of 11
    28,180 Views
    0 Kudos
    Reply
    DavesTechTips
    Super User DavesTechTips
    Super User
    In response to mr-dang
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎11-04-2018 09:16 PM

    Hi Brian

     

    Thanks for you input Brian.

     

    My concern about the number of Flow runs is about the Flow runs to be consumed if a normal user refresh data from the app would use a flow run instead of the native SQL connector. It would be great if PowerApps can issue sql queries directly!

     

    I can pass an array of ids to return in the SQL query to Flow which works, but Flow doesn't support sql queries through the on premises data gateway currently and this is required for the current scenerio.

    Q1: Any idea when this will be supported? Smiley Sad 

     

    Because SQL queries are not supported through the data gateway yet, I can use odata filters instead of SQL queries from Flow, but the apply to each takes long to run, so ideally an odata filter should be used with one GetSQL Rows action in Flow, but I am not able to find a way to pass a list of ids to check in odata filter.  

    Q2: Any ideas to pass something like WFRecordID in ['crm_helpdesk-777','crm_helpdesk-778',crm_helpdesk-779'] in Flow Odata filter? It works if I construct multiple odata statements like "WFRecordID eq 'crm_helpdesk-777' or WFRecordID eq crm_helpdesk-778 or WFRecordID eq 'crm_helpdesk-779", I can only do about 20 ids at a time because of an error "The node count limit of '100' has been exceeded. To increase the limit, set the 'MaxNodeCount' property on EnableQueryAttribute or ODataValidationSettings." I get if I pass more than 20 expressions in the odata filter. I can loop through sets of 20 ids, but that doesn't feel like the most efficient way to do it.

     

    Thank you Brian, have a great day.

    Message 4 of 11
    28,144 Views
    0 Kudos
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎11-14-2018 08:15 AM

    @mr-dang Thanks for the video! However, it did not seem to work for with my on-premise gateway to SQL. 

     

    error.jpg

    Message 5 of 11
    27,959 Views
    0 Kudos
    Reply
    sarveshshinde15
    sarveshshinde15 Advocate IV
    Advocate IV
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎01-23-2019 09:26 PM

    Hi,

    Have you tried using Execute store procedure action? Use that. It works well with it.

    Convert your select query to a stored procedure with parameters. 

    Hope this helps.

    ExecutestoredprocedurefromFlow.PNG

    Thanks

    Sarvesh

    Message 6 of 11
    26,836 Views
    1 Kudo
    Reply
    sarveshshinde15
    sarveshshinde15 Advocate IV
    Advocate IV
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎01-23-2019 09:35 PM

    Thanks for the video and information. It helps a lot. Appreciate it.

    Message 7 of 11
    26,835 Views
    0 Kudos
    Reply
    manlamcheng
    manlamcheng Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎01-29-2019 02:35 PM

    hello Dang

    I follow the same procedures, I am calling a stored procedures and it returns data in Flow, but the collection on the PowerApps only shows one column "Value", and it changes from false to true after Flow is completed, do you know what's wrong with it ?

     

    thanks a lot !

    Message 8 of 11
    26,747 Views
    0 Kudos
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎10-04-2019 01:32 PM

    Collection comes back empty even though flow shows results.

     

    Also, I can't get "ResultSets.Table1" in flow.  

    Message 9 of 11
    22,948 Views
    0 Kudos
    Reply
    MawashiKid
    MawashiKid
    Frequent Visitor
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎10-26-2019 09:28 AM

    RE: Also, I can't get "ResultSets.Table1" in flow.

     

    I must say I found this video was an excellent introduction.., though if you want to go deeper... in explanations then I encourage you can to have a look at the following link

    Return an Array from Flow to PowerApps (Response Method) 

     

    in which Brian goes a bit deeper in explaining each and every steps with providing animations. Some cool stuff you may not want to miss. 

    If you scroll down you'll notice that ResultSets.Table1 can be obtained through an expression....


    @Anonymous wrote:

    Collection comes back empty even though flow shows results.

     

    Also, I can't get "ResultSets.Table1" in flow.  


    "If it does not appear, we can use the expression box to manually enter it. Click the Expression tab and in the fx bar, type:

    body('Execute_a_SQL_query').ResultSets.Table1

    This means, "Return the body of the 'Execute a SQL query'* step. Drill down into ResultSets and then into Table1, which is the part you really want." 

    In conclusion, I'll confess that running the Flow didn't work out as expected the 1st time I watch this video.. I thought hmmmm maybe I missed something? Indeed... after carefully reviewing each and every steps in the link provided above I managed to get it working and even more.,, Thanks for sharing Brian. This is real cool stuff.

    Message 10 of 11
    22,538 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • 2
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    Power Apps

    • Sign in
    • Sign up

    Browse

    • Sample apps
    • Services

    Downloads

    • Windows
    • iOS
    • Android

    Learn

    • Documentation
    • Support
    • Community
    • Give feedback
    • Blog
    • Partners

    • © 2022 Microsoft
    • Follow Power Apps
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks