cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vladimir84
Helper V
Helper V

[BUG] Code After SQL ClearCollect Executes First

Hello,

Briefly, a rectangle has a visibility set to loadingSpinner variable and when I am changing something in the dropdown, this is the OnChange:

Set( loadingSpinner, true);

ClearCollect(colCollection,SQLTable);

Set( loadingSpinner, false);

 

The problem is that the loadingSpinner gets to be true and false, while there are 3 dots still spinning because the data is being transferred/filtered/loaded from the SQL. 

 

Essentially, it's still showing the old data (not being cleared) when the loading spinner disappears. 

 

This is a problem in the certainty of the succession of the code - it's not following the order in which it was written!

 

I kindly ask attention to this matter.

 

1 ACCEPTED SOLUTION

Accepted Solutions
vladimir84
Helper V
Helper V

Hi @RandyHayes ,

Apparently, Clear() and Collect() separately instead of ClearCollect(), and the code before and after should be synchronous. 

View solution in original post

12 REPLIES 12
zmorek
Community Champion
Community Champion

It's following the formula as it's written.

 

Where is colCollection displaying the "old" data? Are there a ton of controls on that screen? The more complex the screen is, the longer it could take the controls on it to refresh.

According to you, I'm lying? It is NOT following the formula.

 

It does not matter where the collection is being displayed.

There no controls on the screen.

 

regardless of how complex the clearcollect function is, first it needs to complete, before the next command is put into place.

Instead, as this is a complex SQL query, it's not waiting for the clearcollect to complete.

RandyHayes
Super User
Super User

@vladimir84 

You must first realize that you have no "code".  You have formulas that consist of functions that are evaluated in the order they are presented.

Next you need to realize that PowerApps performs most of its operations asynchronously.  So, first and foremost on the Collect bit that you are trying to do, the only thing that really happens is that the collection is cleared and the session manager is then "triggered" to clear its contents and set itself up for on-demand data gathering.

If PowerApps actually waited for data operations to complete, the performance experience in PowerApps would be horrible.

So, as your operations are being performed async, you need to account for this in your design.  In general adding statements that duplicate data that has already been duplicated in your datasource (I am referring to the internal object known as your datasource) that is a duplicate (on-demand) of the data in your App session tables (in the cloud) that are a duplicate of the real data in your datasource...it is best to cut out the duplication into a collection and just reference the datasource directly.

 

To go to @zmorek 's point...

PowerApps does not "execute" like code would.  It is a referential platform that evaluates formulas wherever they may be.  And, it does it all the time whenever a formula is impacted.  

To realize this, you really need to think "Excel".  PowerApps was modeled after Excel  Just like in Excel, all formulas in cells are evaluated based on what they reference.  It is a very different way than coding an application.

 

If you really want to see exactly what is going on when formulas are evaluated and impacted by changes, looking at the Monitoring Tool in PowerApps should prove of interest.

 

Hopefully this will provide some insight and help.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
vladimir84
Helper V
Helper V

Hi @RandyHayes and thank you for the exhaustive explanation. But, this needs to be confirmed by somebody working for Microsoft, as our company has an agreement with Microsoft. 

 

Unless there is a willingness to do it here, I will have to follow this up via our internal channels. 

 

This is not at all acceptable, because it's not documented (at least nobody told us), and we cannot keep bending our business logic to suit Microsoft's shortcomings. We have to have a reliable platform.

 

I disagree that the performance would be bad in the case of a synchronous mode, because without the data, the app is useless. Furthermore, it has been established to be a bad practice by Microsoft's blog to use the data source directly. And we have observed this in our work. Doing so actually made the apps unusable. That's why we collect and filter - so that the app would work. 

 

For a gallery, if we were to put a dropdown that takes the data source from the SQL, PowerApps is actually sending for EACH row a separate SQL query to fetch the data. That's insane!

 

We need to be absolutely certain of the process, when it starts and ends.

RandyHayes
Super User
Super User

@vladimir84 

I would suggest takin your grievances to Microsoft then.  This is how PowerApps works.  I highly doubt that they will be changing it to accommodate your misunderstanding of how it should work.

 

PowerApps does NOT go to the datasource (the actual database) as you suggest.  If it did, then it would be horrible performance.  PowerApps establishes a session table in your cloud session (this is what a session ID is all about).  It works from that for tables.  The only time it goes back to the datasource is if you can delegate something to it.  Otherwise it works directly with the data it has gathered and fills in during the session load.

 

It appears that you have some criticality to the data and operations that you are trying to do, so my only recommendation is that I would consider creating a developed app rather than using a no-code product like PowerApps as it is not developed and does not work like a developed app.  

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes  I understand that you're very passionate and willing to prove yourself in some way here. But I kindly ask you to take that tone with someone else.

 

You are not the person I asked for advice, you're not Microsoft's representative or defence attorney, we are not in any working or subordinate-manager relationship, and you have no right to judge my request, my understanding of the Microsoft's technology, or to lecture me on how it works, or what I should be doing. I am quite confident in my understanding, and I have Microsoft-issued credentials that back my experience.

 

Your observations have been noted, but your way of expressing them is not appreciated. 

 

 

 

RandyHayes
Super User
Super User

@vladimir84 

I apologize if you took it that way, that was not my intention.  I am simply stating how things work based on my experience with it.  What you are trying to have happen will not happen as you expect.

I am not here to be combative with people, I am providing advice based on experience.  You yourself stated that "at least no one told us" - this implies a misunderstanding, so that is what I went from.

Trying to fight with the people that are trying to help you is not going to get you very far.  Your initial reply had a tone of combative rhetoric.  You said "Unless there is a willingness to do it here", therefore I concurred with you that you should indeed take this to your internal channels.  Best of luck with that.

 

You are now stating that I am not the person you asked.  When you post on this forum, you are asking anyone.  If you have a particular person you are wanting to ask, then I would suggest doing so rather than an open forum post.

 

As you are not asking me, I will not continue to respond.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
vladimir84
Helper V
Helper V

@RandyHayes 

In that case I apologize to you for misunderstanding your tone, when you said:

"You must first realize" and "to accommodate your misunderstanding "

 

I am glad that the tone is that of cooperation, instead of confrontation, and that there are no ill-intentions on either side. Because only the cooperation is the way to move forward.

 

To clarify what I said:

"at least no one told us" - refers to the fact that limitations are not well documented, and during the frequent contacts I had with MS, these and other limits are not communicated effectively. And they are a hindrance to the development, when not provided for from the start. 

 

"Unless there is a willingness to do it here" - means, we need an official first-hand confirmation in order to adopt this as an internal rule. If a Microsoft tech were to intervene here and say "asynchronous it is! Here is a link to the official documentation", I could mark the topic as solved, and let know about these limits and features internally. 

 

 

RandyHayes
Super User
Super User

@vladimir84 

Yes, there was no ill will or malice intended in any way.  I was responding to your comments as I saw.

Anyway, let's put that little spat behind us - it is all too common in textual interchanges that tones, intentions and meanings are completely lost and can lead to issues.

 

So back to the issue.  If you want direct confirmation then I would seek it from Microsoft as you desire.  I cannot speak on their behalf, but I can tell you that this is from my standpoint async.  You can confirm this in the Monitor Tool.  There are many actions that when initiated in the app will cause an event to occur (in the monitor) you can then interact in the app itself and see those events...THEN you end up seeing a bunch of other events occur because the first thing you did was being done asynchronously. 

My suggestions came from this being the case that I have seen since the beginning and to say that if you are relying on things to be done exactly in the order of your formula, then it will lead to heartache.  In the beginning I *really* wished there was some option to turn of the async on a datasource and make results be sync. But that never happened, so I have always designed around the need for synchronous results.

AND, when I need those type of results that way, then I will initiate some formula that will guarantee the results.

For example, a Flow...if you issue a Run on a flow, it will send the run action off to PowerAutomate and move on evaluating the rest of the formula.  However, if you set a variable (even if just a scoped With variable), then evaluation of the rest of the formula will only occur once the flow completes and returns a result to assign to the variable.

So too perhaps this might be helpful in your case... 

   Set(varCollection, SQLTable);

As collections have special in-memory database abilities and, to an extent, will do async actions.  Variables do not that I am aware of.

 

NOW...another factor to consider is that PowerApps will get data when explicitly demanded.  That is...if the DataSource supports it.  SQL Does support this.  So, if you've not looked at the "Explicit Column Selection" feature in your App settings, you might want to turn that off to see if it changes your experience.

 

As for the other points - documentation - I have to say it is still quite weak in many ways.  Most of the samples are good examples of how to NOT do something.  Many "you need to know this" points are hidden between the lines.  And in general it is left up to experience, trial and error, and consulting with the community for answers.

Keeping in mind that PowerApps is still quite young and ever changing.  Stability is a real concern.  Limitations abound.   I have to say that there seem to be way too many incidents of new releases coming out that break things that it shouldn't.  Most everything gets resolved in some way, but...and this was my point in the last response...if you are looking for that level of criticality and stability, then developing a real app in a development platform is the best option.  I rely on PowerApps for all of our clients and our own internal apps, but if my client was to say "this is critical", I would have to state the above points as a warning.  In other words, I wouldn't run the power grid from it, but I sure would put together a lot of apps to automate and make more efficient the processes that would normally be tedious and time consuming and not ultra-critical.

 

Anyway...as you state, you're looking for a direct response from Microsoft in regard to all of this, and to that I wished you best of luck (meant with a little criticism toward the response...not toward you).  And I believe you will be best satisfied with their response.  SO, all of our banter is just that, some banter between two PowerApps people. 

I too apologize that we got off the rails there a bit and I hope there are no hard feelings as again, my intentions were pure. I felt I was responding as stated.   

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

  It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd. at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3rd, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!       Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪🌠

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

March User Group Update: New Groups and Upcoming Events!

  Welcome to this month’s celebration of our Community User Groups and exciting User Group events. We’re thrilled to introduce some brand-new user groups that have recently joined our vibrant community. Plus, we’ve got a lineup of engaging events you won’t want to miss. Let’s jump right in: New User Groups   Sacramento Power Platform GroupANZ Power Platform COE User GroupPower Platform MongoliaPower Platform User Group OmanPower Platform User Group Delta StateMid Michigan Power Platform Upcoming Events  DUG4MFG - Quarterly Meetup - Microsoft Demand PlanningDate: 19 Mar 2024 | 10:30 AM to 12:30 PM Central America Standard TimeDescription: Dive into the world of manufacturing with a focus on Demand Planning. Learn from industry experts and share your insights. Dynamics User Group HoustonDate: 07 Mar 2024 | 11:00 AM to 01:00 PM Central America Standard TimeDescription: Houston, get ready for an immersive session on Dynamics 365 and the Power Platform. Connect with fellow professionals and expand your knowledge. Reading Dynamics 365 & Power Platform User Group (Q1)Date: 05 Mar 2024 | 06:00 PM to 09:00 PM GMT Standard TimeDescription: Join our virtual meetup for insightful discussions, demos, and community updates. Let’s kick off Q1 with a bang! Leaders, Create Your Events!    Leaders of existing User Groups, don’t forget to create your events within the Community platform. By doing so, you’ll enable us to share them in future posts and newsletters. Let’s spread the word and make these gatherings even more impactful! Stay tuned for more updates, inspiring stories, and collaborative opportunities from and for our Community User Groups.   P.S. Have an event or success story to share? Reach out to us – we’d love to feature you!

Top Solution Authors
Top Kudoed Authors
Users online (4,963)