I am new to dynamicsuser.net and have found the posts quite interested about Navision. Hope someone can have a solution for my query too.
We have a requirement where we need to periodically import invoices from our CRM system to Navision. The CRM system is developed in PHP and MSSQL, hence we have the option to write a script/program to do a direct SQL server to SQL server import OR create a .csv file dump from CRM and write dataports to do the job.
Would appreciate if somone could share their experience with us to sort out the import.
Don’t import data directly in Navision using external programs. Navision business logic won’t be used.
To import to Navision you have many options.
My preferred method is using linked tables. In this way you will share SQL Server tables between servers it’s an easy option.
If you preferred dataport way, It’s the most traditional method.
To import lines / header in Navision in same file you can join header details in same in line.
Thanks for the quick response. Can you kindly elaborate on how to achieve the import using linked tables?
Also, if I wish to go the dataport way initially, what format do you think the datafile should be like. Can you possibly suggest me a format for the file containing invoice headers and lines. Also, if I use dataport how do i do the regular checks for invalid customer and product codes.
We are trying to import the data using dataports and are including three tables within it. Now the problem here is that the dataitemseparator property set as <> for the dataport is not recognized, when we actually run it and it gives and error.
I had to do something like this a few months ago… I had to import customer invoices from other systems.
I think the best way is to create a dataport that inserts records on the Gen. Journal Line table, this invoices (still unregistered) will be available from the General Journals (Financial Managemente → General Journals). At this point you can register this invoices and, if any data is not filled properly, an error would occur.
A recommended suggestion is to create and specific “Batch Name” for this invoices, something like “transfered invoices”, this will be useful if you want to know what customer movs. corresponds to the normal flow of NAV and what to specific movs.
I’m not sure if the dataport I created would be useful for you, but if you need it, tell me your email address and I will send
Hope it helps
Actually we need to import the headers and lines to the Sales Header & Sales Lines table, which then will be posted as a batch in Nav by the accounts person.
Hence, instead of creating two dataports we are trying to use one with multiple tables in it. The individual dataports work fine, but when we try to combine two tables in one and give the dataitem separator, Nav seems to ignore it and tries to add all the rows to the same table.
Ok Hitendra, then I’m afraid it’s not the same case I had before. We only wanted to save a reference, date and amount of the invoice, so my dataport willn’t be useful for you, anyway I programmed a few dataports multi-dataitem and worked fine, I think your problem is in one of this properties:
FieldStartDelimiter
FieldEndDelimiter
FieldSeparator
RecordSeparator
DataItemSeparator
I know I don’t bring you a specific solution :-(, why don’t you try showing us the error displayed and the properties of your dataport-dataitems?
Be lucky
Thanks for the solution, but to be honest, I am bit confused.
Can you please clarify which Invoice import table you are talking about (is there one in Navision) or you want it to be created in our crm.
And also I have figured out the problem we had with the dataport with multiple tables in it, and it’s working well now. The challange now is to schedule it to pick a file from a set location on a daily basis and import it.
Ok. I’m not talking about a new invoice table, if you go to Financial Management → General Ledger → General Journals, and display de “Document Type” options, you will see that an invoice can be registered from this journal. This invoice willn’t be available from the Sales Invoice Header (table 112), but it will be registered in other tables, for example, Cust. Ledger Entry (table 21).
This could be useful if you want to register invoices massivement. A few months ago we used it to register unresolved invoices from our previous system.
To schedule tasks in NAV have a look to Administration → Application setup → Job Queue → Job Queue Entries :-).
Sorry that I was not clear with my original comments.
A new non-standard table that will only be used to store the information that you are importing.
This table can be created in Navision and, if you are using SQL, it would be accessible from other applications.
I choose this construction because you can control the data that comes into Navision (data types, formatting and validation) and subsequently process that data into the “standard” Navision data type, tables and processes (as you have already suggested).
You can re-process the data if necesary.
You have an audit trail of the imported data within Navision.
By using an extra table and some Forms you have greater flexibility, improved validation and an improved user interface.
I think before going much further down this path, take a step back and look at what you are trying to achieve. The most important thing right now is to get the business concept resolved.
You need to look at your NAV system and your CRM system, and work out what it is that you need in NAV.
Do you need for example to be able to reprint invoices in NAV?
Do you just need the financial amounts on the G/L and in receivables?
Do you need to record Inventory transactions?
How often do you need to sync?
What is the maximum down time you can afford?
What is the volume of the Invoices (and lines if applicable?
How do you plan to sync customer numbers and item numbers?
There are many different technologies available, and you need to consider all the above (and more) before making decisions about which tables to write to.
On a technical note: you can only import multiple tables with a dataport by importing records per table. So your example would work with all headers first, and then all lines (e.g. header-header-line-line-line-line). There is no way to import header-line-line-header-line-line.
We have a specific requirement to import the invoice headers and lines and then post them the normal way, as our credit control then uses them to chase and keep a track of payments.
Anyways, we have managed to create a dataport that will import mulitple tables into the Sales Header, Sales Line and Document Dimension table in Nav. Even the posting of those invoices works fine. The only thing I would now need, is to find a way to automate the import.
Thanks for this. We managed to run the dataports using the Job Scheduler.
Also, is there a way of sending email alerts from Navision to an administrator whenever a dataport fails to run? Can you please let me know if any help available?
