Size matters

[George TOUBALIS]

First sorry for my bad english

I think I need A very strong calculation here

Here is the problem.

I made a self relationship in a table with artists names.

The 1st Key "some_characters" is a text field.

The 2nd Key "Calc_name" is a calculation field with this classic code:

"All" & "¶" & "¶" & Full Name & "¶" &

Left(LeftWords(Full Name;1); 1) & "¶" &

Left(LeftWords(Full Name;1); 2) & "¶" &

Left(LeftWords(Full Name;1); 3) & "¶" &

Left(LeftWords(Full Name;1); 4) & "¶" &

Left(LeftWords(Full Name;1); 5) & "¶" &

Left(LeftWords(Full Name;1); 6) & "¶" &

Left(LeftWords(Full Name;1); 7) & "¶" &

Left(LeftWords(Full Name;1); 8) & "¶" &

Left(LeftWords(Full Name;1); 9) & "¶" &

Left(MiddleWords(Full Name;2;1); 1) & "¶" &

Left(MiddleWords(Full Name;2;1); 2) & "¶" &

Left(MiddleWords(Full Name;2;1); 3) & "¶" &

Left(MiddleWords(Full Name;2;1); 4) & "¶" &

Left(MiddleWords(Full Name;2;1); 5) & "¶" &

Left(MiddleWords(Full Name;2;1); 6) & "¶" &

Left(MiddleWords(Full Name;2;1); 7) & "¶" &

Left(MiddleWords(Full Name;2;1); 8) & "¶" &

Left(MiddleWords(Full Name;2;1); 9)

The Resault in "Calc_name" is

All

Bob Dylan

B

Bo

Bob

Bob

Bob

Bob

Bob

Bob

Bob

D

Dy

Dyl

Dyla

Dylan

Dylan

Dylan

Dylan

Dylan

So.... when I want to find (lets say) Bob Dylan I type "Bo" or " Dy" and the portal show me some artists which one of them is Bob Dylan

Now the problem is that I have 175000 records of Music Artist Names And the repetition of this calculation is very big.

can we have a calculation Resault Like this in any name

All

Bob Dylan

B

Bo

Bob

D

Dy

Dyl

Dyla

Dylan

It's very difficult for me...

George

[Reed]

The easiest way to do this if you have Dev7 is to use a recursive custom function:

ExplodedKey(txt)

If(

   Length(txt)>1;

   ExplodedKey(Left(txt;Length(txt)-1)) & "¶" & txt;

   txt

   )

The function just calls itself and adds one more line with one less character on each iteration.

There are lots of these custom functions in the forum here and at www.clevelandconsulting.com and www.briandunning.com.

Of course this function will not break apart the first and last names in a single field like you specify, but it's probably better to have first and last names in separate fields anyway, and then concatenate the results for each field.

Also, if you're using this field as a complex key for portal filtering, you might consider using ranged relationships instead. Check out the example at www.databasepros.com

Dana

[George TOUBALIS]

Sorry but ....

[Søren Dyhr]

Replace Name with "ExplodedKey"

and write txt into the parameter cell off it, before entering the recursive function. It's a Hen & Egg issue...

--sd

[RalphL]

This is not required in FMP7 see John Mark Osborne's Type Ahead demo on his site http://www.databasepros.com

[George TOUBALIS]

It works

!!!!!

[Ender]

True, that Type Ahead method (using the new range relationship operators) has its uses. But you should be aware of the limitations. In order for it to work correctly, each field must be compared separately (Last Name, First Name, etc.) This may be fine, but in some cases you may want the users to just have one search field (Name,) and have it match on either (First Name OR Last Name.) The multikey approach works well for this.

Taking a look at some of the CF options a little more closely, let's see what we get:

Using the ExplodedKey() CF, we would get a multikey that looks like this:

B

Bo

Bob

Bob

Bob D

Bob Dy

Bob Dyl

Bob Dyla

Bob Dylan

This probably won't work well for a search field where the user might just enter a partial last name.

On the other extreme is something like this AllTextCombinations() CF. The key produced from that would allow partial searches even within a word, but would probably include too many things to be practical, and would slow things down because of its size.

Another option is to do an exploded key for each word in the string, like with this ExplodedString() CF. I would also append an ExplodedKey result from the entire string so that the whole string is exploded too. This would accomodate some of the most common partial matches users might try. This would be the resulting key:

B

Bo

Bob

Bob

D

Dy

Dyl

Dyla

Dylan

B

Bo

Bob

Bob

Bob D

Bob Dy

Bob Dyl

Bob Dyla

Bob Dylan

There's probably a way to make that a little more efficient, so that the exploded key on that first word is skipped.

Anyway, hopefully this helps you see some of the options.