Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 2 of 6
Automated USPS Click & Ship Application
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 

Forgot to say thanks for a very cool program.  I sure hope that I can get it to work because it will be a huge time saver.  Copying and pasting is very annoying.  Tell your fiance that I’m glad she whined enough to get you to write this app.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

Yeah, on application open it should auto load any orders currently set to processing.

I’m coding in a couple db checks to verify if the connection is working so we can rule that out.
I am also going to have it fill in the db connection settings fields so you know the application saved them.

Give me about 1/2 an hour to make these changes and I’ll upload a new version.

As far as the Fiance smile I’ll be sure to pass the message along.  I know it’s decreased my “nag” level quite a bit.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

Alright,

New version posted.  This will give you an error message if it is unable to connect to the database.
It also fills in the connection settings fields (*’s out the password).

This should at least give you an idea if it is the database connection failing, or if for some reason it is not finding any pending orders.

As a side note:  Does your MySQL database use the default port of 3306 or something else (this may have something to do with it as I never tested connecting to a different port and the connection strings built into the application for this differ slightly)

 
Magento Community Magento Community
Magento Community
Magento Community
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 

Ok, I downloaded the new version and put in all of the information on the settings page.  Then I restarted the application.  I can see that the “Magento Database Information” is saved now (which is nice!) but I still get nothing on the “Order Info” tab even though I have one order that is “processing”.

I get no errors.  And yep, I have the MySQL database set to use the default port of 3306.

For the ADDRESS under “Magento Database Information”, I’m putting in the IP address of the server.  That’s what should be there right?

Maybe the tables have changed?  What version of Magento is this written for?  I’m running 1.3.2.4.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

hmm,

alright.  Can you do this?

Log into the SQL server via phpMyAdmin go to the SQL Table and run the following query or whatever your flavor is for doing this.

SELECT * FROM `sales_order_varchar` WHERE attribute_id = ‘102’

this should return results similar to:

Edit Delete 289 11 102 21 complete
Edit Delete 301 11 102 22 canceled
Edit Delete 313 11 102 23 complete
Edit Delete 327 11 102 24 complete
Edit Delete 341 11 102 25 complete
Edit Delete 353 11 102 26 complete
Edit Delete 367 11 102 27 complete
Edit Delete 381 11 102 28 complete
Edit Delete 395 11 102 29 complete

I assumed that attribute 102 was the same across the board but I could be wrong.  I may need to add a query to locate which attribute is responsible for marking an order complete/pending/ect

Let me know what you find out.
If you get similar results to that above.  go to the last page of the results and look at the last column.
Does it say pending?  or something else?

 
Magento Community Magento Community
Magento Community
Magento Community
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 

yeah, it’s not attribute_id 102 for me.  It appears that there are two of them and their numbers are 106 and 107.

mysqlSELECT FROM `sales_order_varcharWHERE value='processing';
+----------+----------------+--------------+-----------+------------+
value_id entity_type_id attribute_id entity_id value      |
+----------+----------------+--------------+-----------+------------+
|     
1177 |             11 |          107 |        94 processing |
|     
1178 |             11 |          106 |        94 processing |
+----------+----------------+--------------+-----------+------------+
2 rows in set (0.00 sec)
 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

Yeah, one is the Order Status, one is the Order State.

Since order status can be a range of different values like Pending Payal.. or Pending Shipping or Credit card billed, ect..

I use the State value instead which is more reliable…

Let me see what I can create really quick to fix this.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

Ok, application updated to grab the STATE value dynamically using the following query

SELECT attribute_id FROM `eav_attribute` WHERE entity_type_id = (SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = ‘order’ Limit 1) AND attribute_code = ‘state’

This SHOULD resolve your problem.

Let me know one way or the other.

 
Magento Community Magento Community
Magento Community
Magento Community
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 

That did it!  It pulls in the order that’s pending now.

But there are a couple of things preventing it from being perfect (one of which makes it unusable for me):
1) The weight isn’t being pulled in correctly from the database.  This isn’t a huge deal because I can set it manually easily enough before I hit the submit button, but it would be nice if it were pulled in correctly.  Perhaps this is another problem with pulling data from the wrong column in a table?
2) The service to ship with is automatically set to “Priority Mail” and doesn’t allow me to choose from the other options (Small Flat Rate Box, Medium FRB, Large FRB, Flat Rate Envelope or Express Mail).  If I click on the “edit” link in the Shipping Cart to change the Service Type, it auto submits again before I can change the type.  This makes it so I can’t use it right now.
3) One other small thing, is there a way to automatically select “Save to Address Book”?

Thanks again for working through this with me!

Once #2 is addressed, I’ll be able to test this all the way through and see how the back half of the app works (where it updates the status of the order and inserts the tracking numbers).  I’m not going to ship the order until Monday though, so I won’t know until then if the back half of the app works for me.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

Lets start with the deal breaker #2

When you click on an order in the left panel it should automatically select the shipping method the customer selected.

At that point you should be able to change the shipment method from the drop down and have it send that to USPS.

So the proper process would be to double click on the order # then change any fields needed.  I am assuming your are doing it in this order.

Secondly, what is wrong with the weight?  does it pull in any value at all?

 
Magento Community Magento Community
Magento Community
Magento Community
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 
kardson - 05 December 2009 02:50 PM

Lets start with the deal breaker #2

When you click on an order in the left panel it should automatically select the shipping method the customer selected.

At that point you should be able to change the shipment method from the drop down and have it send that to USPS.

So the proper process would be to double click on the order # then change any fields needed.  I am assuming your are doing it in this order.

Sorry I missed that entry under “SERVICE”.  That solves it!

kardson - 05 December 2009 02:50 PM

Secondly, what is wrong with the weight?  does it pull in any value at all?

It does pull in a value.  I haven’t looked to see what value from the table it’s pulling in, but it pulls in 49 and puts it in the Ounces text field and nothing in the Pounds text field.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

is your package a little over 3 pounds?

If so then I believe it is spot on.

16 ounces in a pound

So your package probably weighs 3lbs 1oz

I let the Click & Ship site do the conversion for me.
If you leave it at 49 and hit submit, when you get to the confirmation screen you’ll notice that USPS auto converts it to 3pounds 1 ounce

It’s easier for me to do this then have to code the extra calculation needed to divide it by 16 to get the pounds then grab the remaining value from the whole numbers and multiple it by 16 again to get the left over ounces.

 
Magento Community Magento Community
Magento Community
Magento Community
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 

Hah, yep, it sure is.  I didn’t realize that you were allowing the USPS site to convert it...duh!  I should have realized that.

 
Magento Community Magento Community
Magento Community
Magento Community
 
kardson
Member
 
Total Posts:  59
Joined:  2009-02-17
 

alright…

With that out of the way.  Not exactly sure why you would want to save the address to the address book but I’ll get that added in my next release.  Shouldn’t be too hard.

I appreciate you trying it out and reporting back on what issues you were having.  Definitely an oversight on my behalf regarding the entity_id issue.  Let me know if there is anything else you find.  I would also like to hear how well it works for you once you use it for a little while.

 
Magento Community Magento Community
Magento Community
Magento Community
 
flytrapcare.com
Member
 
Total Posts:  46
Joined:  2009-08-09
 
kardson - 05 December 2009 03:48 PM

Not exactly sure why you would want to save the address to the address book but I’ll get that added in my next release.  Shouldn’t be too hard.

With the ability to pull orders directly from the Magento backend with this app, it’s really not that desperately needed, but sometimes I do ship products to people that have ordered before without having them actually use the Magento checkout process.  In those cases, I’d like to have their addresses saved.

I’ll definitely report back to you on how I like the app after I use it for a bit.  Thanks again for sharing your work!

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 2 of 6