Unanswered

Delegation issue with calculated SharePoint list column using filter function

(0) Share

Report

Posted on by 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?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like (0)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like (0)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

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

Was this reply helpful? Yes No
WhatisPowerApps 96 on at

Like (1)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@WhatisPowerApps

Sounds good. Post back if you run into issues.

Was this reply helpful? Yes No