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.
Text('Breathe ID') = "1111111", 'Start on' >= FromDate && 'Start on' <= ToDate
Please tell me what I am doing wrong? Many thanks for your time..!
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" )
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.
And sorry - no, your suggested formula doesn't work, see below:
See below for the working, non-delegable version: