Build Index For Text Field (Keywords)

[Microquest]

I have 125,000 records with a keyword field (up to 50 words each). How do I automate the process of building a "keyword" database and "join" database to create an efficient index for the keyword field?

Thanks for any input.

[Vaughan]

Could you explain what you mean by "an efficient index" for the field. Do you mean an index like in a book?

Also describe how the words are separated in the keyword field (spaces, commas, etc).

[Microquest]

Thanks for your response.

The words are separated by spaces. They are a composite of other fields in each record.

INDEX: I want to create a KEYWORD field that a user can search on and find records containing the chosen word.

I believe I need to export each of the words in the composite field to another database and link it with a third database.

I'm a bit slow on figuring out how to do this....

Thanks for any insight you might provide.

Mark Ross

Microquest

Novato, California

[Vaughan]

What's wrong with simply performing a find in the Keywords field? No need to export/import into another database or anything.

Or am I missing something?

[Microquest]

It takes too long to execute. Twenty minutes or more.

Mark Ross

Microquest

Novato, California

[Andy Robo]

Hi Mark

FM 6 shipped with a relational database file as part of it's templates - you may want to take a look at it

In the new database window choose a Business template > I think it's the product catalog template - anyway this creates a two file database with a keywords file as a secondary related file...

the entry of keywords into the keyword DB creates new keyword entries in the primary DB, however I'm sure you could automate this using scripts - and also get it to work the other way around (eg. entering keywords in the primary file creates new records in the keyword file)... this is what I did to speed up data entry... anyway the result is a nifty wee file which aids in keyword searching - I'm sure it's not exactly what you want but it's construction might give you some further ideas...

Andy

[Microquest]

Thanks Andy.

I'll take a look at that template.

Mark

[Vaughan]

"It takes too long to execute. Twenty minutes or more."

What? The keyword field must be unstored, or else it has its indexing turned off. Turn indexing on and a find should only take seconds to complete.

[Microquest]

The keyword field is a calculated result. The keyword field for each record contains multiple words. The field cannot be indexed....

In the options Tab, the stored option is "grayed out" -- not available for this field.

[Vaughan]

This is your problem then. If the field was indexed the searches would be almost instantaneous.

Making a keyword database and "joining" it to the main file probably won't speed things up because related fields cannot be indexed either.

We've got to try to make your keyword field indexed. Does the calculation reference related or global fields?

[Microquest]

Vaughan,

Yes! it referenced related fields.....

I took your direction, and brought the data in through look-ups and rebuilt the keyword field..

Then, I could index it!

Even with 125,000 records, and five combined fields, with an average of 50 words per record.... It is almost instantaneous!

I really appreciate your patience, time and expertise.

Sincerely,

Mark Ross

Microquest

Novato, CA

[Oldfogey]

You can make the field Keyword1 indexable - sort of - by defining another field KeyWord2 as Auto-Enter calculation Keyword1. KeyWord2 is then indexable. You might need to do a relookup on Keyword1 to fill existing records. Make sure to flag "Do not calculate if .....empty."