Hi, I’m new to Dynamics NAV but have been working with other ERP systems in the past as a support consultant / ERP implementer. I do not have a programming background, however I have a good knowledge of database structure… well enough to get me in trouble anyway…[:)].
I have been able to create dataports to import general journals, this has allowed me to bring in a companies general ledger, debtor and creditor balances, then post within Dynamics NAV to insure the data is populating the correct tables. However, when I import invoice history, I am importing directly to the Sales Invoice (Header/lines) tables. I am worried that this will cause a data integrity issues when reporting on historical (imported) sales as not all tables have been updated (i.e. Value Entry, Item Ledger), and not all fields within the Sales Invoices table have been updated.
Now, here are my questions… should I try and import the data using dataports into each of the individual tables that I believe are updated when standard NAV posts an invoice? This poses the same data intergrity risks, just with more tables involved. Or should I investigate bringing in the data through the sales table and posting in NAV? I’m sure if I had a little bit more knowledge with C/AL code I could use this to my advantage to populate the tables, so am I wasting my time trying to import without NAV programming knowledge? Or the big one…
Should I forget about reporting on historical data?
Any feedback on this matter would be greatly appreciated.
I think your approach is correct. In Navision the only purpose for tables Sales Invoice Header and Sales Invoice Line is printing. Once you have printed an invoice at least once, you are allowed to delete it from the abovesaid tables without a second thought. All other related data are kept.
I would say that if Item Ledger Entry, Value Entry, G/L Ledger Entry, etc. can exist without the related invoice, there’s no reason because an invoice could exist without ILE, VE, and so on. Just make sure that these historical invoices are easily recognized (maybe with a dedicated numbering series) so that you will not pestered by users’ complaints about “why I can’t see anything when I try to navigate this document”. [:D]
First a big welcome to your here in the Dynamics User Group. I look forward to read a little more about you in your introduction post in the Introduction Forum.
Second before you start any data migration project then you must not only ask your customers what data they want migrated, but also What they want to do with this data?
As you very good points out, then it’s not a very easy task to do a fully integrated data migration with Dynamics NAV. And as Anna points out then the Sales Invoice Header and Line tables (as well as the Sales Credit Memo Header/Lines tables + similar for Purchase) are by design not seen as “real” historical data in Dynamics NAV. This is the reason why the system per default let the user delete these records, as long as the invoice has been posted at least one time. So in an “ideal world” I would never even think about importing into these tables.
But in the real world, then it’s often these tables that the real end users are requesting for historical reference purposes. Historical Sales Statistics are not the primary focus for these users, but they are more interested in giving the customer the best service. And for them that’s to be able to quickly lookup the customers old invoices, with the purpose of quickly be able to reorder, sell accescories or do service. And for that purpose the old sales invoice is the best tool. So for many real world implementations I have had the situation, where the customer actually required Historical Statistics for only the last year, but Old Invoices for the last 3-4 years. And when you do this, then you actually don’t need to worry much about data integrity. These tables are not directly integrated with other parts of data in NAV. So basically they are much easier to import that Ledger Entries (which always should go through the Journal Lines as you describe your self.
Thank you Anna, and Erik for your replies. Its nice to get some feedback from experienced NAV consultants. Sorry about the spelling of “InteRgrity” [:$]
Anna, thanks for the vote of confidence, glad to know I’m on the right path with the methodology of bringing in data, and thanks for the tip, I will use a dedicated number series for historical invoices.
Erik, I think you hit the nail on the head when you wrote “What do they want to do with this data?”. The current customers we are working with require historical data for some in depth customer/sales/item analysis reports. Its good to know that I don’t have to worry too much about data integrity issues, I really didn’t like the thought of having to process historical transactions.
Once again thanks for the support, I’ll make sure I post in the introduction forum [:D]
Historical reporting should never be off of the posted document tables, because users can (and will, if it were only by accident) go in and remove documents, rendering your reporting useless. If you want to enable accurate historical reporting, you have to get the data from the ledgers, and you would therefore have to import and post journals for all of the required historical data.
The analysis reports currently designed are getting the data from the “Value Entry” table, what I’m currently testing is updating this table with the sales history, then validating my reports, so far it looks good. The “Sales Invoice Header/Lines” will also be updated but I will not report from these tables, they will be only for navigate purposes as stated by Anna. I would love to be able to import and post required historical data, but I don’t think the customer will be willing to pay for the extra time it will take, I also have concerns over the application of cash receipts to these historical invoices. Another concern is backing out the general ledger transactions associated with the sales that have already been imported, it would be easy enough to balance these through a suspense account but if we have balance differences this will require reconciliation… and hence we have the question of billable time once more…
In theory I believe the best way to get historical transactions in is to import into the Sales Header/Lines and then batch post. This would ensure that all ledgers are correctly updated. But for this job its not practical, I may yet encounter issues that change my mind, and I will be sure to let everyone know how it turns out.
Firstly, I hope when you say Updating the Value Ledger Entries Table", you mean “Create an Item Journal and Post it”. Under no circumstances ever directly import data into any Entry table in Navision, that is a 120% guaranteed road to disaster.
My recommendation would be to generate just Customer Journals (table 81) and Item Journals (table 83) and post them to create your history. As mentioned above NEVER create statistical or historical reporting from Posted Documents, and not because they can be deleted, the reason is because of system performance, which will be very bad.
Creating the Journals is not as hard as you might think. Look at a Historical invoice that you have, and post the Customer amount as a sales journal, and immediately create the next line as a payment to offset it. Set up posting groups so that it all gets posted to the same G/L account, and everythign will be zero. For this I always create a G/L account “Sales History” on the Balance sheet, and post it all there.
For inventory just post two lines, that immediately offset one another. I normally create a new location called Z-HIST and use that. One thing, if your sales reporting does not need cost information, or if you don’t know the cost, then set the Cost of the Postive Journal line to zero, that way there will be no hitting the G/L.
I hope this gets you started, and please ask away, and we will help to get this working for you.
PS dont forget SEARCH http://dynamicsuser.net/search/ and if you find an article that is of help, please reply to it with further questions.
Thanks for the advise, I wont be posting directly to any ledger tables directly. I was planning on seeing if I could post into the Value Entry table, but I now see the error in my ways.
I have so many things to test and learn at the moment [:)] I will surely have to ask I few more questions!