Trailing spaces on field type CODE?

Joriske
Joriske Member Posts: 7
edited 2012-03-21 in NAV Three Tier
We have a database (codepage Polish_CI_AS).
In the Table [VENDOR], the field "NO_" is setup to be CODE-type.
However, for some reason, the trailing spaces are NOT deleted (as I believe should have happened to fields of this type)

1. How come Navision did not deleted those spaces? (I supose every record has been created using copy/paste)

2. In order to make proper exports using export-to-excel functionality, how can I prevent the situation excel seeing/interpret this spaces as seperators? (At this moment, single table lines where these vendor numbers are used, will be exported to excel as multiple lines - which seems normal to me in this case)
Is there a way to automaticly update ALL the vendor-n° fields (in all diferent tables, so we will not disturb current relationships)?

TIA

Comments

  • Belias
    Belias Member Posts: 2,998
    1) trailing spaces comes probably from some direct write into sql database without going through the client.
    2) :-k the same thing happened to me on "non-Primary key" fields, and i've solved it with a simple VALIDATE(myfield)/MODIFY, if i remember correctly. Your case is more tricky instead...
    try to do a RENAME on one of your vendor codes; if you're lucky, nav will understend what to do on his own...but i'm just guessing, give it a try
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • ichladil
    ichladil Member Posts: 68
    Hello,

    I may just add that I've experienced the same/similar issues in some customers databases. The issue in my case was always caused by Copy & Paste of the Cell value from Excel to NAV. In Excel there has been some trailing new lines that were not trimmed. Actually users didn't insert those records with incorrect new lines in the end "manually" - they have simply filtered for the value they copied from Excel and therefore the new record with the trailing new lines were created. (I should add the comment this was in the Classic client). This way I have ended with -except for whitespace - the same primary keys in Vendor tables which was later causing us the issues in the OLAP cubes as these are trimming the whitespace in the keys nicely ...

    Actually I would prefer for the Code fields all the whitespace to be trimmed from the end, not only the spaces ...

    Regards
    Igor
  • bbrown
    bbrown Member Posts: 3,268
    Step #1:

    Train users not to copy/paste cell values from Excel.
    There are no bugs - only undocumented features.
  • dimus
    dimus Member Posts: 24
    This is certainly a copy from Excel issue.
    In Excel, press F2 while in the cell and highlight the cell content then Ctrl-C.

Categories