Import file and translate data

Business Central/NAV Developers Forum
1

Hello,

Using NAV 4.0 SP 1

I want to import a file from .csv, .txt, etc, that requires use of a translation table. The data in the import file must be changed prior to importing. If the options for the data were small, I would simply use an IF THEN statement, but the possibilities are numerous. How do I call the translation table and match fields from my import data to the translation table? The example in the Application User’s Guide is confusing.

Kind regards,

John

2

is this a custom Translation table? I have no translation table[:|]

Here’s an old download can you use something like this?
http://www.mibuso.com/dlinfo.asp?FileID=204

Or is it some text file you want to test each incoming field against the table and if you find a match you ant the word replaced?
MOre detail if possible, this question is out of the norm for me.

3

Thanks for your reply and interest.

The scenario is that I am using a dataport to export data from one Navision database that I don’t own and using another dataport to import into my Navision database. Each line that I am exporting has a combination of fields, field1 and field2. There are many combinations of data that can go into field1 and field2. Each unique combination will result in a value, field3, that goes into my Navision database. So I need some way to compare the values of field1 to field2 and enter field3 into the dataport. If could do an IF/THEN except that there are many many combinations of fields.

Exampe:

field1: field2: field3

123 321 F101
213 654 F102

Thanks for your help!

John

4

You should data port the raw data into NAV into the new tabels you have craeted. Then write a function to map the data using mapping tables that you create in Navision. Make it a two step process and it will be easy.

5

Thank you.

Is there any documentation specifically regarding the function to do this?

John

6

If I may add, this is a very custom situation to you specifically.
Now, will this translation happen once or is this a routine procedure you have to do periodically?
If it will be used many times you want it to be a good solid solution.

Placing this data into a new table a “holding area” is probably step 1 as David suggests.
it’s a lot easier to deal with data once they are in the same database.
The mapping funtions is on you unless you explain more on how these numbers equal some code.
I have no idea how 123 & 345 = f101 or 425 & 992 = f193 (for example)
is there any rhyme or reason to this? Perhaps translation is the wrong word here.

7

Thanks Harry,

Maybe ‘mapping’ is a better word as I have been reviewing other posts. There is no rhyme or reason for the codes. In theory, this routine (whatever it turns out to be) will happen every day. To explain further, this is what happens currently:

  1. Data are manually entered into G/L by another deptartment into their Navision database.

  2. We run a report on their Navision database and print it.

  3. We then manually enter the G/L data into our Navision database

Obviously this is not efficient use of time and error-prone.

We cannot simply export from their Navision database and import into our Navision database because the other departments doesn’t use ‘Fund Code’. They use two fields, “Project” and “Award”. There are many combinations of Project and Award that will come over in the import. I need some way to say, for every combination of Project and Award, map to a specific Fund Code.

I have been looking at the Excel Mapping Tool but don’t have any documentation on that either.

Thanks for any help or insight.

John

8

This all now makes very good sense. I did a project for a large School once that received a lot of trust sponsors, and it was necessary to manage the funds in a similar way. Eg a person may donate a sum of money that can be used by the school only if it helps people learn the Violin. Another may donate moeny that can be used for sport or music, but not for tenis and odd combinations like this.

We had to actually run two parallel General Ledgers, one that defined the cost structure where funds were being consumed, adn a second where the funds had been allocated originally. then the intersections of the two enables us to report in detail and show that money was spent appropriately. basically we made a complete copy of Table 15 and all its associated functionality, but all transactions remained in Table 17. It was a very simple and neat solution and a very fun project.

The great thing of the dual chart of accounts, is that we didn’t need to map anything, since we just did that at the reporting level.

9

Thanks.

Is this a good option for the Excel Mapping Tool? If so, where can I find documentation on how to use it?

Regards,

John