Flow fails when query returns no results

246
10
10-03-2022 06:19 AM
erica_tefft
Regular Contributor

Hi @SeanKMcGinnis  - 

I have 3 Flows which run daily to check a feature layer to see if certain conditions are met. If they are, an email is sent. In many cases there will be no records to return which satisfy the conditions of my query. When this happens, the Flows fail. I am concerned because I do not want the Flows to be automatically disabled by Power Automate after they fail too many times. I need an "escape" so that the entire Flow does not fail when my query is not met. 

Here is the error I see:

erica_tefft_0-1664803063976.png

 

You recommended I try this solution, but it does not work regardless of where I add the "configure run after to has failed".

This question was originally raised in this GeoNet post here

I need a work around to this ASAP. 

Thank you,

Erica

Erica
0 Kudos
10 Replies
PhilLarkin1
Regular Contributor

Hi Erica

I wonder if it would be helpful to use the Terminate action. It is in the Control action group. It should give you the ability to exit the flow with the status Failed, Cancelled, Succeeded or custom value. I've not tested anything other than Failed so please post back if you attempt this.

0 Kudos
PhilLarkin1
Regular Contributor

Also, in review of your last post, it seems you duplicated the query and set it to run after the fail of the first. If it is in fact the same query it should also fail.

Regardless, you'll need to signal to Power Automate that this isn't a problem even though the query returns no results. Hopefully this branch in your flow is an appropriate place to terminate the entire flow.

0 Kudos
erica_tefft
Regular Contributor

Hi @PhilLarkin1 -

I realized I'd messed up the suggestion from Sean (re: duplicate query connector), but even after correcting my error that did not work. 

As far as the Terminate control... I've actually never used this. I am curious where this would go in the flow.

Also, this morning I noticed some different behavior in the flows and I am wondering if @SeanKMcGinnis  and his team have changed anything... I have no features which are returned on the query, however the flow doesn't fail like it was previously. When I check the flow results the outputs also look different than they did a few days ago. I've added two screen captures below for comparison. Now it looks like the ArcGIS Connector is OK with the fact that no results were returned from the query and the flow succeeds without failing. 

Original error response received when no results returned:

erica_tefft_0-1665146053218.png

 

New output when no results returned, flow succeeds:

erica_tefft_2-1665146096476.png

 

Erica
0 Kudos
PhilLarkin1
Regular Contributor

Great! Looks like the connector treats 0 records like a non-failed state. Seems like your problem is solved.

If you wanted to test Terminate you could set it to run after 'Get data from feature layer'. The status should be Canceled, Succeeded, or a custom value. I'm not sure if that allows for the flow to end in a non-failed state. Might be a something to try next time you find yourself in this situation.

 

PhilLarkin1_0-1665156257119.png

 

SeanKMcGinnis
Esri Contributor

Good morning @erica_tefft & @PhilLarkin1,

To give you an update, the team was able to recreate the issue and create a backend solution that fix was pushed to production on October 4th.

This action now returns the results from the query, including 0 results, instead of failing as was previously occurring.

It seems like this is addressing the issue that you were encountering, but please let me know if you are still having issues.

-sean
Sean McGinnis - Microsoft 365 Product Manager
erica_tefft
Regular Contributor

@SeanKMcGinnis  - 

Thank you. 

Another issue has come up - I've received this error message out of the blue. Do I need to manually refresh my credentials every 14 days? This Flow ran fine on from the 4th to 7th after your fix was implemented, but failed all weekend.

If so, when will this be fixed? I do not need to do this with the Survey123 Connector. This is really awful if this is what I think it is... 

erica_tefft_0-1665491840375.png

 

Erica
0 Kudos
SeanKMcGinnis
Esri Contributor

@erica_tefft, I am sorry you think the experience is really awful, but we are working with technology disparities between the different platforms. There is a difference in the way Microsoft and ArcGIS Online grants the refresh tokens and that is what is causing the tokens to expire after 14 days. We are actively working with the ArcGIS and Microsoft teams to come up with a resolution - it is one of our highest priorities.

The near term fix, which is planned to be included in the next release, is using a different OAuth method which will extend the refresh token to 90 days to give us additional time to work through a secure and sustainable solution between the different parties.

The reason you are not encountering this with Survey123 - the submitter's token is being included in the webhook payload. Since the ArcGIS Connector for Power Automate is not authenticating outside of the connection, there is no other way to get a token that can be passed securely given the permissions needed to edit a feature layer.

-sean
Sean McGinnis - Microsoft 365 Product Manager
SeanKMcGinnis
Esri Contributor

@erica_tefft - I wanted to share an update with you. The recent published update to the ArcGIS Connector for Power Automate has extended the token timeout from 14 days to 90 days. We are continuing to have conversations with Microsoft to come up with a more permanent solution, but hopefully this helps.

-sean
Sean McGinnis - Microsoft 365 Product Manager
erica_tefft
Regular Contributor

Hi @SeanKMcGinnis  - 

This is great news. While a permanent solution would be nice, 90 days is so much more reasonable for revisiting a flow to refresh the connection. Thank you for letting me know this has been implemented. The new connectors are really great and this new addition will make them even better. 

Thank you!

Erica
0 Kudos