Increase the size of a primary key???

Hi, One of our customers has some items with numbers exceeding 20 char. We did suggest some solutions, but it seems to be VERY important for our customer to have the item No. field changed (primary key). Does someone has ever tried something like this??? For sure, we would have to change a lot of tables: item ledger entries, sales lines, sales inv. lines, etc., but would we have to change reports and codeunits as well??? I know I’d never try this if I had the choice, but you know… customer is always right!

David, you will have to change reports and codeunits if they contain some variable which gets assigned the primary key field in question. This variable would be too short to contain the new key values after the change, making the report/codeunit crash during execution. Merely printing the field value in a text box within a report section is no problem and does not require any modification. Same for key and filter operations etc. AFAIK, there seems to be some kind of workbench which helps you in this task - try searching the forum, there have been some postings on this topic recently. A customer. [;)]

Hi! Use the “Developers Toolkit” to find out where Field “No.” of Table “Item” is used, and you will see … [;)][}:)] Jörg

Hi David I have done exactly what you are asking. We have a customer who Needed the Item.“No.” to be 50 Chars. The best place to start is with Developers toolkit and search for all links from Item.“No.” Then Links from “No.” of Sales/Purchase Tables, Journal Tables, then the worst bit Global and Local Variables, especially all the ones linked to Dimensions. You need to do this on all table, Code Units and any of the Process only reports. For displaying and reporting. With forms we just changed the ones which had Item.“No.” as one of the Primary/main fields. Any other forms if needed. With reports find out which of the standard reports the user will be using then change those only, i always find that the customer will want customised reports based on existing reports. We did this on 3.10 Objects and this took me about 4 days (Including the boredom/Monotony factor). This was done last September and now we still find the odd global variable which was missed. The moral here is only do it if you have no other choice.

Thank you for your answers, Someone here tried to change the size of the fields in every table, form, and we started with the codeunits, and it seem to me that this is a loooonng process to make sure not to forget anything (Steve tells that he still finds variables 10 months after the implementation!). Also, changing the size of this field everywhere will affect the size of the database… is there a possibility that the performance is affected also?

David So far we haven’t found any problems with performance. The problem with finding variables is you don’t always find the local variables first time, And it is not always possible to test everypart of system (i know we should). I think we found them all now[:)]. I don’t intend doing this again not for a long time espcially if it’s Item.“No.” (Changed Nearly 200 Objects of which over 100 were tables)[:(] Best of Luck

Though I would rather slit my wrists than change the length of a Navision standard field, the actuall changing the field length in the tables really is the easy part, adn can be done in under 3 minutes with a fast computer. Just create a form based on the “Field” table , Mark all the Fields that are based on Item No. (don’t forget the ones that are “No.” such as on the sales line), then just use Search Replace (e.g. 20 → 30). Not that I am saying you should ever do this though. By the way, it is much better to create a new field than to do what you have done. I have only ever changed standard field lengths ONCE for a client in 13 years. Since then I have ALWAYS been able to sell them a solution based on a new field.

Thanks everyone for your advices.

David, I think you misunderstood, these messages were not advice, they were warnings, of why not to do it. [;)]

[:)] I know! I thought at the first time it would not be a good option. With the arguments of everyone, we have been able to convince the customer that it could work in another way, making the item selection on another field than the table’s primary key. By changing the size of the primary key, we would have a bunch of problems, and I think there could be problems with upgrades in the future too !?! So again, thank you all for your “advices”. [;)]


Originally posted by David Singleton
David, I think you misunderstood, these messages were not advice, they were warnings, of why not to do it. [;)]

Well done David. You made a wise decission. The only sad thing, is that your customer will never really know how much you helped them (but you will).