Unanswered

Calculated Column returns error when using Filter () fuction

(0) Share

Report

Posted on by Shaun_Hendricks

I'm working with a SharePoint List. I have a Calculated column that returns an integer based on a DateDiff calculation. The Column works perfectly in SharePoint. When I reference the list in PowerApp with a Filter, Filtering on the column, I get an error "Invalid argument type. Expecting Number Value". I've tried using the Value() statement to force the number into the filter but that didn't work either. Here's the filter:
Filter( Table, 5 <= ColumnName )
Messed with spacing, with order of comparison "ColumnName >= 5" etc. No difference. Is it because it's a Calculated Column?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Shaun_Hendricks 21 on at

Like (0)

Report

Over 20 views and nada eh? I had hoped that maybe the forums might work better than the comments section of the documentation area on Filters but looks like about the same result. Perhaps PowerApps is yet another product Microsoft launched that they're just going to abandon since a product is only as good as its support. Add it to the pile I guess.

Was this reply helpful? Yes No
v-micsh-msft on at

Like (0)

Report

Hi @Shaun_Hendricks,

Would you please share a screenshot for the calculated column formula used under SharePoint?

My testing as below:

Column formula:

DateDif([Date1], [Date2],"D")

Then within PowerApps, I use the following formula to perform the query:

Filter(Training, Value(DateCal)<=25)

This would show the results, but with a blue reminding, which is the delegation issue:

Delegable data sources

Currently only "=" is supported for delegation, for the other known issues when working with SharePoint list in PowerApps, see:

Connect from Microsoft PowerApps to SharePoint

PowerApps would be able to read data from calculated column, but it seems the field type is not clearly identified.

Using value() function would make the formula work from my side.

Regards,

Michael

Was this reply helpful? Yes No
Shaun_Hendricks 21 on at

Like (0)

Report

Thank you for responding. Here's the column formula:

=DATEDIF([Manufacture Date],TODAY(),"Y")

It just returns the number of years a given piece of equipment is in service. I'm trying to filter based on anything that is over 5 years old with the PowerApp.

Was this reply helpful? Yes No
Shaun_Hendricks 21 on at

Like (0)

Report

Here's the actual error code when I hover over the "ExpirationCounter" field which is the Column Name in Sharepoint.

Was this reply helpful? Yes No
v-micsh-msft on at

Like (0)

Report
Hi @Shaun_Hendricks,

Please switch your formula in the following way:

Filter(Turnouts 5<=value(ExpirationCounter),Status<>"End of Life")

Regards,

Michael

Was this reply helpful? Yes No
Verified answer

Shaun_Hendricks 21 on at

Like (3)

Report

Nope, the forumla change is not correct, by sheer luck (and sleuthing of the pop up notifications) I stumbled across the answer. THIS is the correct formula:

Filter(Turnouts, (Value(ExpirationCounter)) >= 5, (Status.Value) <> "End of Life")

You have to have the comma after the List (or lookup item name) because it's required by the syntax of the Filter command. The paranthesis around the Value function is required because it must be evaluated FIRST before it can do the math that follows, so it represents a single number to evaluate. Thus it looks up the column, then generates the Value, then compares the statement. The comma means another logical test follows. Now, here's where I stumbled upon the solution that let me fix the first statement. I couldn't even get the Status column to evaluate correctly but for giggles I put in some more normal dotted code and POOF! all my indicators went away after I put it in paranthesis. I did that on the first statement and it works great.

So, lesson is, try about everything and don't trust Microsoft documentation because it falls a bit short on the stuff you can actually use in a given situation. The prompting could be a bit better but what was there did help me sleuth it out. Far from Elementary dear Watson!

Was this reply helpful? Yes No