We are running Dynamics NAV on SQL and the Web Store is only part of the business. Its really important that orders are only taken for stock thats available. This is defined in the ERP system. uCommerce/umbraco db and NAV db will be on the same SQL Server
With uCommerce is it possible to - dynamically read quantity available to order from the external SQL database (NAV's) - synchronise Products from external db so changes made in NAV are replicated to uCommerce. This could be another process that runs regularly - at the completion of a sale, uCommerce calls a NAV web service to create the real Sales Order in the warehouse.
We've got many instance of uCommerce running in an integrated scenario like the one you describe. Some run synchronized, some real-time, others are a hybrid of the two. Depends of what you need.
1) Yes. You have full control over the frontend, so you can pull inventory from the ERP real-time and display appropriate widnets and messages to customers based on the inventory levels.
2) Yep. You've got a couple of different approaches here: 1) SSIS integration on the database level, or 2) use the uCommerce API. The former will be the fastest while the latter offers a simpler developer experience. Let me know which route you go down - there's some prelim docs you want to look at if you go for option 2.
3) You have the option of doing this real-time as the order is completed via uCommerce pipelines or synchronized. I personally prefer the sync option as it inherently supports ERP offline scenarios.
1) Given I can access the ERP via SQL directly or a wbe service to obtain the "live" qty available. How exactly would I modify the front end to query and display this qty as a user looks thru the catalog?
2) We will use SSIS. Any tips or examples specifically around products (with multiple variants) and prices?
3) We will try real-time. Do you know if there is a way to fallback to sync if real-time fails?
1) I'd do a piece of .NET code that retrieves a batch of inventory information for a range of products and display that inline using Razor or XSLT. You don't actually have to worry about uCommerce in this instance because it's only UI at this point.
Once that's in place I'd do a new pipeline component for the basket to make sure that items in the cart are checked continually for inventory so you don't inadvertantly sell something that's discontinued.
2) Product information is present in separate diffenet tables:
Product (for products and variants - variants have a ParentProductId set)
ProductDescription for fixed multilingual values
ProductDescriptionProperty for custom multilingual values
ProductProperty for non-multilingual values
PriceGroupPrice for prices
3) There's no fallback strategy built in. You might find the built-in inventory system useful, but you need to do the fallback yourself.
@Luis: By default uCommerce will install into the Umbraco database, so an additional database is not required. However, you can opt to run uCommerce in a separate database if you want to. Just change the connection string for uCommerce in web.config and you're good to go.
Did you end up going down the uCommerce road with NAV as the back end? We are looking to do the exact samething that you describe so would like to know if you ran into any huge issues?
Yes are integrated into NAV - but is a "loose coupling" - umbraco is not reading NAV live but pushes orders live.
We have a sync process that moves items, item categories etc over to ucommerce from NAV (Simple SQL and SSIS stuff) We also sync across images
We added some extra fields into NAV to flag things like what Items are exposed on the web, extra descriptions
At the end of the check out proces we create a Sales Order in NAV and email an order confrimation from NAV. We are using NAV2009 or later so its all done via web services. If the call back to NAV fails the order stays in ucommerce and can be pushed again manually
What about live inventory and ensuring someone doesnt order something that is out of stock? Is that managed by the items (SSIS) sync process you mentioned?
We do calc balance on Sync and use this to mark "Out Stock"
We then do a live query back into NAV looking up onhand, on orders etc to figure out available when displaying items Some of the sites use multiple variants like size and colour so its a bit complex
For B2B sites we don't use uCommerce just Umbraco so Stock is not such an issue, we just want the order
Integration with SQL Based ERP
We are running Dynamics NAV on SQL and the Web Store is only part of the business.
Its really important that orders are only taken for stock thats available. This is defined in the ERP system.
uCommerce/umbraco db and NAV db will be on the same SQL Server
With uCommerce is it possible to
- dynamically read quantity available to order from the external SQL database (NAV's)
- synchronise Products from external db so changes made in NAV are replicated to uCommerce. This could be another process that runs regularly
- at the completion of a sale, uCommerce calls a NAV web service to create the real Sales Order in the warehouse.
Hi Steve,
We've got many instance of uCommerce running in an integrated scenario like the one you describe. Some run synchronized, some real-time, others are a hybrid of the two. Depends of what you need.
1) Yes. You have full control over the frontend, so you can pull inventory from the ERP real-time and display appropriate widnets and messages to customers based on the inventory levels.
2) Yep. You've got a couple of different approaches here: 1) SSIS integration on the database level, or 2) use the uCommerce API. The former will be the fastest while the latter offers a simpler developer experience. Let me know which route you go down - there's some prelim docs you want to look at if you go for option 2.
3) You have the option of doing this real-time as the order is completed via uCommerce pipelines or synchronized. I personally prefer the sync option as it inherently supports ERP offline scenarios.
Hope this helps.
Hi Soren,
1) Given I can access the ERP via SQL directly or a wbe service to obtain the "live" qty available. How exactly would I modify the front end to query and display this qty as a user looks thru the catalog?
2) We will use SSIS. Any tips or examples specifically around products (with multiple variants) and prices?
3) We will try real-time. Do you know if there is a way to fallback to sync if real-time fails?
Steve
would like to know if you need an additional database to add ucommerce the CMS umbraco?
@Steve:
1) I'd do a piece of .NET code that retrieves a batch of inventory information for a range of products and display that inline using Razor or XSLT. You don't actually have to worry about uCommerce in this instance because it's only UI at this point.
Once that's in place I'd do a new pipeline component for the basket to make sure that items in the cart are checked continually for inventory so you don't inadvertantly sell something that's discontinued.
2) Product information is present in separate diffenet tables:
3) There's no fallback strategy built in. You might find the built-in inventory system useful, but you need to do the fallback yourself.
@Luis: By default uCommerce will install into the Umbraco database, so an additional database is not required. However, you can opt to run uCommerce in a separate database if you want to. Just change the connection string for uCommerce in web.config and you're good to go.
@Steve
Did you end up going down the uCommerce road with NAV as the back end? We are looking to do the exact samething that you describe so would like to know if you ran into any huge issues?
Regards,
Matt
Yes are integrated into NAV - but is a "loose coupling" - umbraco is not reading NAV live but pushes orders live.
We have a sync process that moves items, item categories etc over to ucommerce from NAV (Simple SQL and SSIS stuff)
We also sync across images
We added some extra fields into NAV to flag things like what Items are exposed on the web, extra descriptions
At the end of the check out proces we create a Sales Order in NAV and email an order confrimation from NAV.
We are using NAV2009 or later so its all done via web services.
If the call back to NAV fails the order stays in ucommerce and can be pushed again manually
No real issues.
Thanks for the quick response Steve!
What about live inventory and ensuring someone doesnt order something that is out of stock? Is that managed by the items (SSIS) sync process you mentioned?
We do calc balance on Sync and use this to mark "Out Stock"
We then do a live query back into NAV looking up onhand, on orders etc to figure out available when displaying items
Some of the sites use multiple variants like size and colour so its a bit complex
For B2B sites we don't use uCommerce just Umbraco so Stock is not such an issue, we just want the order
is working on a reply...