Setting/updating/posting item inventory for 12k+ items progamatically

I’m on an ERP replacement project where they are planning to do aaaaaaall of the Dynamics BC setup and configuration ahead of time and then on the switch-over date they want to do a programmatic carry-over of things like:

  • Item inventory levels (per location and per bin)
  • Status of Purchase Order lines.
  • etc

This question is focused primarily on setting the inventory levels for items.

Assume that on the day, the inventory levels for all items is ZERO (0). There are around 100 stores and over 10,000 unique items: so manually copying over the inventory levels overnight on the day they cut-over isn’t possible.

I can access the item inventory levels in the old system by ODBC, query them and export to CSV or copy into a holding table.

If one did this manually through the system it would be done as though it was a “stock take” process. One would create a bunch of Physical Item Journal entries with positive stock adjustments.

Initially I was asked to write an app that would do the equivalent via the web services, but there aren’t any built-in/standard WSs that can support that. So it’s been recommended that I build a custom API.

I’m staring at the documentation on how to build a custom API Developing a custom API - Business Central | Microsoft Learn, but before I go down that rabbit hole I thought I should reach out to the community to sanity check this approach.

If there is an easier/smarter way I’d rather not waste my employer and client’s time and money writing custom code.

And if this is the only way to do it then any pointers or hints are appreciated so I know I’m heading in the right direction.

Thanks in advance…

Would Rapid Start not work?

Doesn’t look appropriate, no.

You could use the Configuration Package capability to import this data into an Item Journal, then in BC post the Item Journal which will do the positive adjustment to bring the inventory in. Be sure you have all of your Item settings correct, and the initial Unit Cost set properly for the posting. You can do a dry run of this process in a test company/sandbox to understand timeline of the data import and posting process. If you are using advanced warehousing, there will be additional steps to properly update the warehouse ledger.

Based on 100 stores and 10k SKUs, you have the potential for a very high count of lines to put all inventory into the correct locations/bins. You may want to look at using an XML import rather than the configuration packages. This can greatly speed up the speed at which the import happens, but it does require some development to create.

Since this will be a one-time process, you don’t need to build a custom API but rather an XML import (if Config Packages are to slow).

1 Like

Ben,

Thanks for the reply.

If I understand this properly, I would need to figure out the format expected for the XML import, then I would write some custom code to generate that XML file, which would be manually uploaded into BC.

Is that the solution you are describing?

Any pointers on where I would learn about that XML file?

The file is your existing Excel spreadsheet. You are writing the XMLimport logic to cycle through the workbook and know how the columns match to the fields on the table you are importing into. You’ll want the logic to include filling in background supporting tables such as the Item Unit of Measure table.

I’m not a developer, so I would suggest you go to the Microsoft Learn portal or reach out to your local Business Central partner.