Error with upgrade codeunit when changing table's PK length

red49

I have table A-Z. Table A has PK of ID, and all other tables has fields that relates to TableA's ID.

I'm being tasked to do code cleanup, and I need to change the TableA's ID from length 30 to 20. I have done for other table B-Z, together with the upgrade codeunit. But when I try to change for TableA, I get this error:

"The are changes related to the following primary key that can cause data loss in the new table. The changes cannot be handled because the TableUpgradeMode of the TableSyncSetup type function for the changed table is set to Copy, which does not copy data to the new table. To fix this issue, you must change the TableUpgradeMode option to Move, then add C/AL code to an Upgrade type function to handle new table data."

What does the error mean? Do I need to change TableA's upgrade codeunit from TableSyncSetup.Mode::Copy to ::Move? Any guidance?

I'm using Dynamics NAV 2016.

samlieshout

Yes you would need to use Move, a Copy would not work in the case that an ID of length 30 and trying to insert this into the new ID of length 20 would fail. Check out the docs here for more info about Move. In essence you will need to create an upgrade table which is the same as the "old" table and you would need to COPYSTR it back over to the "new" table (assuming you want to truncate).

Copypaste from docs:

This mode uses an upgrade table to store existing data from the business data table. You must create the upgrade table manually in advance before introducing the change. To create the upgrade table, you save the table you are about to change as another table with an ID in the upgrade toolkit range. Then, you remove the C/AL code from the triggers, and clean up the properties referencing other tables without the field specification, because all the data will be moved.

The upgrade table must be identical to the existing business data table, including the primary key and the fields.

When the schema is synchronized, if there is data in the field of business data table that is affected by the table definition change, then the data is deleted from the business data table and moved to the upgrade table. Then, the schema changes are applied to the business data table. When the synchronization is completed, the business data table will be empty but its schema will match the table definition. If there is no data in the field that is affected by the table definition change, then the changes are simply applied to the business data table.

If required, you can then write code to copy necessary data from the upgrade table into the new table.

samlieshout

Yes you would need to use Move, a Copy would not work in the case that an ID of length 30 and trying to insert this into the new ID of length 20 would fail. Check out the docs here for more info about Move. In essence you will need to create an upgrade table which is the same as the "old" table and you would need to COPYSTR it back over to the "new" table (assuming you want to truncate).

Copypaste from docs:

This mode uses an upgrade table to store existing data from the business data table. You must create the upgrade table manually in advance before introducing the change. To create the upgrade table, you save the table you are about to change as another table with an ID in the upgrade toolkit range. Then, you remove the C/AL code from the triggers, and clean up the properties referencing other tables without the field specification, because all the data will be moved.

The upgrade table must be identical to the existing business data table, including the primary key and the fields.

When the schema is synchronized, if there is data in the field of business data table that is affected by the table definition change, then the data is deleted from the business data table and moved to the upgrade table. Then, the schema changes are applied to the business data table. When the synchronization is completed, the business data table will be empty but its schema will match the table definition. If there is no data in the field that is affected by the table definition change, then the changes are simply applied to the business data table.

If required, you can then write code to copy necessary data from the upgrade table into the new table.

gerdhuebner

Never use the "Forced" method in this case. Even if all primary field values have a length, which is not greater than the new field length, all records will be deleted!

rsaritzky

I'm reading about upgrade codeunits. There is a whitepaper from MS that walks through the process:
go.microsoft.com/fwlink/?LinkID=509977

This has a scenario of deleting fields in a table. Once you create the upgrade codeunit, you can open the original table and delete the fields. The copy to the upgrade table will happen automatically.

It then says "The same would also happen if instead of deleting the fields in the table directly you would import a FOB file with another version of the Vehicle table where these fields are absent."

So what about the scenario where you have built the upgrade codeunit and upgrade table and have a new table definition with fields removed, and you are moving this to a copy of the database, e.g. from Development to Test or Test to Production?

Does this mean you need to import the Upgrade codeunit and upgrade table FOB's first. Then, once they are in the database, you can import the new table definition?

Ron