[BUG] Code After SQL ClearCollect Executes First

[vladimir84]

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.

[zmorek]

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.

[vladimir84]

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]

@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.

[vladimir84]

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]

@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.  

[vladimir84]

@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]

@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.

[vladimir84]

@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]

@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.