Delegation issue with calculated SharePoint list column using filter function

[WhatisPowerApps]

Hi,

Sort(
	If(
		DropdownExpiredECD.SelectedText.Value = "Yes",
		Filter('EWT_Elist',  ExpiredECD = DropdownExpiredECD.SelectedText.Value)
	)
	, ID
	, Descending
)

Why am I getting delegation warning in Gallery items? It works if I use other columns to filter. ExpiredECD column is returning as data type. That column is calculated field in SP list to return Yes /No based on today's date and another date column.

I don't understand why it won't work. Can someone explain to me or give me advice for a workaround?

[RandyHayes]

@WhatisPowerApps 

Calculated columns are not delegable.  You will definitely get the issue you are seeing.

If you cannot derive or filter from the other columns without a delegation issue, then you can consider looking to Power Automate to automatically fill in the value of a text column (not calculated) in your list with the value you want.  Power Automate can be set to do that on creation of a record or change of a record.  

With that value in place, you can then perform the filter without delegation warnings.

I hope this is helpful for you (even though it is not what you wanted to hear).

[WhatisPowerApps]

@RandyHayes 

I see. I didn't know calculated columns are not delegable. The reason I went that route is I need to compare due date column and today's date. I'm trying to filter it's the item is past due date or not. What would be the best way to solve this?

Can you explain a little more on Power Automate? 

[RandyHayes]

@WhatisPowerApps 

There's been a good push to get more things delegable in the connector.  I believe the only calculated columns that are delegable are text columns...but I have not verified that.  They were never delegable in the past.  You can experiment with this by using a text calculated column and see if you have the same results.

Also, you mention that you expect the field to dynamically change based on todays date.  You do realize that those calculations are not dynamic?  In other words if the calculation of the field today has a "yes" in it and you expect that tomorrow it would have a "no" - it will not, because the value of "yes" was calculated and stored at the time it was created.  Unless you've got something else going that would change this dynamic...

AND - I believe the above is a key factor in your design - you are expecting that the SharePoint list will have the ExpiredECD dynamically changing.  Which really excludes the use of Power Automate as a solution for doing the calculation as it will again be stored in the record as it is at that point in time - and is not updated.

What I might suggest is (and I believe you are trying to determine expirations?) to calculate the expiration date at the time the item is created. Then store that information in the item and use that for your filtering.   You'll still need to work with a way to store that as a column that is delegable, but at least it will be something to start from.

Let me know if I'm hitting the nail or banging the board.

[WhatisPowerApps]

@RandyHayes 

You are right my today date column is not dynamic on SP list. haha I'm not sure what I was thinking. Yes, I'm trying to filter expired items in Gallary.

I have approval process flow with EndDate column. That column is editable later time in PowerApp if the user needs to extend the date. I need EndDate to compare with TodayDate using other than "=". Not sure how to make it work at this point. I'm still searching for the solution.

[RandyHayes]

@WhatisPowerApps 

So, the first question will be...is delegation an actual issue?  Are you dealing with more (or will be) than 2000 items?

If it is not, then I would just ignore the warnings.  If so, and it's the date that you need to work with, then there are methods.

Which method to use is going to be best determined by the number of items you will have over a given period.

[WhatisPowerApps]

Yes, it needs to be delegable. I already have 1000 items in 3 weeks. It will continue to grow. It's an inventory app with approval process flow. One page to check the item lists.

I'm thinking about using flow to update Yes/No on ExpireDate column to run daily. Even then, I might hit 5000 limit very quick on flow, probably need to look into this https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

Do you have other methods that better for me to look into?

[RandyHayes · Accepted solution]

@WhatisPowerApps 

Is SharePoint your only option?  Perhaps SQL?

Anyway, if SharePoint...

Consider that you will be dealing not only with delegation but also with record limit (two different things).

I would first ditch the running of flow daily to change a column.  I would still consider doing your expiration calculation on creation.  What value does an Expired column have?  If the expiration date (calculated when entered, or changed when edited) is Feb 1 2020 and today is Mar 6 2020, then we know it is expired.

So with the above in mind - if when know that the maximum expirations in a given period (let's say a month) will not exceed 2000, then we can use that.

Use flow, during item creation or edit, to create an Expiration Period value and place it in a numeric column.  The calculation will be a granular as the period you need.  So (again, using a month as our period), we would have Flow put the number 202002 in the ExpirationPeriod column and then 01 in an ExpirationDay column.

Now, we can do a delegable filter against the two numeric columns.  One for ExpirationPeriod>CurrentPeriod AND ExpirationDay>CurrentDay

This will give you the flexibility of avoiding delegation and also reducing record limit issues.

I would lean toward something more like this because even given your thought of having flow update a yes/no column, you are still going to be dealing with record limits.  

SharePoint gives us very little options for Server-Side processing of data (i.e. delegation) (unlike SQL, where we can employ a View to preprocess), so you have to get creative while knowing the boundaries that you will encounter (record limit and delegation).

I hope that is somewhat clear.

[WhatisPowerApps]

@RandyHayes 

I won't have more than 2000 items for the expired items at any given time. So, your solution seems like a very good idea 👍. I will try that. Thanks!

[RandyHayes]

@WhatisPowerApps 

Sounds good.  Post back if you run into issues.