Generating auto numbers for existing objects

9
Currently, I have got 30 different objects in my applications which I just enriched with the attribute 'MxID' (AutoNumber). How can I create auto numbers for all the objects which already exist in the database (10,000 objects)?
asked
3 answers
6

There are 3 ways how you can do this,
you can execute a SQL query which updates all existing rows from your object.
you can use java to update the nr. use microflow and some additional actions. (I think you will prefer to use microflow)

When you want to update a production environment keep in mind that you have several actions to perform to set the autonumber.

First create the autonr field, but don't set the type of the field to auto number change it to Long. Now you can start building your 'update flow'. The first activity should be a retrieve activity, don't forget to set the sort attribute in this action. You probably want to set the nr in the order you have created the objects, so sort on created date.
The second activity should be a create variable, create a Long variable and initialize is on the number you want to start at.
Then create a loop, in this loop change the object and set the attribute that will become the auto number with the Long variable and commit the object. Change the Long variable, raise the number with 1

Execute this microflow on your database, if the flow is executed successfully you can change the long attribute to an auto number.
The auto number should continue at the highest nr (no guarantees though). If the auto number starts counting at 0 please leave a comment and I will tell you how to fix that.

answered
6

Keep in mind that when you do this and you make a typo this can make your database corrupt. So always execute this action first on a copy and make sure you have a backup!!!
This query only works for Microsoft SQL server, if you execute this query on postgresql you won't be able to create any more objects.

First let the XAS create the autonumber attribute by synchronizing, then execute these two queries after you have change the terms "module$objectname" and "attributename" with the name of the object and attribute it is about. (both names should be lower case)

UPDATE [module$objectname]
SET [attributeName] = [id];

UPDATE [system_cb_attributesequences]
SET [currentvalue] = (SELECT MAX( [currentvalue] ) + 1 FROM [module$objectname] )
WHERE [tablename] = 'module$objectname'
AND [columnname] = 'attributeName';
answered
3

Jasper,

What would be the sql query if we wanted to do this the database (MSSQL) without changing the autonumber to a Long?

Thanks for the help!

answered