query problem

[Mike Reed]

Hi,
I am trying to run this query and it won't work. The interesting thing is that the first time I ran it, it worked fine, but now it won't work at all.
I took the code to a different machine and the same thing happened. It worked the first time but won't work any more.

Any thoughts?

dim newcode as c
dim tbl as P
dim qry as P
dim nrecs as N

newcode = "con"
tbl = table.open("mgmt_co")
indx = tbl.index_record_get()
indx2 = tbl.index_primary_put(indx)
query.description = "Management Company"
query.order = "mgmt_co"
query.filter = "*leading(Mgmt_Co,newcode)"
query.options = ""
qry = tbl.query_create()
nrecs = qry.records_get()
?nrecs
= 0

There are 27 records in the table that meet the filter.

Any ideas why this doesn't work?

thanks,
mike

[Tom Cone Jr]

Re: query problem

I think this will help, but it would easy to test if you supplied sample data.

Note this is not a complete script, I've just showing the parts I'd work on.

newcode = "con"
tbl = table.open("mgmt_co")
[COLOR="Red"]' indx = tbl.index_record_get()   <-- not needed
' indx2 = tbl.index_primary_put(indx) <--- not needed[/COLOR]
query.description = "Management Company"
query.order = "mgmt_co"
query.filter = "*leading(Mgmt_Co,[COLOR="Red"]quote(newcode))[/COLOR]"

[Mike Reed]

Re: query problem

Hi Tom,
Thanks for the hint.
I tried with the additional quote() option. Still doesn't work.

I agree that the 2 references for indexing are not needed. I added those in a desperate attempt to try something to get it to work.

Have you tried this on any database?

[Tbaker]

Re: query problem

Mike

This may be a dumb question, but is the Mgmt_co in the query.filter a field in your table?

The only reason I ask is that it is the same name of the table that you are opening.

Tom Baker

[Mike Reed]

Re: query problem

Yes it is. The odd thing is that this function will work sporadically using the query by expression with the table in browse. Even then, it doesn't function consistently.

mike

[G Gabriel]

Re: query problem

Close the table at the end of the script.
tbl.close()

[Tbaker]

Re: query problem

Mike

Let me make a suggestion. I am lazy when it comes to setting up a query.filter. A lot of times I go to the operations tab on the control panel - new operation - create query - use the table that I am querying and then use the genie.

I then save the operation. Right click on it in the control panel and click on show xbasic. That shows me the query.filter that works. I then use that as my basis for the query filter in my xbasic.

The reason I do this, is there are times when I write the query myself and it looks okay - but it doesn't work. I then copy the query.filter from the operation and it works. Sometimes it is a space or something else that causes my first attempt to fail.

Just a thought, that might help.

Tom Baker

[Mike Reed]

Re: query problem

Tried that too....still no dice.

Attached is a zip file of the database slimmed down to 2 fields. It has a script and a query operation. Can anyone get it work consistently?

thanks,
mike

[G Gabriel]

Re: query problem

Fixed.
I also did minor change in your field rules (had no impact on the issue at hand).
The question I wonder:
since company name is unique: you couldn't possibly expect more than a count of 1 at the most, which means, you could simplify your script using something like exists(). Yu merely want to know if it exists or not, but not necessarily interested in the count.

[Tbaker]

Re: query problem

Here is another way, using your function *leading
May not be what you want.
FWIW

Tom Baker

[Mike Reed]

Re: query problem

Thanks for your help! I now have a way to find the info I needed. We are trying to stop a duplicate entry.

It is clear the *leading() doesn't work so now I have a work around. I'm thinking I'll send this in as a bug. I also tried *any() and *first() with the same results.

With the company name, it won't always be unique. The reason is that this customer has offices in 6 cities and they do work for apartment complexes. A management company may have offices in each of those cities as well. Some management companies want billing sent to their local offices and some to a central office. Since all the data being billed comes from my customer's main office, they need to have a unique, but discernable mgmt code assigned to the complex. Each satellite offices FTP's their work orders to the main office where pay and billing is done.

Thanks again!
mike

BTW, what was the minor change to the field rules?

[G Gabriel]

Re: query problem

It is clear the *leading() doesn't work so now I have a work around. I'm thinking I'll send this in as a bug. I also tried *any() and *first() with the same results.

They both work fine, but they are case sensitive:
dim a as c

a="Alpha software inc"
?*leading(a,"alp")
= .F.
?*leading(a,"Alp")
= .T.
?*leading(a,"Alpha s")
= .T.
?*first(a,"alp")
= .F.
?*first(a,"Alp")
= .T.
?*first(a,"Alpha s")
= .T.

In the field rules, you were using remspecial() twice. I just simplified the expression with one remspecial().

[Tom Cone Jr]

Re: query problem

Mike, I'm pleased solutions were found. Notice that they appeared shortly after you posted sample data. It's no coincidence.