Thread: Question about Tax Table working with USPS Address Verification

I have gone to great lengths to get my tax table working with something like 10,000 zip codes (and yes, I know that taxes are not determined by zip code, at least in California, but what else can be done.)

The table worked great, until, I signed up and got the USPS address verification service which automatically appends the "+ 4" zip code portion to the zip code. (ie, I have "91511" as a zip code, but the US Postal Service validates and appends something like "91511+1234", and that is not handled by the table.

Now the tax table won't work because it has issues with the "+4" portion.

Is there a wildcard or something I can run a sql script to update the table to work somehow?

I don't have the source code, but think this should be corrected. Even if just by doing a sql fix (i.e., "left 5 characters only" lookup to the tax table)

Any help is graciously welcome ,

Charlie

Hi I am Ramon from JairoSolutions Inc a DevNet Partner with reference to the tax table we have actually two options:

1. We will use sql code to remove the extract zip codes

2. We can customize the tax table code so that it will only grab the first 5 digit without including the numbers that were appended by usps.


For further explanations contact me at info@jairosolutions.com.

Many Thanks,
Ramon

Hi Ramon,

Looking for help from peers or AspDotNetStorefront staff. I understand code, but looking for help upfront, not to have someone do it for money.

thanks for offering to help,

- Charlie

- bump -

Need some help if anyone has come across this.

TIA,

Charlie

I have a script here where you can extract only the 5 characters out of a field, but I need to know what table are we talking about here? ShippingZone? or ZipTaxRate? Let's start from here...

Hi Jao,

Thank you very much for the reply.

My hope is that AspDotNetStorefront can help me compare only the left 5 of the zipcode in the 'zip' field in the Address table against the 'ZipCode' field in the ZipTaxRate table.

Please note: I am not trying to just retrieve the zipcode, that is easy for me to do. I am trying to get the AspDotNetStorefront order process to correctly find 5 digit zipcodes in the ZipTaxRate table, and the USPS validation feature does not work with it, because they are more detailed Zip Plus Four values in the Address table, and therefor the tax rate is never found.

If your script is an update sql script that looses the zip plus four data, it does not help me.

I already now how to loose the quality of the mailing zip code by doing such an update query. (And it would need to be a insert trigger anyways, since it would have to degrade the zipcode resolution 24/7 or the tax would not be calculated on a new customer's new order)

What I would prefer is to get some sort of fix that allows the comparison to only be done on the 'traditional' 5 digit zip against the ZipTaxRate table, while still keeping the full zip plus four zipcode that the USPS address verification creates in the Address table for more accurate shipping purposes.

Please let me know if you can point me to a fix that will let both features (Zipcode Tax Rate lookup AND USPS address verification) work properly.

Again, Thank you for your help Jao !!

I have come across the same problem... and when I posted here several weeks ago, the reply was that part of the design is intentional- because Zip code boundaries do not align with political or taxing boundries. Looking at california, there are some zips that cover 2 or 3 cities, and some that cover 2 counties, with different tax rates.

A suggestion on this forum was to create a trigger in ADDRESS that would create a new entry in the ZipTaxRate table with the +4...
In other words, if a new address is entered with a zip of 94551-7212, and there is no entry under this Zipcode in ZipTaxRate, then insert one that copies the 94551 rate...

it is definitely a hack, and, since I have not written any triggers yet, I have not modified my production Database, but, it may work.

-

Thanks Tbadowski !

That would work for me, and I can easily added it on my end. I will still wait to see if Jao from AspDotNetStorefront has a better solution however....

I don't buy the statement that it was intentional.... Why allow any zipcodes to work if they don't align with tax boundaries exactly.... And if the feature is there, it should work with other features (IE usps validation...)

"I'm sorry sir but if you want the sunroof, you can't have an engine ?!!!! "

I agree it's not perfect but I think putting in 2,683 zipcodes from 8.25 to 10.25% is more accurate than most people are doing.

Thanks again for the suggestion!!

No need for the script, if you have the source code, you can modify the AppLogic.GetZipTaxRate SQL script and simply add a LEFT function on the DB.SQuote(Zip) and that's it...

Hi Jao,

Thanks for getting back to me.

My first post says "I don't have the source code, but think this should be corrected. Even if just by doing a sql fix (i.e., "left 5 characters only" lookup to the tax table)"

I am well aware of SQL. I don't have the source code. I should not have to buy it to fix "stock" features that AspDotNetStorefront writes.

(As an employee, I would have thought that you would be able to see if I have source code or not since you work for AspDotNetStorefront.)

Is there anything you can do for me to make the features AspDotNetStorefront decided to include, compatible?

If you were to compile the change in AppLogic.GetZipTaxRate table for me and send me the dll that would work.

I am running ML 8.0.1.2

Thanks for your help.

You'll need to submit a support ticket for this one in order for our technical support engineers to examine the issue and provide you with the appropriate patch...

I am not going to use up a support ticket, to have something fixed that should work correctly out of the box.

I have all ready started writing a SQL fix to will deal with it outside of AspDotNetStorefront by taking tbadowski's suggestion.

Thanks for trying.

Add it to the list of things to correct in the next version.

Originally Posted by cengen

- bump -

Need some help if anyone has come across this.

TIA,

Charlie

Having same problem and just submitted ticket to support. Did you get a solution, asside from turning address verification off?

Thanks

Jim

Originally Posted by Jao

You'll need to submit a support ticket for this one in order for our technical support engineers to examine the issue and provide you with the appropriate patch...

Ticket submitted. Looking forward to the patch!

Matthew @ Aydus Consulting just fixed this issue for me.

Thanks!