Category: Visual Foxpro

Jet API

Ugh, the API for selling on Jet.com as just as bad as the Walmart API.

At least Jet has a test environment unlike Walmart. That would be an improvement – if it worked. It doesn’t:

  • While you can upload items to the test API, it just tells if you they uploaded or not. It doesn’t actually show you what the item is going to look like on Jet.
  • Items that upload just fine in the test environment can suddenly have errors when uploading to the live environment. For example, the test environment does not enforce image requirements that the live environment does.
  • You are required to mark an item as Published on the live system. This function does not work on the test system. Don’t spend a lot of time trying to figure out why your Publish API call does not work. It’s not you, it’s them.
  • The test system will let you upload an item without any pricing. You might think then that this is allowed in the live system. It’s not.
  • You can, at least, create test orders in Jet and import them. That’s a big improvement over Walmart. But, as I discovered when we went live, the JSON data in live is slightly different than the test JSON. Line item detail in live data starts at entry #2. Entry #1 in live data is some header information. That’ll break your order receiving, order acknowledgement, and order shipping calls.

The biggest problem with Jet though is speed. They only update their portal once per day. Want to know if your price change worked? If your uploaded order is available for purchase? If your quantity change worked? You have to wait 24 hours to see if any of that changes in the portal. Funny, they require us to accept orders within 15 minutes, but only update us once a day.

If that wasn’t bad enough, it can take up to 72 hours for Jet to accept a new item. When you first upload it, Jet puts it in a limbo called “Under Review”. You have to wait and see if it makes it out or not. And, compounding the badness of this, if there is something wrong with what you uploaded, it will sit there in limbo forever. And, even badder badder, there is NO error reporting to tell you what happened. It just sits there. You have to open a ticket with customer support, after waiting 72 hours, and ask why your item, that uploaded just fine in the test system, is being rejected by the live system. Maybe they get back to you in a day or two. Then you fix the problem, upload the item,… and wait another 72+ hours to see if the fix worked.

Update 9/18/17: Consider yourself lucky if Jet updates items in 72 hours. It can be much slower than that. As an example, we have 50,000 items under review. In a week, Jet only got 600 of them to the live status.

Update 10/1/2017: Yep, still about 750 items a week is all they get live. It’s been 8 weeks, we have 6,000 items live. It’ll take a little over 10 years to get all of our items approved by Jet.

Update 10/5/2017: Noticed that all of our Jet orders we’re coming through as FedEx Ground and Standard Shipping. FedEx ground is not a carrier we had specified in their portal. Found it a little hard to believe that no one had requested rushed shipping. Dropped Jet an email. Jet says “Oops”, they were pulling our shipping information incorrectly. They’ll take care of the angry customers that requested expedited shipping but didn’t get it.

Update 11/29/17: The Jet portal continues to be a real mess. It reports that only 26,000 of our 400,000 items are live, but after talking with support and checking, it seems that the majority of our products are live. The portal is just wrong. That’s a problem.

The portal also provides no way to manually mark an order as shipped. We have some rare cases on our side we’re the data is not there, but we need to mark the item as shipped anyways. There is no way to do that in the portal – you have to write code and make an API call.

Portal also provides no way to deal with a return. Again, you have to do an API call. Not a huge problem, but annoying that a user can’t go into their portal and just handle a problem. It requires programming to get the simplest of things done. Even then the portal doesn’t really work. You’ll get a success message from the API call, but the portal will still show the return as incomplete. You can look at the raw data and see that it is complete in the portal, but the portal doesn’t mark it as complete for several minutes.

Advertisements

Walmart API

I’ve been working with a client that wants to sell their products on Walmart.com. Working with the Walmart API has been a real challenge so far.

First off, Walmart provides no test environment. Want to test your item upload process? You upload items to the live site – better hope customers don’t find it and buy it before you’re ready. Want to test your order processing? You’ll have to get on Walmart.com, find one of your items, and place a live order. Who designs a system that doesn’t allow for testing?

Walmart doesn’t allow updating one item via their API. Instead, you have to build a batch of items to update and then send that batch (which they call a “feed”). Walmart has three different feeds for adding/updating an item, updating the quantity, and updating the price. Walmart, however, does not necessarily process those feeds in the order you send them. Have a new item and you want to update the price and quantity? You have to do the add, wait, wait, wait for Walmart to process that feed, and then send the price and quantity feed. Otherwise Walmart might process the price feed first and reject it all because the items don’t yet exist.

Have you managed to work out all your bugs while on a live site? Have you made sure you’re submitting feeds in a timely fashion? Do you think things will now go smoothly? Nope! Walmart might reject your feed for any number of reasons.

Sometimes Walmart just doesn’t feel like processing your feed. No reason or explanation, just rejected:

WallmartError1

Or maybe Walmart will like some of the items in your feed, but won’t like other items. Again, no real reason for this. You can sometimes resubmit the item and Walmart will take it:

walmart errors

Sometimes Walmart rejects your item for unexpected and unexplained reasons:Walmart errors 2

Sometimes Walmart rejects your item because it is feeling a little glitchy:

WalmartErrorGlitch

Sometimes Walmart rejects your item because it is a little busy:

WalmartErrorTimeout

 

Update 8/17/17: One very good thing about Walmart is that their customer support is strong. Emails we send get replies. Issues we bring up have been fixed in a timely manner. So while it was a bumpy road getting here, we’ve gotten all our products on Walmart and things are running smoothly.

Update 9/18/17: Here’s a fun error message:

MaxLengthError

Wonder which field it might be? Contacting support, they say it’s the product title. I replied that it can’t be – we’ve got several thousand items with titles longer than that. They reply, nope, impossible, has to be product title. It ended up being the model number field.

 

Update 10/5/17: Out of the blue, we went from 350k products live on Walmart to 160k live products. After some hectic emails with support, we got a vague “our pricing routines changed” answer. It seems Walmart thinks our prices aren’t competitive. They won’t tell you who they compare to or how much a price difference matters – just that price is a problem.

We spent two weeks looking into it, comparing our prices to Amazon/EBay/etc. Looking at different prices, wondering if Walmart is comparing Apples to Apples.

Then today we’re suddenly back to 350k live products. We didn’t change anything.

Task Scheduler – Cannot Quit Visual Foxpro

I’ve got a new VFP application that we’re running automatically with Task Scheduler in Windows Server 2012. The app is set to run every 20 minutes. Sometimes though the app will run longer than that.

No big problem. I use WBEMScripting.SWBEMLocator to check if the app is already running and just quit if it is.

Two or three times a day though, I’d find my app hanging with a “Cannot Quit Visual Foxpro” message on it. Once I click OK, the app continues running. There’s certainly nothing in the program that would be trying to quit. There wasn’t any thing I could do manually to make it try to quit. Task Scheduler is set to shut it down only if it runs for more than a day.

Not sure what to do, I changed Task Scheduler to instead run a batch file that starts the application rather than just starting the application directly. And… the problem went away. Not sure how, but it seems that Task Scheduler was occasionally trying to quit my app.

DBI Calendar Control

If you attended Southwest Fox 2016, a free copy of DBI’s Calendar Control was part of your goodies bag. I had a user request come in recently that the calendar control was a perfect fit for so had a chance to work with it.

My application has an “Appointment Book” form that shows all of the appointments, events, etc that the client has to deal with. It’s not terribly sophisticated, just listing all the items in date/time order, but it got the job done:

appointmentbook

The problem the client ran into was that the “Intro” items frequently move around when people call to reschedule. They needed a quicker and easier way to change them as well as seeing what is going on over multiple days. This is exactly what the DBI Calendar Control does and I ended up with the form below. Again, not terribly sophisticated, not using a lot of the color/graphic capabilities of the control, but it gets the job done:

schedule

The advantage here for the user is that they can see the days graphically now, can see multiple days at once, and they can drag & drop the appointments to change the day/time of the appointment.

I was impressed with how easily the control was to work with and how smoothly it works with VFP. In 4 hours or so I went through DBI’s samples (in VFP!), created my own form, and did all the code for adding the appointments, allowing for drag & drop, and double-clicking to open an appointment for editing.

Here’s the 1 line of code for adding an appointment to the calendar. The calendar control works on minutes past midnight rather than a time format so the first parameter is converting my VFP time into minutes. The second parameter is end time which I don’t have for my appointments so I default to start time + 30 minutes. The third parameter is the date while the fourth is whatever text you want to show up.

lIndex = thisform.ctxCalendar1.addAppointment(HOUR(ApDate) * 60 + MINUTE(apDate),;
 (HOUR(apDate) + 1) * 60 + MINUTE(apDate) - 30,;
 TTOD(apDate),;
 lText)

Here’s the code to update my data when an appointment is dragged & dropped to a new date/time. This goes into the control’s AfterAppointmentChange event. The control keeps an index number for every added appointment so I just have to take the nIndex that is passed in, find that entry in my data, and then update my data with all of the other passed in parameters for the new date/time:

SELECT CurAppt
LOCATE FOR ctIndex = nIndex
IF !apx("Seek", CurAppt.apID)
 zmsg("o", "Error! Could not locate appointment to update date/time.", "Error!", "!")
else
 * Update the time of the appointment
 SELECT apAll
 LOCAL lDateTime
 lDateTime = dtDate + (nStartTime * 60)
 replace apDate WITH lDateTime
endif

This code adds or removes day columns when the user changes the form width:

 LOCAL lCol
 lCol = INT((thisform.ctxCalendar1.Width - 60) / 150)
 thisform.ctxCalendar1.DayColumns = lCol

 

Really straight-forward code and the control works really smoothly. In the future I might get fancy and add some color-coding for different appointment types or add some graphics.

The ease of use from DBI has me wondering what other controls I might want to incorporate as well.

Stripe: Working with Pagination

Stripe.com has an …interesting… feature where when you ask for a bunch of things – customers, recent charges, etc – it doesn’t return all of them to you, but instead gives you just the first 100. I’d guess it’s a performance issue, but it does mean you have to take a few steps and a few calls when you need all of something.

For example, you can call Stripe to get customers with a call like: /customers. This will only return the first 100 customers to you.

To get the next 100 customers, you have to do a call like /customers?starting_after=<TheIDOfTheLastCustomerYouGotInThePreviousCall>. (And, no, you can’t do customers?starting_after= and leave this blank. Stripe gives you an error for that.)

So to get all your customers, you have to do the first call and then repeatedly do the second call until Stripe tells you there are no more. How do you know when there are no more? One of the last fields Stripe returns to use is “has_more” which is true/false. If there are more records to return, it’ll be true.

And one final gotcha: Stripe doesn’t return a nice neat ID number for those calls. It returns a bunch of data about the customer, and data about their subscription, and data about their plan, and data about their recent charges. So if you’re searching that data looking for the customer ID, you have to make sure you’re looking in the right place and not getting a Subscription ID by mistake. Additionally, there can be multiple has_more entries in there for that extra stuff. Make sure you’re looking at the right one.

The CustomerList function of the StripeX wrapper is a good example of how to make all of this work.

 

Global hgignore

Rick Borup‘s excellent sessions at Southwest Fox has gotten me using Mercurial in my development. One thing Rick didn’t cover (or maybe I just missed it?) is that you can create a global hgignore file in Mercurial. If you’re working with multiple projects, that’s incredibly handy. You setup the global file just once and it’s used for any repository you have.

You’ll find the global file in My Documents and it’s named hgignore_global.txt. Put all of the standard stuff you want ignored – VFP binaries, *.zip, etc. – in this file and then you won’t have to set that up every time you create a new repository. (Rick has provided a good baseline hgignore in previous Mercurial sessions. Doug Hennig provided one as well in his ‘Lessons Learned with Version Control’ session in 2016.)

The global file works in conjunction with the local .hgignore file – Mercurial will ignore anything that is contained in either file when you attempt a commit. So if one project has some special things to ignore, you just add them to the local hgignore.

Lastly, if you’ve got one strange repository that needs to ignore the global hgignore entries? Mercurial can do that as well.

 

AppendXLSX/WorkbookXLSX

Update 11/27/17: I’ve found the WorkBookXLSX library from VFPX to be more stable than AppendXLSX and use that now rather than AppendXLSX. I have found two little gotchas with WorkbookXLS:

  • The table  you’re exporting should not be open
  • The code seems to turn Deleted Off. If you want it on, you should re-set that command after running this.

Old post about the no longer used AppendXLSX:

I’ve been using the AppendXLSX tool to create XLSX files from my Foxpro data. It works great for the most part, but occasionally I find that it builds the XLSX file but it will be a corrupt file with no data in it. After a lot of fiddling, I figure out that more than 1 date field in the file seems to cause the problem.

This quick bit of code will change the data fields in your table into character.

FOR x = 1 TO FCOUNT()
IF TYPE(FIELD(x)) = “D”
ALTER table (ALIAS()) Alter COLUMN (FIELD(x)) C(10)
ENDIF
NEXT

 

(Does anyone know where AppendXLSX comes from? There’s nothing in the comments saying who the author is and may googling didn’t turn up anything. Whoever your are author, thanks a ton for this tool.)