I’ve got a working dataport (used daily), but multinational characters aren’t interpreted correctly. The database (Navision 3.70+SQL) is COLLATE SQL_Latin1_General_CP1_CI_AS The source (VMS) is ISO Latin1. The characters we have trouble with are £ (GB pound) and accented characters. 1. Any suggestions on the best/easiest way to fix the dataport ? 2. How about fixing the data already imported ?
The problem exists in the fact that Navision unfortunately still uses a kind of ASCII interpretation internally. It is easily possible to transfer files between different Navision systems (as long as the same regional settings are used on both clients) but makes it quite difficult to exchange data with other systems. Have you ever thought about using XML for the task ?
XML ? How would I do that ? How would it help ? I think it would be fairly easy to drop the necessary tags into the export, if I knew what they were.
It is not really that easy. Unfortunately Both I mean [;)] Even if you would do a character translation before the export, Navision will translate that character AGAIN when running the dataport. So you need to find the character which is getting interpreted as your Pound sign at the export. So best would be to export all characters from CHR(32) to CHR(250) and try to find out what character is getting exported as pound sign. Also the fin.stx plays a role in this game. And that one is different from country to country [B)] Maybe we get a UNICODE version of Navision at a certain time.
Not sure I’ve been clear here - the dataport is a daily export from our legacy system into Navision. It receives a csv, and presumably makes some assumption about the encoding. ISO Latin1 uses 163 as GB pound, and all the accented characters are documented too. I’ve no idea what SQL_Latin1_General_CP1_CI_AS encoding is, or where it’s documented (assuming that’s what the dataport expects).
What Navision stores in the database and what it displays inside the forms and what it reads from a dataport has nothing to do with the collation used for the SQL server [V] So if you are just importing, then you should copy the suspect character into clipboard and paste in the C/AL editor in the dataport, then you can call CONVERTSTR with the string you want to convert characters in as first parameter, your result from the clipboard as second parameter (of course enclosed by apostrophes) and the character you would be expecting as last character (also enclosed by apostrophes). You can use either the OnAfterImportRecord trigger or the OnBeforeEvaluateField trigger of the field you want to convert in the dataport Fields (point to the field you want to convert and press F9).
C/SIDE uses the OEM code page internally and does many OEM/ANSI conversions at various boundries, e.g: a character entered into Navision is ANSI - it is then immediately converted to OEM but prior to sending to SQL Server is converted to ANSI again (provided that the database collation is not using an OEM code page). And so on… ASCII corresponds to OEM code page 437. Navision does not use this specifically but uses whatever OEM code page is defined by the system regional settings of the computer. So when processing a dataport file, the file must have been written with the same OEM code page as the computer Navision is running on. The SQL collation must use a code page that matches either the Navision client OEM or ANSI code page and Navision will ensure this when you open the database. Your collation is using ANSI 1252 so your Navision clients are also using this ANSI code page; their OEM code page is most likely 437 or 850. There are numerous posts on Navision’s use of OEM, ANSI and regional settings.