Hi guys i want to rename more than 1000 item because i do integration between branches and this integration required to different between codes in main database so that every branch must different to each other.
so that rename these items one by one it will take more time are there are any way by sql to rename all these items by sql query
i write this query
UPDATE dbo.[RAMOI$ITEMS] SET
No_ = ‘RA-’ + No_ WHERE No_ NOT LIKE ‘RA-%’
IT run successfully but in item table but related tables as item ledger entry table not affected i see all tables related it not affects
in above query i need to modify or update item by add RA-
AS example agg become after modify RA-agg
The query above run success but tables related not affected
can any one tell me which tables must updated and affected after item table modified by arrangement.
Note i requested this table because i know some tables as item ledger entry table but others i don’t know so that i ask
SQL is a great tool and very useful, but no so much so for manipulating NAV data in this way.
The cascading update that modifies foreign key values when you change the value of the primary field is managed by the client executable, so you won’t see any NAV code being processed if you were to watch the debugger while renaming an item through the NAV user interface. The way that the NAV client executable knows which fields to update is through the TableRelation property of the foreign key fields. Since the list you’re looking for (all of the tables that include a foreign key to Item.No. field) isn’t available as a standard NAV document (probably because it would be moot if you were to rename records using what MS consider to be “the right way”, and because the list would be obsolete the minute you make your own foreign key fields), you’ll have to make your own list.
I think the easiest way to find all of these foreign key fields would be to export all table objects to text and search for TableRelation property values that include the Item table. This approach isn’t flawless though. You could miss foreign key fields if the TableRelation property value is so long that it causes the expression to wrap to a second or third line in your text file. In that case, you might not find “TableRelation” and “Item” on the same line in your text file. You would also have to deal with false positives - fields that are related to other tables or fields that have “Item” in the name, like “Item Vendor” or “Appl.-to Item Entry” for example. Finally, (there are other issues, but just the last one I’ll mention now) this plan would be pretty time-consuming. There’s no way you could use this method to automatically generate SQL script that would do the cascading update.
Another approach would be to use SQL (or C/AL) to populate an intermediate buffer table that would include the old and new values of Item numbers that you want to change. Then you could write a NAV function that would loop through the buffer table and rename the items in the list. The advantages with this plan would include that you wouldn’t have to worry about maybe missing a foreign key field, you can schedule the process to run during slack times and without user involvement, you can chose to break the job up into small groups of items so the process wouldn’t still be running when you come back to work, etc. If I were doing this project, this is probably the approach I’d use.
SQL (or C/AL) to populate an intermediate buffer table that would include the old and new values of Item numbers that you want to change. Then you could write a NAV function that would loop through the buffer table and rename the items in the list.
Can you show me example from this way above by code or any site if possible please
well this tough for everybody… one possible way is to check the source finder from developers toolkit… by importing all the tables in the database and then check for only table relations rather than variables and code…
this way you will get accurate list of tables referring to item number…
Don’t understand why you want to make so much out of this. I have done this many many times in NAV and a setup of this can be done in 10-15 minutes.
What you need to do is to create a new table. It only needs two fields: “Old Item No.” and “New Item No.”. This takes 2 minutes.
Then you have your user enter the new item numbers, or use the Excel import tool to import them into the table from Excel. This takes maybe 5 minutes.
Then you create a codeunit with code like this:
WITH MyNewTable DO BEGIN
RESET;
FINDSET;
REPEAT
Item.GET("Old Item No.");
Item.RENAME("New Item No.");
UNTIL NEXT = 0;
END;
That’s all it takes. Then you start your codeunit when most users are offline and makes sure that the server is not shut down for backup or maintenance. If you’re afraid that this might happen, you can add another field to your new table “Renamed” (Boolean), and change your code to:
WITH MyNewTable DO BEGIN
RESET;
FINDSET;
REPEAT
Item.GET("Old Item No.");
Item.RENAME("New Item No.");
Renamed := TRUE;
MODIFY;
COMMIT;
UNTIL NEXT = 0;
END;
This way if the server is shut down, or you need to stop the process, if it’s not able to finish before your users are back, then you will know exactly where to start. But I suggest not using this version with the COMMIT as it will cause that the process to take much longer time.