Comparing single line of text to string in filter

[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..!

[Akser]

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"
)

[donbrignell]

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.

[donbrignell]

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

See below for the working, non-delegable version: