So that was an awful lot more complicated than I expected it to be.
Client requirements:
Flat rate shipping (regardless of number of items/cost) but with differing price based on region.
- Dublin, Ireland
- Rest of Ireland (including Northern Ireland, which is of course politically and address wise, part of the UK)
- Rest of UK, Europe, and Rest of World (I was glad when the client dropped the need for a UK/Europe rate and a separate ROW rate)
So after much research I decided to go with the approach suggested in this thread of manually adding regions to the relevant DB tables. I thought I’d share my MySQL queries here in case they were of any use to someone else looking to add unique shipping rates to custom regions/counties in Ireland (and obviously this method applies to any other country). Yes it’s definitely a very crazy way of doing it, but it’s working for me now, so I’m content. Magento, on install, already had 319 region codes specified so maybe one of the Magento team can add default codes for Irish counties (I’ve used the ISO-3166-2 county codes for Ireland – the full ISO code has every county code prefixed by IE, which I left out seeing as the country code IE had already been specified).
By the way, I had to insert into directory_country_region first, as attempting to insert into directory_country_region_name resulted in an error.
//******* Query 1 *******//
INSERT INTO `directory_country_region` (`region_id`, `country_id`, `code`, `default_name`)
VALUES
(320, 'IE', 'C', 'Cork'),
(321, 'IE', 'CE', 'Clare'),
(322, 'IE', 'CN', 'Cavan'),
(323, 'IE', 'CW', 'Carlow'),
(324, 'IE', 'D', 'Dublin'),
(325, 'IE', 'DL', 'Donegal'),
(326, 'IE', 'G', 'Galway'),
(327, 'IE', 'KE', 'Kildare'),
(328, 'IE', 'KK', 'Kilkenny'),
(329, 'IE', 'KY', 'Kerry'),
(330, 'IE', 'LD', 'Longford'),
(331, 'IE', 'LH', 'Louth'),
(332, 'IE', 'LK', 'Limerick'),
(333, 'IE', 'LM', 'Leitrim'),
(334, 'IE', 'LS', 'Laois'),
(335, 'IE', 'MH', 'Meath'),
(336, 'IE', 'MN', 'Monaghan'),
(337, 'IE', 'MO', 'Mayo'),
(338, 'IE', 'OY', 'Offaly'),
(339, 'IE', 'RN', 'Roscommon'),
(341, 'IE', 'SO', 'Sligo'),
(342, 'IE', 'TA', 'Tipperary'),
(343, 'IE', 'WD', 'Waterford'),
(344, 'IE', 'WH', 'Westmeath'),
(345, 'IE', 'WW', 'Wiclow'),
(346, 'IE', 'WX', 'Wexford');
//******* Query 2 *******//
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`)
VALUES
('en_US', 320, 'Cork'),
('en_US', 321, 'Clare'),
('en_US', 322, 'Cavan'),
('en_US', 323, 'Carlow'),
('en_US', 324, 'Dublin'),
('en_US', 325, 'Donegal'),
('en_US', 326, 'Galway'),
('en_US', 327, 'Kildare'),
('en_US', 328, 'Kilkenny'),
('en_US', 329, 'Kerry'),
('en_US', 330, 'Longford'),
('en_US', 331, 'Louth'),
('en_US', 332, 'Limerick'),
('en_US', 333, 'Leitrim'),
('en_US', 334, 'Laois'),
('en_US', 335, 'Meath'),
('en_US', 336, 'Monaghan'),
('en_US', 337, 'Mayo'),
('en_US', 338, 'Offaly'),
('en_US', 339, 'Roscommon'),
('en_US', 341, 'Sligo'),
('en_US', 342, 'Tipperary'),
('en_US', 343, 'Waterford'),
('en_US', 344, 'Westmeath'),
('en_US', 345, 'Wiclow'),
('en_US', 346, 'Wexford');
So that was my prep for Irish county based shipping prices. I realise I could have just done two regions, Dublin and Rest of Ireland, but figured whilst I’m here I may as well do it fully and someone else might benefit.
For GB, I definitely wasn’t going to do a full full county listing (there’s 80+ I think). So I just went for the 4 countries in GB…
//******* Query 3 *******//
INSERT INTO `directory_country_region` (`region_id`, `country_id`, `code`, `default_name`)
VALUES
(347, 'GB', 'ENG', 'England'),
(348, 'GB', 'SCT', 'Scotland'),
(349, 'GB', 'WLS', 'Wales'),
(350, 'GB', 'NIR', 'Northern Ireland');
//******* Query 4 *******//
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`)
VALUES
('en_US', 347, 'England'),
('en_US', 348, 'Scotland'),
('en_US', 349, 'Wales'),
('en_US', 350, 'Northern Ireland');
Finally I just had to edit my table rates CSV file as follows:
"Country","Region/State","Zip/Postal Code","Order Subtotal (and above)","Shipping Price" "IE", "D", "*","0", "4" "IE", "*", "*","0", "5" "GB","NIR","*","0","5" "GB","*","*","0","15" "*","*","*","0","15"
And then go into Admin -> System -> Configuration, change scope to website, then go to Shipping Methods->Table Rate and Import CSV and it all worked beautifully.
I nearly cried trying to work it all out, and did curse quite loudly a number of times, but, well, it’s done now, and I know how to do it again if needs be.
However it does feel like there could be a community effort to go through the ISO codes for all countries in the world and get them all listed so users anywhere in the world won’t encounter these same issues.
Comments