Search?

[Peter.Greulich]

The following script work fine with an index:
topparent.find(search.value)

However, it does not seem to work with a filtered query. Here is the query:
query.description = "G_Est_No"
query.order = "NEW_BUS->Year + NEW_BUS->EST_NO"
query.filter = "substr(NEW_BUS->EST_NO,8,1)'H'"
query.options = ""
tbl.query_create()


Any suggestions?
Peter

[Bill Warner]

RE: Search?

I tried the same thing, substituting values and field names to suit one of my tables. It worked fine. When you say it doesn't work, exactly what happens?

One thought: your last line is

tbl.query_create()

Did you define what tbl is? (i.e., tbl=table.open("table_name") or tbl=table.current() )

Does the query work as expected? If so, what happens when you run the find?

[Peter.Greulich]

RE: Search?

Bill,
Thanks. The query works fine as do a dozen other queries used in that set. What happens is the query runs and that's it - no find. But, I just checked the book, it says the .find() command is supposed to work w. indexes, so I suppose that excludes queries. So I guess I am surprised that it works for you.
Peter

[Tom Cone Jr]

RE: Search?

Peter,

Most people have trouble framing the filter expression when building a query in xbasic. It's likely that Bill just checked the syntax of the query filter expression, using one of his own tables. He confirmed that it works.

There's a big difference between a find by key and a query. The find by key is designed to search for the first matching record, only. The query is designed to return a collection of all matching records.

Can you explain further what it is you are trying to accomplish?

-- tom

[Mick Magnuson]

RE: Search?

Hi Peter
I wonder if a range filter would not better suit your needs. My case: Sales department quote log. All quotes are in one .dbf for all the sales reps. Each rep has their own form specifically for quotes they have written. But, they also need to be use the "find by key" with set indexes, such as by customer or quote number, etc.

Example:
Rodger needs to find a quote #, but it only needs to look at quotes Rodger has written. I have a button on the form to change the index, such as "Search by Quote #".

tbl.index_primary_put("Quote_Number")
range.flags=range_filter
range.filter="sales_rep='Veneman, Rodger'"
tbl.range_add()
parent.resynch()

Then he can use the "find" icon on the tool bar.

Just a thought.
Mick

[Bill Warner]

RE: Search?

Peter, I made some assumptions when you sent your script, and then tested mine. Let's see if I can be a little clearer.

I ran the query. Then I ran the find. The query gave me the correct records, and then the find returned the first record with the value I entered.

Your find script will return a record. Your query should return one or a group of records, depending on the search criteria.

If you put the find script AFTER the query, maybe that will give you what you want. Or, you could run the query with a sort but NO filter, and use the find to return the first record, based on that sort.

Did that help??

[Peter.Greulich]

RE: Search?

Tom,
The query works as advertised. I'm running the query and trying to find a record based upon the querie's order.
Peter

[Peter.Greulich]

RE: Search?

Mick,
Thanks. I have avoided ranges since DOS. I'm trying to simplify my life:-)
Let me see what else works first.
Peter

[Peter.Greulich]

RE: Search?

Bill,
I ran the query. Then I ran the find. The query gave me the correct records, and then the find returned the first record with the value I entered.

I ran the query. Then I ran the find. The query gave me the correct records. The find "returns" the first record in the query, not the value I want.

Your find script will return a record. Your query should return one or a group of records, depending on the search criteria.

The query works, the find does not.

If you put the find script AFTER the query, maybe that will give you what you want.

I put the find after the query. No dice.

Or, you could run the query with a sort but NO filter, and use the find to return the first record, based on that sort.

I need the filter.

Thanks,
Peter

[Mick Magnuson]

RE: Search?

Hi Peter
I'm bored and have no life so I made a simple file to test.

Field 1 is color, Field 2 is a name. A button on the form creates a query filtering out any color with an R in the 3rd position (purple). The query runs and a msg_box pops asking for the find criteria.

Since my query order is alltrim(f1)+alltrim(F2), the topparent.find will look in the field 1 (color). If the data is BLACK (f1) and the name is RICK (f2), the topparent find will see "BLACKRICK", and return the closest thing based on what I entered in the dialog box. Is this what your trying to do?

Mick

[Peter.Greulich]

RE: Search?

Hi Mick,
I am tired now and will look at your download tomorrow. Take heart in that humility is a great virtue! Thanks for your effort.
Peter

[Tom Cone Jr]

RE: Search?

Bill,

I think the problem Peter is having is this. When you run the query the query list becomes active. He then wants to do a find (which is essentially a find by key). The find requires a primary index.

Haven't bumped into this before because, since the find is so fast, I don't bother to create the query first. Perhaps Peter needs it for other purposes.

Apart from Mick's idea using a filtered range, maybe Peter could open a second instance of the table, set the desired index primary, and do his find there. But if he needs to know whether that particular record falls within his 'range' (identified in the query list) this wouldn't help.

-- tom

[Peter.Greulich]

RE: Search?

I think the problem Peter is having is this. When you run the query the query list becomes active. He then wants to do a find (which is essentially a find by key). The find requires a primary index.

Exactly

Haven't bumped into this before because, since the find is so fast, I don't bother to create the query first.

Tom, what do you mean. Can you expand on that?

Perhaps Peter needs it for other purposes.

Yes, the query segregates records by company division. The division manager or employee needs to find the record for that division w/o seeing the other division's records.

[Tom Cone Jr]

RE: Search?

Peter,

The find by key uses the primary index and is virtually instantaneous. Unlike a query the sorted list does not need to be specially prepared before doing the find. You just set the desired index primary, and do the find. bingo you have the result.

It sounds like you are trying to prevent one user from seeing another's records. this may be a good reason to build a list of qualifying records, and then do the find from among those only. Mick's filtered range will do this.

Another alternative would be to prequalify the find key. Instead of using whatever key might be supplied by the user, collect the input, and then validate it yourself. If the user is looking for something that belongs to another user, don't do the find at all. Otherwise set the index primary, and do the find instantly. You would have to be careful to prevent further navigation by the user, once the found record appears, but that might not be too hard.

-- tom

[JohnZaleski]

RE: Search?

If you are trying to limit the records displayed to those entered by a particular user, and the current user code is kept in a global variable, you could put a form filter in the properties of the form sorted by the field or expression that you want to find by. When the user enters the form, nothing will have to be done but execute the find.