Flow is Running for Days!

[Anonymous]

Ummm, this is concerning, yes?!

Is a recurrence flow, that is designed to simply update the compliance status column of a list, suppose to run for days?

Albeit, the list is nearly 5,000 items; but, still.

If yes, this is normal, is it a resource drain? Am I gonna hear from some administrator some day? Is there a way to determine why is keeps failing periodically and how to fix that problem?

Thoughts?

[ScottShearer]

@Anonymous 

Depending on what you are doing and how many columns are in the list, a Flow may take over a day to run through a large list.

If the list has a large number of columns, you might create a view with only the columns you need and specify that view in "Limit columns by view".

You say that there are nearly 5000 items in the list.  Please note that Get items will retrieve a maximum of 5000 items. 

You might consider using a filter query to limit the items returned to those that meet a specified criteria - in other words only the items that need to be updated.

[Anonymous]

Very Nice!

Any insight on the 'failed' attempt? Its happened more than once.

Unfortunately, the flow is comparing dates on each and every line to determine "Compliance", "Noncompliance", "Certified", every day. Whatever view I'd create would still be the entire length of the list, yes? 

[ScottShearer]

@Anonymous 

It is possible that you might be able to design a filter query to limit your results. If it gets too complicated, it may not be worth it.

Also, the view that I suggested that you create will not limit the rows - only the columns that are returned.  That is useful when you have a "wide" list.

I don't know why it may be failing, but you might consider using the "run after" feature to send you an email immediately if there is an issue or try to handle the error in some other way.  Take a look at the failed run for insight as to why it failed.

[Anonymous]

The List, as I said, has 'Compliant', 'Noncompliant', 'Certified'.

'Certified' does not need to be evaluated.

Is there a way to construct the flow so that it only looks at those items that are 'Compliant' or 'Noncompliant' to determine whether they need to be updated, leaving all the 'Certified' items out, and maybe addressing the Get Items limit issue?

Or, does it still have to go through the entire list FIRST to separate wheat from chaff before evaluating?

[ScottShearer · Accepted solution]

@Anonymous 

You could use a filter query similar to the following to limit the items returned:

Note the following:

• There are single quotes around 'Certified'
• The filter query is case sensitive
• ComplianceStatus is the internal name of the SharePoint column.  Internal names never have spaces and may be different than the display name.  Your internal column name may not be the same as mine.

[Anonymous]

Never mind; I realized the answer as soon as I hit send!

Ok, makes sense.

Before I do, one last....

New document will be uploaded to the list; those evaluated for 'Compliance/Noncompliance' and 'Certified'. 

Those evaluated as 'Certified' only need to be evaluated once at upload. How will what you propose impact the ability for the List to evaluate both, if at all?

[Anonymous]

This is a look in...

Am I understanding your recommendation to be adding the " ComplianceStatus ne 'Certified' " in place of the (obviously empty) variable?

Can it also/instead be added to the variable at initialization; just to clear up that point?

Also, I am evaluating for 'Certified' later on, so how will you change impact that...

[ScottShearer]

@Anonymous 

You can use a variable and you can initialize it to the filter query.

Using a filter query will limit the items returned to include only those that meet the criteria.  No items that have a status of Certified will be included.

[Anonymous]

Do you then agree that implementing the above will cause any 'new' items requiring evaluation for designation of 'Certified' as appropriate will not be evaluated and their ComplianceStatus will be left blank and the 'Yes', 'No' conditions in the flow would no longer be necessary is 'Certified' is not being evaluated?

And, if the above is true, is it your opinion that evaluating all lines, though close to the limit, is the appropriate set up of the flow?

Thank you.

[ScottShearer · Accepted solution]

@Anonymous 

Items that do note meet the specified will not be returned.  Using a filter query does eliminate the need for many conditions in your Flow and, as a result, simplifies the Flow.  

If you are checking for items where the Compliance Status is not equal to Complete, items with no compliance status meet that criteria and will be returned.

My concern is that you are close to the 5000 item limit.  If the list size grows, you will not have a choice other than to use filter queries.  Using a filter query will make the Flow run more quickly and avoid potential issues when/if the list grows above 5000.

Note that I am assuming that you do not have a premium Flow license.  If you are using the Flow license that comes with M365, the 5000 limit applies.  If you have an upgraded license, it does not.