Jump to content
Sign in to follow this  
Lynne in NY

Tax Zones by County

Recommended Posts

Hello!

I understand that CubeCart doesn't currently apply sales taxes to any zones less than States.

In New York state (and about 32 other states) sales tax is applied only when shipping is within the state that the store is located in.

But New York state adds a deeper complexity where by the sales tax rates that have to be applied are based (mostly) on the county.
*note: we can't use zip codes because zip codes are postal delivery based and any 1 zip code can potentially cross over more than 1 county.

In order to try to "hack" my way around this, for the state of New York I'm entering all the counties as a state.
for example:
New Jersey
New Mexico
New York - Monroe County
New York - Suffolk County
New York - Wayne County
New York - Yates County
North Caroline
North Dakota
Ohio

My thinking is that I can then assign the tax rate by "county", even though it's really the state name.  But I want the abbreviation to be the same for all (NY).

Where I'm glitching up is the Abbreviation field. When I run the SQL to dump all of the "states" into the table, it dies on a duplicate value on the Abbreviation. Yet I can't see that the field is a primary key or a unique key? I suspect it is somehow a unique key. But I don't understand why?

I also notice that from the Admin control panel end, I see "New York" / "NY" as a state, but when I add a new zone via the form as "New York - Monroe County" / "NY" it responds with
Country successfully updated.
Zone successfully updated.
Zone successfully added.

But when I look at the list of zones, there's nothing new and "New York" / "NY" is still there unchanged. So it wasn't "successfully added", and I did not change the Country only the state, so neither was Country or Zone "successfully updated".

:::confused look:::

I'm also wondering ... what is actually going to show up as the shipping address via PayPal? ...is it the abbreviation or the name?

I feel like I'm so close to the answer, it's probably staring me right in the face and that's why I can't see it, lol!

Please comment and/or advise?

 

Share this post


Link to post
Share on other sites

The database table CubeCart_geo_zone does have a UNIQUE index (named 'country_id') that covers the 'country_id' and 'abbrev' columns. That is, there can be only one 226FL (226 - USA, FL - Florida) and only one 150FL (150 - Netherlands, FL - Flevoland).

So, attempting to create more than one 226NY is going to cause problems. (There is code that will fetch the 'abbrev' when given the 'name' and 'country_id' values.)

Note: I have no doubt the CubeCart programmers are feverishly solving this "tax by state/county/city/special-interest-zone" clusterfsck. My WA solution involved making a query to their online tax database.

Other note: Does not your state have very lenient requirements for online shops making less than $X annually?

An experiment I would like to try is to change the 'country_id' UNIQUE index to cover 'country_id', 'abbrev', and 'name' columns. That way:
226NYNew York - Monroe County
226NYNew York - Suffolk County
226NYNew York - Wayne County
226NYNew York - Yates County
etc
will all be unique.

 

Share this post


Link to post
Share on other sites

I like your idea of expanding the unique key to include name! That would make a very big key, but on the other hand, I'm not concerned about it, or about performance as this store front is going to be very small with regards to products and inventory.

I'm still not clear on what fields end up feeding the actual shipping address that is displayed as the "ship to" that's fed to PayPal?

I was thinking, based on your explanation, that if the code pulls the "ship to" info from the Abbreviation or the Name fields, that maybe a quick hack would be to add a field to the zones called "name_print-as" (or something), load up that field with the text that would be expected to be displayed on the actual postage, and have the program (s?) that feed the "ship to"'s in carts use that  field value. Then you could have NY01, NY02, NY03.... up to NY99. I only have 52 (or was it 58?) counties to worry about so that would work for me.

That would be less disruption to the relational structures of the database.

What do you think?

Regards NY taxes...

New York has legislation in process to be voted on for businesses with $300k+ annually to be required to be businesses with economic sales tax nexus. But this legislation is being misinterpreted a LOT. The goal is not to relieve businesses  making less than $300k of sales tax nexus, but to close the loopholes for those at or over. (Hope that didn't dash any hopes...).

[cut and paste directly form NYS sales tax guide:]

New York considers a seller to have sales tax nexus in the state if you have any of the following in the state:

  • An office or place of business (check)
  • If employees are located in the state (check)
  • Goods in a warehouse
  • Ownership of real or personal property (check. (duh.... talk about a catch all?))
  • Delivery of merchandise in New York in vehicles owned by the taxpayer
  • Independent contractors or other representatives in New York
     

(And people wonder why business is bailing out of New York at the speed of light....)

 

Edited by Lynne in NY

Share this post


Link to post
Share on other sites

Another note: MySQL returns a value that is the number of records affected by an UPDATE statement. Earlier versions of CubeCart would report a fail if an UPDATE made zero changes, but now CubeCart reports a success if there were simply no errors. It's kinda misleading no matter how you look at it.

When I tried to add a new Zone to an existing state, I got back "Failed to add zone" (expected), "Country updated" (expected, because no changes, no errors), and "Zone updated" (expected, because no changes, no errors).

According to the standard PayPal gateway module, if the customer's billing address is in the US or CA, then the state's abbreviation is used. Not being familiar with PayPal, it may be the case that they expect certain values and will reject what is not valid. So, if PayPal expects "NY" for USA addresses, then sending "New York" could cause problems. (I just don't know PayPal's API structure.)

 

Share this post


Link to post
Share on other sites

That's odd, I am getting a different result when I add a new zone with an existing country, a new name, and an existing abbreviation I get:
Country successfully updated.
Zone successfully updated.
Zone successfully added.

...in that order. Are you running the most recent version? I just installed this manually yesterday off of the "download".
Anyway, what PayPal would use is not a concern to me, if it wants to see NY then for all of the county zones in New York the new field for passing to PayPal would simply be stuffed with a value of "NY".

The only thing missing for me is what .php modules (or whatever else) are using which field for the customers invoice display and for passing the value "abbreviation", I would change al those to pass the value of the new field, which I think for my purposes I would call [state_displayname].

I gotta put this down for now and let my subconscious chew on it for a while, lol

Share this post


Link to post
Share on other sites

My experiment is with CC625. I do not recall seeing any code changes that would cause what you are seeing.

Various places throughout the codebase, CubeCart uses a pair of functions in /includes/functions.inc.php: getCountryFormat() and getStateFormat(). Given something and what it is expected to be, give back something else. For example, give "NY" as the 'abbrev', ask for 'name' to be returned.

And here's the problem: we now have more than one record with "NY" as the abbreviation. The code that calls this function will take the first array element - whatever that is. I have found that only the PayPal Pro and the All-in-One-Shipping modules do this (but there may be others).

Share this post


Link to post
Share on other sites

Oh, ok... my version is 6.2.6

I was talking about having the abbreviation be unique. So, like
NY01, New York Monroe County
NY02, New York Suffolk County

....etc.

And the for each instance of the "NY"'s, the new field (which would not be a unique nor an indexed field) would be "NY". And that  value would be the value that would get passed along in place of Abbreviation.

You see, I wouldn't even need, for my small application, to have the new field added to Admin forms and whatever/where ever else,  I would just update the field manually in table itself. That would take me about 5 minutes tops?

Super easy to put into place, but knowing what modules to modify for the customers view and the variable to be passed along to the cart is my twitch.

I'm going to assume if Abbreviation is the default field to pass along to payment processors, then using the new field should just be a matter of adding it to the query and replacing the code that sends that value with the value of the new field from the query.

Share this post


Link to post
Share on other sites

Lynne,  I was trying to do the same for California which has State, County, District,  and City taxes.  Al told me this would not work and changed everything back to Country and State.  Part of the problem was that the tax drop down box could not read a long combination. Also no matter what I put in for Zone it defaulted to the main Country/Zone and not the modified Zones (for county, district, and city).   It appears that length restriction on the box has been either elongated or removed.  Al had me pick an average tax and use that instead.  One could adjust the tax after the order was placed and it is better to return funds than to tell the customer you need more money for the tax.  I wish this would be addressed as I am sure there are other countries with complicated tax structures.  I solved the problem by moving out of state.

Edited by Crysties

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...