Im Looking for a module that will sync our Inventory from RMS with our Online Store which uses WooCommerce. I just want to be able to sync Inventory levels. I have looked at POS-Sync. which showed promise but it never worked and they never responded to e-mails or help tickets. Any help would be great!!! Thanks!
*This post is locked for comments
Lance,
Can you update inventory the other way as well, i.e. can you update Dynamics RMS inventory from WooCommerce or Magento, etc. using the LinkDB method?
Regards,
Victor
I don't use WooCommerce so I'm not familiar with the database structure. However, I'll explain how I synchronize inventory between RMS and my online store so hopefully you can take it from there.
You can either sync data by connecting directly to the mysql database as a linked server or you can export the inventory data into a file that you FTP to your server and then use to update mysql.
You'll need to have database fields in each database which can be used to link items. item.itemlookupcode in RMS is the most logical and hopefully there is a field in WooCommerce in which you can add itemlookupcodes.
Linked server method:
1. create linked server:
www.ideaexcursion.com/.../howto-setup-sql-server-linked-server-to-mysql
www.packtpub.com/.../mysql-linked-server-sql-server-2008
2. use a query like this in SQL Server Management Studio:
UPDATE w
SET w.TempeQty = i.Quantity
FROM OPENQUERY([WEBSHOP], 'SELECT itemlookupcode, quantity_field FROM mysql_database.inventory_table') w
INNER JOIN [RMSdatabase].[dbo].[Item] i WITH (NOLOCK) ON w.itemlookupcode = i.itemlookupcode
WHERE w.TempeQty <> i.Quantity;
3. once you confirm that it works you can create a .bat file to run the query via OSQL or sqlcmd and then set it up as a scheduled task. Here is an example of the contents of the .bat file:
osql -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword < file_containing_sql_query.sql
Data export and FTP method:
1. create a .bat file with a bcp command and FTP command. For example:
bcp "SELECT itemlookupcode, quantity FROM RMSdatabase" queryout c:\inventory_for_mysql.txt -c -k -t, -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword
ncftpput -C -u ftp_user -p ftp_password domain_name c:\inventory_for_mysql.txt c:\inventory_for_mysql.txt
I use ncftp (http://www.ncftp.com/ncftp/) but you can use the ftp command built into Windows. If you have a lot of items and the file is big you can compress it using a 7zip command in the .bat file. Here is an example of what I use:
cd "C:\program files\7-zip"
7z a -tzip "c:\inventory_for_mysql.zip" "c:\inventory_for_mysql.txt"
2. create a new table in mysql that serves as a temporary place to load the inventory data before it's used to update the WooCommerce inventory table
3. Create a shell script to import the data. If using a Linux server here is an example:
#!/bin/sh
mysql temp_database -u mysql_username -ppassword < /path/to/sql/script/inventory_data_import.sql
contents of inventory_data_import.sql:
DELETE FROM temp_database.inventory_table;
LOAD DATA LOCAL INFILE '/path/to/file/inventory_for_mysql.txt' REPLACE INTO TABLE temp_database.inventory_table FIELDS TERMINATED BY '||' LINES TERMINATED BY '\r\n';
Create a cron job for the shell script.
Hopefully this will guide you in the right direction.
Lance
Any help on this?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,900 Super User 2024 Season 2
Martin Dráb 229,297 Most Valuable Professional
nmaenpaa 101,156