LIKE operator functionality in calculation?

[Andrea S]

Hi,

I'm having a tough time tracking down FM functionality that works like a LIKE statement in a calculation.

Specifically, I have two fields fieldA and fieldB. FieldA has a list of values associated with it, and the user can choose multiple values. I want fieldB to be populated with specific values based on whether the user selected single or multiple values for fieldA as follows:

If fieldA equals "valueX" then fieldB = "result1"

If fieldA equals "valueY" then fieldB = "result2"

If fieldA contains "valueX" and "valueY" then fieldB = "result3"

Of course, setting fieldB based on single values in fieldA is easy. So no worries there.

What I'm trying to do is find something that works as a LIKE operator where I can do something like this:

If fieldA LIKE "%valueA%" AND fieldA LIKE "%valueB%" then "result3"

From what I've been able to track down, it looks like the "*" operator would work if I'm doing a search, but it's not working when I try to use it as part of a CASE statement.

Any assistance anyone case provide is appreciated.

Thanks!!

[IdealData]

You cannot use wildcards in comparison operators.

Have a look at the TEXT functions, particularly

GetValue

PatternCount

[Andrea S]

Ah-ha.

PatternCount worked like a charm once I used that and nested it into my CASE statement.

Thanks so much for your speedy assistance!

[Søren Dyhr]

If fieldA equals "valueX" then fieldB = "result1"

If fieldA equals "valueY" then fieldB = "result2"

If fieldA contains "valueX" and "valueY" then fieldB = "result3"

This is how I would structure the detection of 3 values, so I kind of dissagree with the use of a Case( structure:

Choose (

4*Sign(PatternCount ( userEntry ; "ValueX" )) +

2*Sign(PatternCount ( userEntry ; "ValueY" )) +

Sign(PatternCount ( userEntry ; "ValueZ" )) ;

"result0"; "result1" ; "result3" ; "result4"; "result5" ; "result6";"result7" )

...what might slightly disguised here is the base conversion from binary to base10 - the reason for this is that the nesting otherwise becomes unbearingly deep, if Case( where chosen.

So if even more words were to be detected, would the numbers

256-128-64-32-16-8-4-2-1

...be utilized for getting the combinations of 9 values.

--sd

[Andrea S]

Hi Soren, and thanks for your reply.

I just looked up the CHOOSE() function and see that it tests integer values.

Reading over my original post, I should have clarified that my value in fieldA is text, not integer value. The value in fieldB is dependent on a couple specific words existing in fieldA.

Here is basically the CASE statement that worked for me:

Case (

(PatternCount(Status ; "word1")=1) and (PatternCount(Status; "word2")=1); "HC";

(PatternCount(Status ; "word1")=1); "H";

(PatternCount(Status ; "word2")=1); "C"

)

I will definitely keep your solution for the CHOOSE() function in mind, as this may come in handy during future develpment.

Thanks again,

[Søren Dyhr]

Case (

(PatternCount( Status ; "word1")=1) and (PatternCount(Status; "word2")=1); "HC";

(PatternCount( Status ; "word1")=1); "H";

(PatternCount( Status ; "word2")=1); "C"

)

This can be simplified:

Left("H";PatternCount( Status ; "word1")) & Left("C";PatternCount( Status ; "word2"))

I should have clarified that my value in fieldA is text, not integer value.

I did actually treat it as a text string, Sign( as well as Patterncount( returns integers. The way PatternCount( works on a number is turning it string (typecasting )and then plucking if posible.

Let me give you an example, where it seems that the quotes have gone wrong, although its deliberate:

PatternCount(4 / 9;"4")

4 divided by 9 gives 0,4444444444444444 so the result is 16!

--sd

Edited January 14, 2007 by Guest

[comment]

There seems to be a lot of confusion here:

First, when a field is populated through a value list, it cannot contain duplicate values. Therefore, PatternCount ( field ; value ) can only return 0 or 1 - and it's not necessary to force it into a Boolean by using Sign(), or anything else.

Next, as Søren says, the Choose() function here operates on the RESULTS of the PatternCount() tests. These results are numeric, regardless of the type of the tested field. The expression:

2 * PatternCount ( Status ; "word1" ) + PatternCount ( Status ; "word2" )

can only return 4 possible results: 0, 1, 2 or 3 and thus very well suited to serve as the test parameter in Choose().

Finally, beware of false positives when using PatternCount() with lists of values. For example:

PatternCount ( "Atomic" ; "Tom" )

returns true.

[Søren Dyhr]

it's not necessary to force it into a Boolean by using Sign(), or anything else

But where in the text is it said that:

a list of values associated with it

dependent on a couple specific words existing in fieldA

is the same as a valuelist?? You could be right but I avoided taking any chances here.

--sd

[comment]

FieldA has a list of values associated with it, and the user can choose multiple values.

It sounds like a checkbox to me, but of course I could be wrong.