fetch_find()

[martinwcole]

where i have an index that includes two fields, how do i use fetch_find

index is on last_name and first_name

i usually say something like:

tbl=table.current()
tbl.index_primary_put("sales office")
rec=tbl.fetch_find("athens")
if rec .........

but in this case tbl.index_primary_put("bmain") ''two fields
if the first name is john and last name is smith
have tried
tbl.fetch_find("smithjohn")
since the index would logically be smithjohn
but it hasn't worked

[Doug Chanley]

RE: fetch_find()


Hi Martin,

If you look a few messages down "search failed" or somthing close to that,
you can read some discussion on the topic of searching on an index using
more than 1 field.

[Finian Lennon]

RE: fetch_find()

This is really easy to look at in the interactive window and well explained in the Xbasic docs.

If you have a table called names.dbf with fields lastname (c, 20) and first name (c, 10) and an index called last_first which is defined as
trim(last)+space(1)+trim(last)
and you have a record with values John (first name) and Doe (last name) which, for the sake of argument, is the only Doe in the table.

Look at the record in a browse or form and note its record number. Again, for the sake of argument, say the record number is 1234. In the interactive window

t=table.open("names")
t.index_primary_put("last_first")
rec=t.fetch_find("Doe")
?rec
-1234
rec=t.fetch_find("doe john")
?rec
1234

In the first case it returned a -1234 indicating that an exact match was NOT found, even though the correct record was located. In the second it returned a positive 1234 because an exactly matching key existed in the index. If the index as defined as last+first, the only way an exact match could be found would be if the search string was
rec=t.fetch_find("doe.................John......")
where the dots are really blanks.

I would only use fetch_find on name fields to locate a record(s) for presentation to the user, never as a prelude to further manipulation. OTOH, fetch_find on unique key indexes like ID fields is a great way to process records in a script.

Finian

[Finian Lennon]

RE: fetch_find()

Oops, the index should be
trim(last)+space(1)+trim(first)

Finian

[martinwcole]

RE: fetch_find()

thank's ever so much

had not yet used an expression to define an index = thank's

up until very recently all my customers had small files, may 700 to 3500 parents and maybe 200 to 5000 children

i had a lot of indexes and many sets

a new customer who is seriously looking at my hearing aid dealer program has maybe 30,000 parents and maybe 60,000 children each in three child files

after creating a test database with that number of records, it was immediately obvious that i am carrying toooo much bagage - so i'm learning not to be so lazy - and am learning how to create an infrequently used set or index when needed instead of keeping it all the time (which is why i had to learn to search on multi-field indexes.)

big files are really quite different from smaller ons!!!!!!!!

[Jim Rowlen]

RE: fetch_find()

Welcome to the club.

But, of course, 90 K records is still really a pretty small table ... other than the zip code table I think we only have 3 or 4 others under 100 K.

[martinwcole]

RE: fetch_find()

when i read about some of you having from 100,000 to 250,000 records and up i wonder how you do it, but am learning. e.g., with only a few records you can compact the database in a few seconds, but with 250,000 parents and who knows how many children it could take an hour.

i have been experimenting with ways to keep the number of "active" indexes and sets to a minimum.

i notice that with my test data i can use the tag add method to create an infrequently used index. it takes about 2 seconds for the system to create it, and my thinking that if i do that with all infrequently used indexes and sets, it should make the system much faster - like it would keep to a minimum what has to be updated when new records are added.

a question that i can't find any documentation on is this:
if i manually "drop from the database" a set the set can be re-added manually, too. but when dropped forms using the set are dropped, too, and then added back when the set is added again. i have looked for methods to set_drop/set_add that would "automatically" do that. haven't found anything.
is there a way to do this with xbasic - or would i be better off using xbasic to create the set when the user needs it.

will keep on experimenting for optimal performance

[Finian Lennon]

RE: fetch_find()

Martin:

I don't see any performance advantage to dropping sets from the database if the set has layouts attached. (You can use the file_add_to_db() method for this) OTOH, creating a set with xbasic for the purposes of processing operations in a script is probably a good way to reduce clutter.

Finian