How can I renumber many tables containing data via code?

boywonder

Hi

I have a requirement to move a lot of tables from one number range to another (from 50000+ range to 90000+ range for example)...basically I want to do what NAV does in Object Designer when you press F2 on the table ID field, renumber and hit <ENTER> but without the 'Do you want to rename the record prompt' and I want to do this for a lot of tables without asking the user to do it with possible errors.

I thought I might be able to do this with a processing only report and use the system table 'Object' with a RENAME command in the OnAfterGetRecord trigger (filtering the report just on Tables) but I am running into errors when the table has data in.

I did think to try and use RENAME on the type of TableData as well as Table but this doesn't work either :-k

I'm sure this must be possible but stumped as to how I can do this?

PS. I do not want to move the data out of the tables!

Thanks in advance :shock:

garak

if there are no datas in the objects (tables) export the objects as text open with wordpad use Find / Replace (and dont forgett the variables and properties ;-)

Regards

Slawek_Guzek

You can't without moving the data.

Regards,
Slawek

boywonder

You can't without moving the data.

Surely there must be a way after all using F2 in Object Designer does this as I mentioned :-k

Can anyone else confirm/deny this?!

kine

And what you will do with all relations to these renumbered tables?

boywonder

And what you will do with all relations to these renumbered tables?

I haven't given any background info. because there are no others problem - but basically there are 2 live versions of the software so all Forms/Reports/Codeunits etc all run the new range already.

David_Singleton

garak wrote:

if there are no datas in the objects (tables)...

:-k

boywonder wrote:

...I am running into errors when the table has data in.
...

I think its pretty clear that the purpose for doing this is to move the data.

David_Singleton

I think this is a pretty common request, and it looks like you know what you are doing. I think your best solution is to do it in SQL. (IF you are on Native, then move to SQL to convert, then move back again).

There are a number of ways of doing this. In fact I just did it a couple of weeks back (though I did it manually the same theory will apply to doing it automatically).

Keep in mind that SQL does no know the table number it just knows the table NAME. So what I did was detach the NAV object from the SQL object (Set table property to LinkedObject=Yes).

then renumber the objects how ever you like.

Then reattach.

Obviously there are lots of little steps involved as well, but this is a pretty easy solution. In fact I renumbered both table and fields. Note though that it was only one table, and it was more for the fields that I did it this way, but it should work for you also.

Now having said all that, my next question would be "how many tables are there that you can't do it manually. To that I can imagine only that you have multiple databases that need converting. In that case, whilst this method would still work, you may find it easier just to write a script to move the data across.

boywonder

Thanks David for the detailed reply. I did think about using SQL and will do this in future but I actually did this manually in the end because it was only 30 or so tables for only 2 dbs.

One further problem with changing the tables is that FlowFields still point to the old table number so following the renumbering I exported the whole object set as text and re-wrote any old references to the new tables.

I had to renumber some forms as well and again exporting as text and searching for Form.run(OLDNUM and then Form.runmodal(OLDNUM found all the rogue entries.

So all solved and thanks again

David_Singleton

Great to hear that you got it resolved. And thanks for the feedback, its allways good to hear how the story eventually wound out. \:D/