Unanswered

Comparing single line of text to string in filter

(0) Share

Report

Posted on by donbrignell

Help! I can't get what seems to be a very simple filter to work in a delegable fashion. My filter is below. This works, but requires the use of a text() function to get a value to compare from a SharePoint list. This isn't delegable and the list is going to be large eventually so I do need it to be. I've searched for this problem on the forums but everything I can see makes it look like this ought to work

The 'Breathe ID' field is a "single line of text" field in SharePoint. 111111 is a string in this example.

If I remove the text() function the formula becomes delegable but the filter doesn't return anything. There is no whitespace etc in the field entries.

SortByColumns(
Filter(
'All CPD',
Text('Breathe ID') = "1111111", 'Start on' >= FromDate && 'Start on' <= ToDate
),
"Name",
Descending
)

Please tell me what I am doing wrong? Many thanks for your time..!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Akser 1,546 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @donbrignell

Where does the filter value ("1111111") come from? Variable, Control? Because you can do the conversion there (i.e. make it a Text()) and then use it in your filter (without the need to do 'Text()' there).

Note: Upon reading your question once again, I got confused. You say that

The 'Breathe ID' field is a "single line of text" field in SharePoint.

But then you do this

Filter( 'All CPD', Text('Breathe ID') = "1111111",

Why would you convert a text to a text? Does the following return nothing? (Assuming you have a record in your data source where 'Breathe ID' is a single line text column with a value of '1111111'?
Filter( 'All CPD', 'Breathe ID' = "1111111" )

Was this reply helpful? Yes No
donbrignell 4 on at

Like
a
(0)

Report
Copy link

Link copied!

So "1111111" is an example. It is actually a string I have parameterised in the On Start function called strBreatheID.

This is definitely already a string (or "text").

Regarding your second point - this is my issue really. I don't think I should need to convert to text(), because it is a single line of text at source. There is no type mismatch. But if I don't include this function, I don't get any results from the filter. When I click on the sharepoint field name in the function, it says: "'Breathe ID' = [Control] | Data type: Control"

When I click on strBreatheID (or "111111" in the example I posted) it says "111111" = 111111 | Data type: Text.

But if I convert the 'Breathe ID' to text by using Text('Breathe ID') it works perfectly, it just isn't delegable.

Thanks - appreciate your help.

Was this reply helpful? Yes No
donbrignell 4 on at

Like
a
(0)

Report
Copy link

Link copied!

And sorry - no, your suggested formula doesn't work, see below:

See below for the working, non-delegable version:

Was this reply helpful? Yes No
franzdalvit 2 on at

Like
a
(0)

Report
Copy link

Link copied!

I have the exact same issue. String comparison doesn't work on a single line of text, Sharepoint List column. I must use the PlainText function to get the matching results, but unfotunately it is not delegable. Please let me know if you find a solution.

Was this reply helpful? Yes No