find by phone number, no records found

[KathyWeeks]

I would like to be able to have a push button to search by phone number. I want it to prompt user for phone number, but I only want it to retrieve the records that contain the phone number. And if no phone number is found I would like for it to say "no records found" and show NO records.

Would someone point me in the right direction please?

I currently have a find by key, but it doesn't tell you taht no records have been found. And if it does find the phone number, you keep pressing arrow down and it moves to the next record, which doesn't contain the number.

Thanks in advance for any help
Kathy

[Stan Mathews]

RE: find by phone number, no records found

Here's one way. I didn't add any code to preserve the original order of records and restore it after.

Open AlphaSports, Edit the Customer Information form, create a new button, use xbasic editor, and put this in the code box, save, etc.

tbl = table.current()
tbl.fetch_first()
findnum = ui_get_text("Which","Enter the number without () or - or spaces")
findnum = mask(findnum,"( )| - ")
query.order = "phone"
query.filter = "alltrim(Phone) = "+quote(findnum)
ix = tbl.query_create()
countnum = ix.records_get()
if countnum " 0
parentform.Refresh_Layout()
else
ui_msg_box("Sorry","No records found")
end if
parentform.Show_All()

[CALocklin]

RE: find by phone number, no records found

Note that Stan's routine shows ALL records if none are found. This is probably the best way to do it because "showing no records" will actually put the form into Enter mode just like opening a new table with nothing in it - probably not what you want to do.

I suppose you might be able to get away with this if the form is Read-only or if "Enter" is restricted but it may create other problems. I've never tried this so I'm not sure but I would definitely check it carefully before letting anyone use it that way.

[KathyWeeks]

RE: find by phone number, no records found

I pasted this exactly from your post and when I enter a phone number it always says no records found. Do I need to do something else? Also if you have time would you tell me in english what each line is doing, so I can understand. Thanks for your help, I'm new but learning!
Kathy

[Stan Mathews]

RE: find by phone number, no records found

Pasted it where? The script given is specific to the AlphaSports customer information form, designed to be the onpush event code for a new button.

tbl = table.current() 'get pointer to current table
tbl.fetch_first() 'go to the first record
findnum = ui_get_text("Which","Enter the number without () or - or spaces") 'ask the user for a phone number in the format ##########
findnum = mask(findnum,"( )| - ") 'transform the ########## into (###) ###-#### which is the way the numbers are stored in the AlphaSports customer table
query.order = "phone" 'create the order expression for a query, phone the field name in the table containing phone numbers
query.filter = "alltrim(Phone) = "+quote(findnum) 'create the filter expression for a query
ix = tbl.query_create() 'run the query against the AlphaSports customer table, creating a pointer
countnum = ix.records_get() 'get the number of records returned by the query
if countnum " 0 'if the number of records is greater than zero
parentform.Refresh_Layout() show the first record matching the query on the form
else 'else
ui_msg_box("Sorry","No records found") 'display message no records found
end if 'end the if
parentform.Show_All() 'release the query

If you try to use this on a table of yours you will have to modify the lines

findnum = ui_get_text("Which","Enter the number without () or - or spaces")
findnum = mask(findnum,"( )| - ")
query.order = "phone"
query.filter = "alltrim(Phone) = "+quote(findnum)

to suit your needs.

Or you could just attach a sample of your table so I can see how it is set up, as the "Please read before posting" thread at the top of the post listing suggests.

[KathyWeeks]

RE: find by phone number, no records found

I pasted it where you said to with in Alpha sports.
Here is the error I get:

Script::customer_information:BUTTON1.on push line 7:
ix=tbl.query_create()
No such field

[Stan Mathews]

RE: find by phone number, no records found

Can't imagine what the difference might be. I have attached a zip of my version of the customer table files from AlphaSports. If you unzip it to the location of your AlphaSports database, it should contain everything you need to see how the button works on the form. Button is in the top right, dark blue, label is just "script".

[Dave Wilson]

RE: find by phone number, no records found

Stan -

Yes, your example code works fine. I'm seeing a similar error to Kathy's when trying this with my APP (see attached screen shot). It seems to center around the line:

ix=table.query_create()

Is "ix" a variable that has to be declared explicitly?

Thanks,

Dave

[Tom Cone Jr]

RE: find by phone number, no records found

David,

Your error message suggests a syntax error in the query.filter expression.

the table.query_create() method will return a pointer to the query list when it runs successfully. "ix" is simply a variable to hold the pointer returned by the method. It need not be declared explicitly unless your script uses OPTION STRICT.

-- tom

[Dave Wilson]

RE: find by phone number, no records found

Tom -

Thanks for the reply. Here is the code I have "ONPUSH"

'Date Created: 18-Jul-2005 03:19:19 PM
'Last Updated: 24-Jul-2005 11:58:54 AM
'Created By : Stan Mathews
'Updated By : Dave
tbl = table.current()
tbl.fetch_first()
findnum = ui_get_text("Which","Enter the number without () or - or spaces")
findnum = mask(findnum,"( )| - ")
query.order = "recid"
query.filter = "alltrim(recid) = "+quote(findnum)
ix = tbl.query_create()
countnum = ix.records_get()
if countnum " 0
parentform.Refresh_Layout()
else
ui_msg_box("Sorry","No records found")
end if
parentform.Show_All()

Where "recid" is a 10 character numeric field in the current table. What might I be missing?

-Dave

[Tom Cone Jr]

RE: find by phone number, no records found

Post a working copy of your database, with instructions for us to follow to see the problem. I'm suspicious of the data types you're using in the filter expression. It's not clear you understand their data types either. RecId cannot be a character numeric field. It's one or the other, right?

-- tom

[Dave Wilson]

RE: find by phone number, no records found

Tom -

Clarification: "Recid" is a 10-numeral numeric field. I'll prepare a working copy so you can see what I'm doing. Thanks.

-Dave

[Dave Wilson]

RE: find by phone number, no records found

Tom -

OK, take a look at this. From the main menu, go to "add/edit records' then you'll see a push button on the botom to "search recrod ID". Recid is a 10 digit numeric field. I still get the before mentioned error with this example database. Any thoughts on what's wrong apprecitated.

-Dave

[Stan Mathews]

RE: find by phone number, no records found

I'm not sure why you'd be storing a phone number as a numeric type, if that is what you are doing. The expression

query.filter = "alltrim(recid) = "+quote(findnum)

would be

query.filter = "recid = "+findnum

for a numeric field.

The common usage is to use numeric fields only if you intend to do computations involving the contents. Using character fields to store such strings of digits lends itself to much easier parsing if you want a subset such an area code, the "plus four" of a zip code, etc.

[Stan Mathews]

RE: find by phone number, no records found

Get rid of

findnum = mask(findnum,"( )| - ")


as well. I don't imagine Alpha lets you mask a number.