Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

RMS to WooCommerce Inventory updates

Posted on by 350

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

  • vfeinman Profile Picture
    vfeinman 5 on at
    RE: RMS to WooCommerce Inventory updates

    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

  • Suggested answer
    LanceM Profile Picture
    LanceM on at
    RE: RMS to WooCommerce Inventory updates

    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

  • dionhall85 Profile Picture
    dionhall85 350 on at
    RE: RMS to WooCommerce Inventory updates

    Any help on this?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans