Sync stock quantities with external database. Products with variants.

Has anyone done this before? I have a client with an apparel store managed by MS CRM or something like that. Basically it's an MS product backed up by Access database. Clothing items have variants (sizes, colours, styles, etc...). Each variant has it's own SKU.

I need to be able to sync the quantity information with the store. My biggest problem now is figuring out how to link up the product in Zen Cart with the product in the database. I can handle all the technical details, I am just unsure about the logic.

Are there some unique identifiers I can use to link the two together?

Thanks!

I can't believe nobody has done this..??

I'm also now in situation where I should sync product quantities with external database.

It's not exactly what you were looking for, but I'm using Easy Populate to import a CSV into my Zen Cart.

You can easily create an CSV output from the MS CRM, but not sure if there is a way to automate the EP import.

Mind you if you are technically inclinded you could look the way they import and you might be able to get some ideas!

Thanks for the suggestions, but I need syncing both ways. I a lot of programming experience and can write anything. I am thinking of just updating the MS CRM database directly. It's a MS SQL database.

moltar:

Thanks for the suggestions, but I need syncing both ways. I a lot of programming experience and can write anything. I am thinking of just updating the MS CRM database directly. It's a MS SQL database.

How did you accomplish the sync? I'm looking into web services to automate this stock sync process. Then I'll have to sync customer data and the such which..should be prone to some fun errors. Anyone with experience in this who might be able to help when I run into problems?

I used Stock With Attributes contribution and modified it to my needs. Basically I created another table that tracked the ID of the product in the external database VS. the ID of the stock_with_attributes table. Then I have a script running every 10 minutes and syncronizes the external database. I only sync one way - update online quantities. If something is sold online, it is adjusted manually in the RMS database.

I know this is old, but just wanted to add my 2 cents.

One word.
"triggers"

as for uid, stock_id + products_id does nicely

You can use the following directions to come up with a process for this:

http://lists.mysql.com/mysql/179199

and some info on how to make the job happen on a set schedule, which applies to all Linux servers, not just Dreamhost:

http://andycroll.com/writing/backing-up-mysql-databases-remotely-using-dreamhosts-cron-and-ssh

If you are syncing with MS-SQL, then I would recommend setting up an intermediary database for just the inventory figures, and syncing that with your MS-SQL database with a tool like Jitterbit or any other database syncing tool, some are cheap or free, some are more money.

We are rigging something up to sync our inventory with Microsoft Accounting 2009 which uses MS-SQL. Microsoft Accounting has been discontinued, and they also discontinued the eBay connector service, so we're working on a connector to download eBay orders as well using their API. If all goes well, we will have inventory sync between eBay, ZenCart and Microsoft Accounting with a little more development.

Tim