search multiple fields at once 1

[JanisKedanis]

Is there a way to locate a character string across multiple fields with one command? I think I can do something like:
locate for "find this " $ field1 .or. "find this" $ field2 BUT that would be VERY VERY slow! Is there a better way to do this?

I read somewhere once something about a % function, but I can't quite remember what!

Thanks for the help

 

[zimbaliza]

We do this with PhDbase (3rd Party index)
Full Text Index/Seacrch Technology for Foxpro

it creates it's own index, index is very fast. (altho is kinda slow to create) (well on a 386 anything is slow)

PhDbase III
Korenthal Associates (search for it on the web should be able to find it)

it cost was about $299.95us or so

have it working at 40 sites with about 500+ users, using
Foxpro 2.5 for Dos (point of sale system) as well as in head
office applications

Zim

 

[DSummZZZ]

% is modulo operator, not a substring search like $. If you don't want to buy a third party product, you are going to have to code something like your example. Or code something using the AFIELDS() function. The other option is to BROWSE the table and do a search with CTRL+F. This will find something in any displayed field. (not Memo or General fields though.)

Dave S.

 

[infinitelo]

you could create a dbf with all the "words" in all the fields and include the record number in the dbf, index on phonic spelling then you could seek your work and produce a list of record numbers that have that word or something that sounds like it.


given enough disk space anything is possible.

 

[jschenke]

Hi,

If you're only searching a predefined set of fields and have enough disk space, what you could do is
INDEX theTable ON field1+field2 TO large.idx

And then
LOCATE FOR "find this" $ field1+field2

Our applications use these sort of indices all the time, with excellent results and not too much of a performance penalty.

Just a thought,

Jan Schenkel.

"As we grow older, we grow both wiser and more foolish at the same time." (De Rochefoucald)

 

[Scott24x7]

Jan,
Clever solution....

Best Regards,
Scott

Please let me know if this has helped

 

[JRB-Bldr]

If you don't already have enough possible solutions.....

If in your argument:
"find this " $ field1 .or. "find this" $ field2the "find this" is the same for both fields then Jan's solution above would work very well.

However if you are looking for different values in each field you might want to consider using a SELECT SQL to create a temporary data table whose records met your search requirements.

SELECT * ,;
FROM dbfname ,;
WHERE "find this1" $ field1 ,;
OR "find this2" $ field2 ,;
INTO CURSOR result.
SELECT result
IF RECCOUNT() > 0
do whatever
ELSE
WAIT WINDOW "Nothing Found!"
ENDIF
Obviously, the speed of this approach would depend on the size of the original data table to be searched.

Good Luck,
jrbbldr
jrbbldr@yahoo.com

 

[JanisKedanis]

Thanks for the tips. A question: For Jan's solution - can you use a CDX in this manner or is it an index that needs to be re-created with each search?

If it needs to be re-created, that might be a bit of a time user (there are 4 fields to search, 3 are 50 charachters and 2 are 25 in length) plus there are about 300,00 records in the database

I wonder if the SQL might work faster as it is optimizable?

 

[jschenke]

Hi,

If you want you can include this formula in the CDX of your datafile, though this will increase the size quite a bit -- of course you can always make the multi-field-search index an ADDITIVE one so it doesn't interfere with the CDX but gets updated accordingly as the data changes.

The main reason why i'd not use the SQL, is that it's far easier to add more fields to the index you're searching for.
If you
STORE 'field1+field2' TO idx_expr
Then you can
INDEX ON &idx_expr TO large.idx
And subsequently
LOCATE FOR "find this" $ &idx

Which has some very interesting features

Best regards,

Jan Schenkel.

 

[Jerry4DOS]

In FPD, beware of temporarily adding tags to a CDX, then deleting the tag -- the size of the CDX file doesn't return to its previous size. (Maybe FPW also?)

Jerry