The calculation can use PatternCount to find the 6. But be careful if the real data could have 55, 66, 77, as a simple PatternCount would also match 6 to that.
Hi Kostas Teleytaios,
It would help to understand what you are trying to accomplish. And please describe real situation and not abstract example. :^)
Sorbsbuster, thanks for the anwser. I will test it in a while!
I have a table with the field "Range". It contains multiple values seperated by carriage return.
Then I have a number, lets say 250, taken by the filed "Test number".
In annother field, a calulation field named "Result", the value must be "1" if the number 250 exists in the field "Range" or "0" if it doesn't.
Hope I helped you. I'm waiting for your suggestion.
This is a text field, presumably (or else you couldn't have return characters in it.)
You could set up a self-relationship, using the Test Number = Range. The calculation would be:
RelationshipByTestNumber::Range > 0 ; 1
Note that I am assuming there is an exact match for the Test Number in the Range field. (So there is no match if Test Number is 210 and the range field has the values 195, 205, 215, 225.)
Or, of course, with no relationship:
Case ( Filtervalues ( Range ; Test Number ) > 0 ; 1 ; 0 )
There may be better ways to store your list of values--such as a table of related records, but working with what you've given:
Not IsEmpty ( FilterValues ( Range ; TestNumber ) )
Will be true if the value in TestNumber is also a value in the list of values in Range.
Sorbuster, thank you.
The database I'm working with is full of relationships and now I am trying to reduce them. So the solution with the relationship allthough is great, does not fit to the solution I'm trying to build up.
The second solution of yours is very interesting. I will try it in a while and come back to tell.
Kostas from Greece.
Create calculation (result is number):
FilterValues ( Range ; testNumber ) ... and search for *.
Or: not not FilterValues ( Range ; testNumber ) ... and search for 1.
OR: GetAsBoolean ( FilterValues ( Range ; testNumber ) ) ... and search for 1.
Can you describe more about that Range text field? Why are the numbers put in the same field instead of related records? What is its purpose?
Guys I wish to thank you all for your interest and the provided solutions. You are amazing!
Sorbsbuster's first answer and the solution he provided works perfect for my database.
Thank you Sorbsbuster. And once again thank you all.