I am subscribed to tables from the Data warehouse on an hourly basis : sales orders, sales invoices and credit notes.
The data is kept in a current_sub_DB and when the new data comes in it is exported to Historic_sub_DB. I know with reporting I can send the current data to csv files by means of a subscription for loading into the relevant tables (sales invoice header/ sales invoice lines).
My question is how would you automate the capturing as the csv is generated if at all possible?
Yes we are currently using Nav 2013, the data warehouse is a SQL 2012 system that a contractor maintains.
Our distribution solution used to handle the orders/invoices/credits from clients but we decided to take over as we were being forced to run a day behind.
We are subscribed to the tables indicated so we (manufacturer) can determine lost sales/ track returned goods and so on.
I am just struggling to find a means of automating this import.
Can you describe the current data flow? I mean you receive invoices/orders etc. via your data warehouse? And now you want to import this into NAV. And in NAV you want to automate this?
Well there are several ways of importing orders/invoices into NAV, from base CSV-files, XML-files, using the NAV web services or one of the many EDI add-ons available. Or you could actually read them directly from your SQL database. But none of these solutions are “ready to use”, they all need development or for the EDI-add-on configuration. You have to decide which technology is the best for your organization. I would go for something where I would have the most control and a technology already used in your organization.
Next I would use the Job Queue functionality to schedule the import to happen on exactly the intervals required. Here you’re able to call reports or codeunits, which would activate your import functionality.
The distribution solution receives the order via B2B, it is formatted and placed in their warehouse. The relevant sales and credits are entered as well in respective tables.
Our Subscription is pushed data in batches every hour containing multiple orders/invoices/credits in single tables. This data is the data we wish to have imported to Navision.
It is just a matter of how, as I would like to prevent manual entry - people make mistakes.
Prior to this we would only receive invoice and credit data a day or two later.
Ok, so what you really need is a solution to import files into your system. But also keep a log of which files where imported correctly and which were not?
If you’re not always 100% sure that the content of the files are correctly formatted, that item no.s, descriptions etc. is known. That the customers are correct, either already created or otherwise in the system, or that a new number has been assigned and it needs to be created (and doesn’t fail during creations), then you can “just” import them. But if there is just a little change that not every error in the file has been avoided or already checked for. Well then you need something a little more advanced. I have worked with this and similar requirements many times over the years. And what seemed to be a small project to create a simple import, it always turned into something bigger. The way a systems is doing error handling is the foundation of its success or not. And you so correctly states “people make mistakes”.
And to do this in NAV really requires that you know how to develop in NAV 100%. Designing such a solution is not a job for beginners into NAV development.
Now you didn’t tell us if you normal work is NAV development, if you work with a NAV partner or end-user. If you’re not a NAV professional and have experience with such systems in NAV, then I will recommend that you talk to your partner and have them help you with this one.
I will also recommend that you start looking for an EDI add-on which will allow you to import the files you have. Most EDI solution support import of CSV-files, on top of the normal EDI standard formats like EdiFact etc. I think this way you will get the most for your money and you will be able to configure the EDI solution to do what you need. I would check the market for possible solutions (search for “Microsoft Dynamics Marketplace” to get Microsoft certified solutions). Then I would ask my partner which one they can recommend. Maybe they know one which is not listed at Microsoft, which they know will do the job.
You’re welcome. I think I have heard this story many times. Management goes to the DBA and tell him “please import this data in NAV, sure that you can do that in a few hours!”. They really have no idea what they are talking about. If there really was only one standard for files and data, and everything was designed to be working together, then they would have been correct. But that’s not how the world works. ERP systems are basically small islands. The files and data are the smaller and bigger ferries which is sailing in their routes, knowing that not all ferries can go into the harbor unless the dock is fittet for it.