i'm sure this is simple...

[christoff]

Hi, I know how to do a conditional formula based on whether a field equals an exact word or words by using if(field = "word" ; etc etc).

but how do you ask if you just want to pick out 1 word from a field

eg if(field [color:red]contains? "word" ; etc etc)

does that make sense?

[comment]

This can be simple, if you are looking for a string: either the PatternCount() or the Position() function can be used for this.

It is not so simple when the string you are looking for must be a word in the searched text. For this, you must either know in advance which punctuation marks can be found in the text, or use a custom recursive function to check the searched text, word-by-word.

[Delta Tango]

You can use the Filter function to test if it returns the word.

Check the manual on the filter function.

[comment]

You can use the Filter function to test if it returns the word.

No, I don't think you can.

[christoff]

If I do a find/search for the word "DOG" in a certain field in a number of records, it will bring up records where the field is "WHITE DOG", "BLACK DOG" "BIG DOG" etc

but if I do an IF or CASE formula based on the word "DOG" it won't recognise those fields with more than one word, like "WHITE DOG" etc

that's a bit strange isn't it?

[comment]

It's not strange at all. The default find in Filemaker is for 'words starting with ...'. Thus "dog" finds "dog food", "white dog" and also "doggie" and "dogma" - but not "bulldog".

[christoff]

Hi, what happens if you just want to look at the last word in a field of 2 or 3 words. eg look for the word "Dog" when dog is always the last word in a field. I tried using RightWords in an IF formula but it didn't work. Maybe I'm doing it wrong?

if((Field 1 = RightWords("Dog" ; 1) ; 1; 0)

I don't think PatternCount is what I need.

[christoff]

so can you achieve a similar thing with an IF formula?

[comment]

if((Field 1 = RightWords("Dog" ; 1) ; 1; 0)

The correct syntax for this would be =

RightWords ( Field 1 ; 1 ) = "Dog"

can you achieve a similar thing with an IF formula?

You can, but not easily - see the second part of my first post.

[David Jondreau]

There's some fundamental aspects to Filemaker you may not understand, which is or was true for everyone at some point.

Case() and If() produce results when evaluated in the calculation engine. If you're putting them directly into a find request, FM interprets that as trying to find those exact strings. If you're putting them in a scripted find that uses Set Field[], FM evaluates the expression, then finds the result.

if((Field 1 = RightWords("Dog" ; 1) ; 1; 0)

This expression means:

1) Determine the Rightmost word of the string "Dog".

2) If Field one is equal to that word, return a 1, otherwise, return a 0.

In Find mode, non-global fields do not have a value so evaluating this expression in Find Mode will always return 0.

If you find a set of records who last word is a certain string, you should define a calculation field = RightWords(field;1) and then search on that field.

Edited February 25, 2009 by Guest

[RodSierra]

Here's a way to do it with a calc field. Note that not all punctuation is set up in this example, you'll need to nest more substitute functions to add additional filter characters. (Sure wish the substitute function would allow multiple Strings or characters.) Not sure if this is what your looking for.

WordFilter.fp7.zip

[comment]

Sure wish the substitute function would allow multiple Strings or characters.

It does:

Substitute ( text ; [ "." ; " " ] ; [ "," ; " " ] ; ... )

[christoff]

you solved my problem! that really spins me out that I had the syntax wrong. Luckily usinf RightWords solves my problem coz the key word is always on the right. If it was occasionally left or in the middle, then I wouldn't know how to pick out the key word.

cheers

[RodSierra]

Thanks comment, when I tested the list in substitue, I put all the to subs in one list, instead of adding the sub to in each, learn something everyday!