Quick way to replace many item numbers?

Hi, I’m trying to replace 1500 Item No’s in the Item table. Does anybody know of a quick way to do this without entering these manually. I have been looking at using the dataport, but I’m not sure how these work and if this would actually update all of the related records when you use it. Thanks, Craig

Do these Item already have posted Entries against then? What you are trying to do is rename the item by the sounds of it. Dependant upon if the items have postings against them or not will determine the way in which you must proceed.

Yes, they have postings against them. I am trying to rename the Item No’s in question.

I presume that you have the mappings of the old codes to the new ones? If this is in an external source (excel spreadsheet) then i would use a dataport - but do not import directly in to the item table. You will need to use code to get the desired Item Record and then use the RENAME Function. Item.get(OLDCODE); Item.rename(NEWCODE); for a quick example. You mat also want to use a COMMIT; after every 100 records or so to speed things up a little, and do it while everyone is off the database too.

Sorry, I am quite new to this would you have to import the data from the external spreadsheet into some kind of temp table first. Then, set the fields in the Item table to these values.

You could use a tempary table but what i would do is set up a dataport with say 2 variables OldCode and NewCode both Code 20. in the Dataport select View - Dataport Fields and use your 2 variables instead of fields. (By doing this is makes no difference as to what table you base your dataport on really, so just use item for the sake of it.) Import you spreadsheet of 2 columns into this dataport. Setup another Variable Item2 - Rec - Table 27. Then OnAfterImportRecord use the following if Item2.get(OldCode) then Item2.rename(OldCode); If you do keep at counter OnAfterImportRecord- NoofRecs += 1; then you can also use COMMIT; after every 100 or so records. If NoofRecs = 100 then begin Commit; NoofRecs := 0; End; Is this helping any?

Thanks, this is really helpful I try to do this. Did you mean to re-name with NewCode here?

Hopefully your “old” and “new” numbers are not overlapping ranges ?

yeah sorry - meant to use NewCode for the rename. Also what Thomas mentions could also be a factor, but as you would not be looping through the records in any order, but doing a specific .GET would this cause any problems??? I don’t thinbk so…??

Yes it could cause a problem if the new number of any item is also the old number of another item. This would cause the rename to fail as there is already a record with that primary key.

yeah sorry completely understand where you are coming from with that.

And another one for those who want to put commits every 100 records: IF a record is getting renamed to a new value (let’s say at position 412 in the text file) which is also an old value for an earlier line (let’s say line 233) and somebody interrupts the dataport and starts over again, the system is totally confused and finds record 233 again !!! Best way: Create a new table with three fields: OldNo Code20 NewNo Code20 Renamed Boolean Import into that table later create a CU (or report) looping through this table and set the “Renamed” field to true. Then you can do a commit every 100 records (or even after each record) whithout risking to rename a record twice.

Thanks for this, I’ve managed to implement this now and have tried this on a test database. Does anybody know if there are any issues with the Item history when using the rename function? I need to know this because I have to decide whether to use this on the live system. As far as I can see there will be no problems because it automatically updates the other tables which reference the Item No.

Thank you, i’ll see what my manager thinks and then go ahead with this then.

Be carefull epecially with customizations in the database. There might be fields holding a item number but do not have a table relation to the item table !!! This normally does not happen inside standard Navision tables and fields but I’ve sen quite some additional tables/fields where the programmers forget to set the tablerelation because the field is noneditable anyway and is getting filled by some code rather than by a user.