Usps_city_from_zip() ?

[Rayr]

Hi All,

I have a customer table and I am using USPS_CITY_FROM_ZIP() and USPS_STATE_FROM_ZIP() functions, I have placed each function in the default value field in the table for each field, my problem is it only works if the city and state field get focus after entering the zip code and only when entering a new record not allowing the city and state field to change if we change the zip later on.

What I would like to do is have the USPS_CITY_FROM_ZIP() and USPS_STATE_FROM_ZIP() functions fire and set the city and state field as soon as the zip code field looses focus all the time not just when adding a new record, could someone please show me how to write an xbasic script and where I should place it at the table level that will work all the time no matter what field gets focus after the zip is updated or entered for the first time.

[Tbaker]

Re: Usps_city_from_zip() ?

Ray

Using a default value in field rules will only fire when the record is first entered. After that it will not. So the default is doing what it is supposed to do.

To get the city to change when changing the zip code you would have to use a calculated field in the field rules or put an on change event in the zip code field to update the city and state fields when the zipcode changes but leave the default in the field rules.

In the onchange event for the zip code field

:formname.city.value=usps_city_from_zip("zipcode")
:formname.state.value=usps_state_from zip("zipcode")

Where formname is the name of your form - replace city and state with the field you have in your table.
Tom

[Rayr]

Re: Usps_city_from_zip() ?

Ray

Using a default value in field rules will only fire when the record is first entered. After that it will not. So the default is doing what it is supposed to do.

To get the city to change when changing the zip code you would have to use a calculated field in the field rules or put an on change event in the zip code field to update the city and state fields when the zipcode changes but leave the default in the field rules.

In the onchange event for the zip code field

:formname.city.value=usps_city_from_zip("zipcode")
:formname.state.value=usps_state_from zip("zipcode")

Where formname is the name of your form - replace city and state with the field you have in your table.
Tom

Tom,

Thank you very much for the info, can I put this code at the table level so its not form dependent?

[Tbaker]

Re: Usps_city_from_zip() ?

In the field rules you can make city and state a calculated field dependent upon the zipcode field.

in the city calculated field expression you would show

 usps_city_from_zip(zipcode)

in the state calculated field expression you would show

 usps_state_from_zip(zipcode)

That way anytime the zipcode is changed the calculated field expression would lookup the city and the state and enter them in the appropriate fields on any form or browse connected to this table.

However, if you have a form or a browse and the zipcode reflects a city that is not the one you want but is located in that zip code, you would not be able to change the city since it is a calculated field.

Just a caution.

Tom

[Rayr]

Re: Usps_city_from_zip() ?

In the field rules you can make city and state a calculated field dependent upon the zipcode field.

in the city calculated field expression you would show

 usps_city_from_zip(zipcode)

in the state calculated field expression you would show

 usps_state_from_zip(zipcode)

That way anytime the zipcode is changed the calculated field expression would lookup the city and the state and enter them in the appropriate fields on any form or browse connected to this table.

However, if you have a form or a browse and the zipcode reflects a city that is not the one you want but is located in that zip code, you would not be able to change the city since it is a calculated field.

Just a caution.

Tom

Hi Tom,

Thanks for the info, this would not work because as you stated I cant change the city, state if the zip brings up the wrong info, is their any other way I can use xbasic at the table level without using calc field?

[Tbaker]

Re: Usps_city_from_zip() ?

Ray

You could try this to see if it works for you

In the field rules for zipcode - field event - Onwrotefield

dim t as p
t=table.current()
t.city=usps_city_from_zip(t.ZIPCODE)
t.st=usps_state_from_zip(t.ZIPCODE)

t.refresh()

just replace the fields I have with your fields.

This will allow you to change the name of a city if the wrong city shows up.

The only problem with this type of field event is that it does not refresh the fields until you tab into the city and state fields. I'm sure if you played around with the form field zipcodes ondepart event, you could probably get the fields to refresh. I haven't really looked at that.

Still leave the default for city and state as it is with the usps_city_from zip() and usps_state_from_zip(). That will enter the proper zip code into the fileds when a new record is entered.

This might work for you.

Tom

[Rayr]

Re: Usps_city_from_zip() ?

Ray

You could try this to see if it works for you

In the field rules for zipcode - field event - Onwrotefield

dim t as p
t=table.current()
t.city=usps_city_from_zip(t.ZIPCODE)
t.st=usps_state_from_zip(t.ZIPCODE)
 
t.refresh()

just replace the fields I have with your fields.

This will allow you to change the name of a city if the wrong city shows up.

The only problem with this type of field event is that it does not refresh the fields until you tab into the city and state fields. I'm sure if you played around with the form field zipcodes ondepart event, you could probably get the fields to refresh. I haven't really looked at that.

Still leave the default for city and state as it is with the usps_city_from zip() and usps_state_from_zip(). That will enter the proper zip code into the fileds when a new record is entered.

This might work for you.

Tom

Hi Tom,

Thank you for the script however this did not work, my code is below-

'Sets the city and state by looking up zipcode from usps
dim t as p
t=table.current()
t.billing_city=usps_city_from_zip(t.BILLING_ZIP_CODE)
t.billing_state=usps_state_from_zip(t.BILLING_ZIP_CODE)
t.refresh()
'End script

[Tbaker]

Re: Usps_city_from_zip() ?

Ray

Did you put your code in the field event onwrotefield for billing_zip_code?


I just tried it with my test db and it worked.

Here is a zip of my test app

Just change a zipcode on an entry and then cursor into city and then into state.

Tom

[Rayr]

Re: Usps_city_from_zip() ?

Ray

Did you put your code in the field event onwrotefield for billing_zip_code?


I just tried it with my test db and it worked.

Here is a zip of my test app

Just change a zipcode on an entry and then cursor into city and then into state.

Tom

Tom,
Thank you very much for all your help in resolving my trouble, after downloading your test table I noticed I was still getting the unexspected results that I was looking for, after carful review and testing your table I noticed I still had to tab into the city and state field in order for the lookup to happen so I investigated more and noticed if I did a form refresh after tabbing out if the zipcode field all worked well. The final code is below-

'Code used to lookup city and state from zipcode from usps website
dim t as p
'dim frm as p
t=table.current()
t.billing_city=usps_city_from_zip(t.BILLING_ZIP_CODE)
t.billing_state=usps_state_from_zip(t.BILLING_ZIP_CODE)
t.refresh()
form.refresh()
'end script

[Tbaker]

Re: Usps_city_from_zip() ?

Ray

I did not know that from a field event in the field rules that the code

 form.refresh()

works.

Or did you mean

 form.refresh_fields()

Tom

[CALocklin]

Re: Usps_city_from_zip() ?

The <form>.refresh() will work from the field rules BUT it will generate an error if there is no form open and you are just working directly in a browse. It might also cause an error if just running an xbasic routine but I don't think so because most field rules aren't triggered when tables are changed with xbasic. ALSO, just using "form.refresh()" probably won't work because "form" would first have to be defined so it points the the appropriate form.

If you want to make that OnWrote event handle the whole thing so you don't need to worry about creating new forms and forgetting the "refresh", you can add something like this:

IF eval_valid( "parentform.this" )
    parentform.Refresh_Layout()
END IF

FWIW: I don't believe the "<tbl>.refresh()" is intended to do what you want in this case. The <tbl>.refresh() command is really supposed to be used to refresh the current table when other scripts/users may have changed the data while something else was going on in your script. For example, you might have a script that opens a table then displays an xdialog before doing something else. If you leave the xdialog open while you answer the phone, someone else may get in and change your record. The .refresh() will update your current session with the new data.

Even if it refreshed the data in your table, that doesn't necessarily mean it will refresh the form because the form is a separate object. Even if it did work, I wouldn't trust it. Anyone who has used multiple versions of A5 can tell you that just because something worked in a previous version doesn't mean it will work in the next version. When this happens it's usually because the old version allowed something that shouldn't have been allowed. (If there are any perfect programmers out there, please let me know so I can add you to my black list.)

Whenever possible, my preferred method for handling something like that is to close any open tables before running the xdialog then re-open the table afterward. This isn't always feasible but, when it is, it's always safe.

The <form_pointer>.refresh() is a different story.

ALSO, although all the "refresh" commands say they were originally from A5v5, be aware that some of them were earlier commands that didn't always work well in certain situations. I believe the .refresh_layout() is one of the newer commands and works in more situations. I won't guarantee that it always works but it's the first one I try now.

(Resynching is a whole separate and complex issue that I won't even get into here.)

[Rayr]

Re: Usps_city_from_zip() ?

The <form>.refresh() will work from the field rules BUT it will generate an error if there is no form open and you are just working directly in a browse. It might also cause an error if just running an xbasic routine but I don't think so because most field rules aren't triggered when tables are changed with xbasic. ALSO, just using "form.refresh()" probably won't work because "form" would first have to be defined so it points the the appropriate form.

If you want to make that OnWrote event handle the whole thing so you don't need to worry about creating new forms and forgetting the "refresh", you can add something like this:

IF eval_valid( "parentform.this" )
    parentform.Refresh_Layout()
END IF

FWIW: I don't believe the "<tbl>.refresh()" is intended to do what you want in this case. The <tbl>.refresh() command is really supposed to be used to refresh the current table when other scripts/users may have changed the data while something else was going on in your script. For example, you might have a script that opens a table then displays an xdialog before doing something else. If you leave the xdialog open while you answer the phone, someone else may get in and change your record. The .refresh() will update your current session with the new data.

Even if it refreshed the data in your table, that doesn't necessarily mean it will refresh the form because the form is a separate object. Even if it did work, I wouldn't trust it. Anyone who has used multiple versions of A5 can tell you that just because something worked in a previous version doesn't mean it will work in the next version. When this happens it's usually because the old version allowed something that shouldn't have been allowed. (If there are any perfect programmers out there, please let me know so I can add you to my black list.)

Whenever possible, my preferred method for handling something like that is to close any open tables before running the xdialog then re-open the table afterward. This isn't always feasible but, when it is, it's always safe.

The <form_pointer>.refresh() is a different story.

ALSO, although all the "refresh" commands say they were originally from A5v5, be aware that some of them were earlier commands that didn't always work well in certain situations. I believe the .refresh_layout() is one of the newer commands and works in more situations. I won't guarantee that it always works but it's the first one I try now.

(Resynching is a whole separate and complex issue that I won't even get into here.)

Hi Cal,
Thanks for the info, I did open a default brows after reading your post and see that my code does still work even with the default brows put I still changed my code from form.refresh() to-

IF eval_valid( "parentform.this" )
parentform.Refresh_Layout()
END IF

[Rayr]

Re: Usps_city_from_zip() ?

Ray

I did not know that from a field event in the field rules that the code

 form.refresh()

works.

Or did you mean

 form.refresh_fields()

Tom

Hi Tom,
As Cal stated form.refresh() does work and that is what I was using but based on what Cal said I changed my code to-

IF eval_valid( "parentform.this" )
parentform.Refresh_Layout()
END IF

Thanks for all your help and saving me time not having to figuring this out on my own :)

[CALocklin]

Re: Usps_city_from_zip() ?

Ya' learn some'n new every day. I couldn't find any reference in the Help file to using "form" that way but I did find it in the list of reserved words. Apparently it is acceptable - I just wish I new exactly what it refers to.

By the way, if you aren't familiar with the difference in Parentform and Topparent, check it out in the Help file. Sometimes they refer to the same thing and sometimes they don't - the difference can be significant.

[Tbaker]

Re: Usps_city_from_zip() ?

Cal

I'm not trying to beat a dead horse since Ray has gotten his app working with your help.

But when I used form.refresh() in the onwrotefield for zipcode and then opened the form, I get an error message (see attached thumbnail).

I also attached a thumbnail of the onwrotefield event.

That is why I didn't think that would work and suggested form.refresh_fields() which does work.

I guess this is just to enhance my knowledge.

Thanks

Tom Baker