How does this find work?

[lasse]

I have a questions regarding how this FM search works which I hope someone can help me clarify.

The FM file have a text field on the top of a layout page. The thing is the search only return exact matches to its input. This field is not available in Find mode.

The search is for a given title entry. Currently I have located it to this:

There is a (=) relationship from the textfieldname to a table called NameRelate.

NameRelate =

1 & "¶" &

Left(Title;1) & "¶" &

Left(Title;2) & "¶" &

Left(Title;3) & "¶" &

Left(Title;4) & "¶" &

Left(Title;5) & "¶" &

Left(Title;6) & "¶" &

Left(Title;7) & "¶" &

Left(Title;8) & "¶" &

Left(Title;9) & "¶" &

Left(Title;10) & "¶"

If you do a search with '1' you get all Titles.

But if you do a search for 'Book' as in 'My Book' you get nothing. Also if you enter a title longer than 10 char you get nothing, which is obvious.

Why would you ever have a search options like this?

How can you modify it to handle new criteria such as "Book"?

[Ender]

It looks like what you have here is a multi-key for a relationship. When used to match related records starting with the text you enter into the seach field on the parent side, this would show the matches. But your calc is indeed limited to 10 characters. This is what you would actually have in this multi-key field:

1

M

My

My

My B

My Bo

My Boo

My Book

My Book

My Book

My Book

When the parent key has a string that matches with any of those lines, the relationship matches, and the record is displayed in your portal.

If you had access to FM7 Developer or FM8 Advanced, there are some nifty custom functions that do a much better job of exploding keys, including the ability to explode each word separately, so you can get matches for "Book" or "Boo" too.

Without the Developer/Advanced version, your options are limited. You could try separating each word out so whole intermediate-word matches would be possible, but you'd be limited to a finite number of words (which the calc would need to explicitly parse out.) Something like:

...

MiddleWords (Title; 1;1) & ¶ &

MiddleWords (Title; 2;1) & ¶ &

MiddleWords (Title; 3;1) & ¶ &

...

To increase the length of the Title allowed for a partial match, simply add additional lines to your calc, like:

...

Left (Title; 11) & ¶ &

Left (Title; 12) & ¶ &

...

[Genx]

... what could you do if you had dev / adv?.. custom function?

cheers, genx

[Ender]

Use this to explode each word:

http://www.briandunning.com/cf/36

with this to explode the entire string:

http://clevelandconsulting.com/support/viewtopic.php?t=49

So the calc that does this would concatonate both:

key (calculation, text result) = ExplodedKey(field) & ¶ & ExplodedString(field)

And the result would look like:

M

My

My

My B

My Bo

My Boo

My Book

M

My

B

Bo

Boo

Book

[lasse]

Thank you, here is what I did:

I used the functions from:

http://www.briandunning.com/filemaker-custom-functions/list.php

ExplodedKey(text) = 

if(Length(text)>1;

ExplodedKey(

Left(text; Length(text)-1)

)& ¶ & text;

text

)



ExplodedString(text) =

ExplodedKey(text) & ¶ & 

if( WordCount(text) <> 1;

ExplodedString(RightWords(text;WordCount(text)-1));

""

)

I then changed the calculation to:

NameRelate = 1 & ¶ & ExplodedString(Title)

Now a given search for a word like 'Book' will find all the relevant titles containing this word.

A little simpler but also makes the Indexes a little slower to update and the file bigger! So be aware!

After the update is made, will it have any other consequences besides the extrastorage for the long title names being exploded?!?

Will it effect the find relationship time for many titles?

Find other similar functions here:

http://www.briandunning.com/filemaker-custom-functions/list.php

Edited February 15, 2006 by Guest

[Ender]

Hey lasse,

Don't take too much credit for having "Created" the two custom functions that others have figured out, and I pointed you to.

About your calc: you will have problems matching on more than one word unless you use the format I showed, adding ExplodedKey() to the calc. For example, "My Book" would not match from the calc you came up with.

As to performance: yes, the size of the index is increased as well as the file size, but performance shouldn't be hurt unless you're trying to do this in a huge file (hundreds of thousands of records,) or the field you're exploding is very long (lots of big words.)

[lasse]

Sorry about that. That wasn't my attentions.

I don't understand what you are saying.

I seems to work for me.

The idea is to use a recursive call from explodedString to explodedKey with the RightWord so you iterate through the title and strip out the first word and do the same again like:

"My Book"

1

M

My

My

My B

My Bo

My Boo

My Book

B

Bo

Boo

Book

This way you will cover all cases, like "My Great Book" and get "Great Book" included as well as "Book" etc etc..

Thanks!

Edited February 15, 2006 by Guest

[Ender]

Oh, I see what you did. You wrote a different ExplodedString() function.

You should note the differences between them, as your version may result in unnecessarily large keys. I'll give you an example, and you can decide which is better:

Suppose you want to explode "My Big Book of Fun", your version results in this key:

M

My

My

My B

My Bi

My Big

My Big

My Big B

My Big Bo

My Big Boo

My Big Book

My Big Book

My Big Book o

My Big Book of

My Big Book of

My Big Book of F

My Big Book of Fu

My Big Book of Fun

B

Bi

Big

Big

Big B

Big Bo

Big Boo

Big Book

Big Book

Big Book o

Big Book of

Big Book of

Big Book of F

Big Book of Fu

Big Book of Fun

B

Bo

Boo

Book

Book

Book o

Book of

Book of

Book of F

Book of Fu

Book of Fun

o

of

of

of F

of Fu

of Fun

F

Fu

Fun

And the original ExplodedString() CF with an ExplodedKey() tacked on in the calc results in this:

M

My

B

Bi

Big

B

Bo

Boo

Book

o

of

F

Fu

Fun

M

My

My

My B

My Bi

My Big

My Big

My Big B

My Big Bo

My Big Boo

My Big Book

My Big Book

My Big Book o

My Big Book of

My Big Book of

My Big Book of F

My Big Book of Fu

My Big Book of Fun

If you want to allow partial matches of more than one word, starting from the second or third word, like "Big Book," your CF will be necessary. If that's not likely to come up, then you might try to make it a little more efficient.

[Ender]

One recommendation about making those keys more efficient: You'll notice that one, two, and three letter lines are a big chunk of those keys, yet for a user searching for something, entering one, two, or three letter search terms will likely result in way too many matches to be of any use. My suggestion is to hobble the ExplodedKey() CF so that the one, two, and possibly the three-letter lines are skipped (unless they are the only letters in the word.) This would trim that multi-key a bit and improve the searchability of those keys.