Resetting a Numeric Field with Property AutoIncrement=YES

ta5 · 2018-04-30

Hi
We have some fields with the Property AutoIncrement=YES. For example used as PK for a log entry. After a while customer can delete the log entries, because they are not used anymore. It would be nice if there is an easy possiblity to reset the Counter to Zero.
One example here
mohana-dynamicsnav.blogspot.ch/2014/03/reset-autoincrement-field-in-nav.html

Afaik this is possible with SQL Command only. Right? Any suggestions or work arounds?
Thx in advance, greatly appreciated.
Thomas

Slawek_Guzek · 2018-04-30

It is possible to reseed it in NAV, but it requires to remove the AutoIncrement property, and then set it back.

Removing AutoIncrement is easy, but in order to put it back the all records needs to have a value of zero in the field which is supposed to be switched to Autoincrement. This operation is usually a problem as the autoincement field usually serves as PK for the table, so cannot be simply cleared.

If you don't have any data in the table then it is easy, you just take it off, save the table, put back Autoincrement =Yes, Save the table again - job done.

If you have already a data in the table and the table is PK is the Autoincrement field then yo cannot simlpy clear it across the board. You need to make some other field a PK temporarily, so you can clear the original field. The new temporary PK could be some existing field if you are sure that all values are unique. If you are not sure then the easiest thing is to add a new integer field, make it AutoIncrement=Yes, and SQL will populate it with unique values itself.

Then you can switch PK to this new field, clear the old one, or even remove it from the table. Since you cannot have two Autoincrement fields you have remove the Autoincrement from the new temporary field (this is OK, it does not affect existing data).

Then set the Autoincrement =yes back on the original field or re-add it - SQL will populate it from the seed value (default 1). Then you can change the PK back to the original field and remove the temporary one.

It is a cumbersome operation, I'd say if you have direct access to the SQL database don't bother yourself with playing with NAV fields, just use SQL.

As @NavNab mentioned you can enforce any value into the AutoIncrement field - but effectivley you need to be a member of db_owner in order to go this, and it does not reseed the autoincrement value

If your question is "is it possible to reseed the table using C/AL code" then the answer is generally no.

Slawek_Guzek · 2018-04-30

SQL: DBCC CHECKIDENT table_name RESEED new_reseed_value
NAV:
1. Edit table, take off Autoincrement=Yes
2. Save table
3. Edit table, add new field with Autoincrement=Yes, and make it PK
4. Save table
5. Clear all values to 0 in the original field
6. Edit table, Autoincrement=No on artificial feld, save table
7. set Autoincrement=Yes and PK on the original field, remove the artificial field.

ta5 · 2018-04-30

Thanks Slawek
This means, not possible from within NAV, right?

NavNab · 2018-04-30

Hello @ta5

You can do it in NAV atone condition "The user who runs the code should be dbowner on SQL". The code should be like this:

MyTable.SETFILTER(MyAutoIncField, '<>%1', 0); // If needed
IF MyTable.FINDSET THEN
  REPEAT
    CLEAR(MyTable.MyAutoIncField); // Or whatever number you want to assign to your field...
    MyTable.MODIFY(TRUE);
  UNTIL MyTable.NEXT = 0;

https://docs.microsoft.com/en-us/dynamics-nav/autoincrement-property

Slawek_Guzek · 2018-04-30

It is possible to reseed it in NAV, but it requires to remove the AutoIncrement property, and then set it back.

Removing AutoIncrement is easy, but in order to put it back the all records needs to have a value of zero in the field which is supposed to be switched to Autoincrement. This operation is usually a problem as the autoincement field usually serves as PK for the table, so cannot be simply cleared.

If you don't have any data in the table then it is easy, you just take it off, save the table, put back Autoincrement =Yes, Save the table again - job done.

If you have already a data in the table and the table is PK is the Autoincrement field then yo cannot simlpy clear it across the board. You need to make some other field a PK temporarily, so you can clear the original field. The new temporary PK could be some existing field if you are sure that all values are unique. If you are not sure then the easiest thing is to add a new integer field, make it AutoIncrement=Yes, and SQL will populate it with unique values itself.

Then you can switch PK to this new field, clear the old one, or even remove it from the table. Since you cannot have two Autoincrement fields you have remove the Autoincrement from the new temporary field (this is OK, it does not affect existing data).

Then set the Autoincrement =yes back on the original field or re-add it - SQL will populate it from the seed value (default 1). Then you can change the PK back to the original field and remove the temporary one.

It is a cumbersome operation, I'd say if you have direct access to the SQL database don't bother yourself with playing with NAV fields, just use SQL.

As @NavNab mentioned you can enforce any value into the AutoIncrement field - but effectivley you need to be a member of db_owner in order to go this, and it does not reseed the autoincrement value

If your question is "is it possible to reseed the table using C/AL code" then the answer is generally no.

ta5 · 2018-05-01

Hi Slawek and NavNab
Thanks for your postings. Ok for the moment, I'll take that into consideration for further proceedings.
Kind regards and thanks again.
Thomas

Resetting a Numeric Field with Property AutoIncrement=YES

Best Answer

Answers

Categories