August 16, 200520 yr 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
August 16, 200520 yr 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
August 16, 200520 yr 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
August 16, 200520 yr This is not required in FMP7 see John Mark Osborne's Type Ahead demo on his site http://www.databasepros.com
August 16, 200520 yr 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.
Create an account or sign in to comment