Define a calculation field, cWordList, as:
Substitute ( KeyWords ; " " ; ¶ )
Add a serial number field to this table, PrimaryKey, so that each record is uniquely identified.
In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
YourTable::PrimaryKey = YourTable 2::PrimaryKey
Go to Manage | value lists and define a value list that lists values from YourTable 2::cWordList. Select "Include only related values, starting from YourTable". This now produces a value list of all keywords in the field with all duplicates removed. The relationship limits the list to only the current record so each record will have its own list.
Now this calculation:
valueListItems ( Get ( Filename ) ; "YourvaluelistnameInQuotes" )
Will return the list of keywords with duplicates removed.
Set Field [YourTable::Keywords ; valueListItems ( Get ( Filename ) ; "YourvaluelistnameInQuotes" ) ]
will thus filter out the duplicate words.
Use this version:
Set Field [YourTable::Keywords ; Substitue ( valueListItems ( Get ( Filename ) ; "YourvaluelistnameInQuotes" ) ; ¶ ; " " ) ]
to keep the list as a list of words separated by spaces instead of returns. (The words will also be rearranged into ascending alphabetical order.)
Thanks again PhilMod,
Works Like a charm. I've been learning a ton over the last few months as I was headed in the right direction with this, but didn'd quite have the finishing touches. I got all the way up to the valueListitem calculation and got stuck, before I looked here to see if there was a reply.
I am trying to do the same thing, but I don't appear to have the Set Field option in my Specify Calculation dialogue window. I am working with Filemaker Pro 10 basic version. First, does my version not have this feature? and second, is there another way I can achieve the same goal?