9 Replies Latest reply on Sep 12, 2011 10:15 AM by theta

# Search in one field with multiple values.

### Title

Search in one field with multiple values.

### Post

There is a serious problem I must solve.

We have a field with values as the following example:

Field contents:

5 [carriage return]

6 [carriage return]

Within another field (calclution field) I must search if, let's say, the value "6" is inside the range of the previous field [5, 6 and 7].

Is there any solution?

• ###### 1. Re: Search in one field with multiple values.

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.

• ###### 2. Re: Search in one field with multiple values.

Hi Kostas Teleytaios,

It would help to understand what you are trying to accomplish.  And please describe real situation and not abstract example. :^)

• ###### 3. Re: Search in one field with multiple values.

Sorbsbuster, thanks for the anwser. I will test it in a while!

LaRetta:

I have a table with the field "Range". It contains multiple values seperated by carriage return.

For example:

125

126

136

250

689

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.

• ###### 4. Re: Search in one field with multiple values.

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:

Case (

RelationshipByTestNumber::Range > 0 ; 1

; 0

)

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

• ###### 5. Re: Search in one field with multiple values.

Or, of course, with no relationship:

Case ( Filtervalues ( Range ; Test Number ) > 0 ; 1 ; 0 )

• ###### 6. Re: Search in one field with multiple values.

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.

• ###### 7. Re: Search in one field with multiple values.

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.

Thanks!

Kostas from Greece.

• ###### 8. Re: Search in one field with multiple values.

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?

• ###### 9. Re: Search in one field with multiple values.

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.

Kostas.

:)