Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015 Part 3

This is the third part of the series of “Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015”

The previous articles can be found here:
Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015
Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015 Part 2

This time we would like to talk about a message which could occur during the upgrade to Dynamics NAV 2017:

“UPG Item does not exist”

This error shows up like this:

SessionId: xx
CodeunitId: 104xxx (depending on the version)
FunctionName: UpdateItem
CompanyName: CRONUSxxxx
StartTime: xxxxx
Duration:
State: FailedPendingResume
Error: The UPG Item does not exist. Identification fields and values: No.=’xxx’

Anyone who worked a bit with upgrading probably knows this message, or have faced it once or twice. Now it is time to explain what is going on and how we can resolve it.

All the tables starting with the name UPG* are auxiliary tables and part of the Upgrade Toolkit.

These tables are there to help the upgrade logic and acting as temporary storage while running the upgrade procedures.

They are a part of the upgrade process, and they depend on the localization of your database and the upgrade toolkit you wish to use for your upgrade.

When you import the Upgrade Toolkit objects, you will see that a bunch of UPG tables are created, such as here in Upgrade900100.DE.fob:

Normally you start with importing these objects from your Upgrade Toolkit and after that you do the table schema synchronization.

Once these tables are synchronized, the table should physically exist on SQL Server.

After that you normally proceed with your data upgrade and now you normally see that message.

The data in TAB104092 is important for your upgrade codeunits – the explanation can be found in codeunit 104075, as an example:

COD104075:

[TableSyncSetup] GetTableSyncSetupW1(VAR TableSynchSetup : Record “Table Synch. Setup”)
// The purpose of this method is to define how old and new tables will be available for dataupgrade
// The method is called at a point in time where schema changes have not yet been synchronized to
// the database so tables except virtual tables cannot be accessed
// TableSynchSetup.”Table ID”:
// Id of the table with schema changes (i.e the modified table).
// TableSynchSetup.”Upgrade Table ID”:
// Id of table where old data will be available in case the selected TableSynchSetup.Mode option is one of Copy or Move , otherwise 0
// TableSynchSetup.Mode:
// An option indicating how the data will be handled during synchronization
// Check: Synchronize without saving data in the upgrade table, fails if there is data in the modified field/table
// Copy: Synchronize with saving data in the upgrade table, the modified table contains data in matching fields
// Move: Synchronize with moving the data in the upgrade table,the changed table is empty; the upgrade logic is handled only by application code
// Force: Synchronize without saving data in the upgrade table, disregard if there is data in the modified field/table
// Examples:
// DataUpgradeMgt.SetTableSyncSetup(DATABASE::”Sales Header”,DATABASE::”UPG Sales Header”,TableSynchSetup.Mode::Copy);

…………
DataUpgradeMgt.SetTableSyncSetup(DATABASE::Item,DATABASE::Table104092,TableSynchSetup.Mode::Copy);
…………

The question here is, if it plays a role if your table is empty or not.

If we follow the data Upgrade step from this known KB article: https://msdn.microsoft.com/en-us/dynamics-nav/upgrading-the-data

Step 10:
##
Task 10: Run the schema synchronization to synchronize the new tables
Similar to task 8, to publish the data schema changes of the newly imported tables to the SQL tables, run the Sync. Schema For All Tables – With Validation option from the development environment or run the Sync-NavTenant cmdlet from the Microsoft Dynamics NAV 2017 Administration Shell.
##

It is very clearly stated, that the schema sync should be executed with validation. This step of synchronizing the schema can be that key for this message.

Usually to figure this out, before you run the sync with Validation or with Force, you start with running Sync.– mode Checkonly, just to understand how the situation after the table synch looks like.

The result can looks like the following result.

Sync-NAVTenant DynamicsNav100 -Mode CheckOnly
Sync-NAVTenant : The schema synchronization may result in deleted data. The following destructive changes were detected:

Table: 18, Customer
 Field: 5001900, No. Entries for Avis: Deleted
 Field: 5055250, Liq. Payment Terms Code: Deleted
 Field: 5157970, Electronic Document Dispatch: Deleted
 Table: 23, Vendor
 Field: 5001900, No. Entries for Avis: Deleted
 Field: 5001901, Direction Code: Deleted
 Field: 5001902, Payment Type: Deleted
 Field: 5055250, Liq. Payment Terms Code: Deleted
 Field: 5157970, Electronic Document Dispatch: Deleted
 Table: 38, Purchase Header
 Field: 99008500, Date Received: Deleted
 Field: 99008501, Time Received: Deleted
 Field: 99008504, BizTalk Purchase Quote: Deleted
 Field: 99008505, BizTalk Purch. Order Cnfmn.: Deleted
 Field: 99008506, BizTalk Purchase Invoice: Deleted
 Field: 99008507, BizTalk Purchase Receipt: Deleted
 Field: 99008508, BizTalk Purchase Credit Memo: Deleted
 Field: 99008509, Date Sent: Deleted
 Field: 99008510, Time Sent: Deleted
 Field: 99008511, BizTalk Request for Purch. Qte: Deleted
 Field: 99008512, BizTalk Purchase Order: Deleted
 Field: 99008520, Vendor Quote No.: Deleted
 Field: 99008521, BizTalk Document Sent: Deleted
 Table: 79, Company Information
 Field: 50000, Ort für Signatur: Length reduced
 Field: 50001, GSF: Length reduced
 Field: 50002, HRB: Length reduced
 Field: 60003, IBAN 2: Length reduced
 Field: 60008, IBAN 3: Length reduced
 Field: 60013, IBAN 4: Length reduced
 Field: 70300, Path scanned Purch. Doc.: Length reduced
 Table: 91, User Setup
 Field: 5310951, CrefoDefaultCustRefConsumer: Length reduced
 Table: 110, Sales Shipment Header
 Field: 5900, Service Mgt. Document: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 99008509, Date Sent: Deleted
 Field: 99008510, Time Sent: Deleted
 Field: 99008515, BizTalk Shipment Notification: Deleted
 Field: 99008519, Customer Order No.: Deleted
 Field: 99008521, BizTalk Document Sent: Deleted
 Table: 120, Purch. Rcpt. Header
 Field: 99008500, Date Received: Deleted
 Field: 99008501, Time Received: Deleted
 Field: 99008507, BizTalk Purchase Receipt: Deleted
 Table: 204, Unit of Measure
 Field: 60000, Inventory Unit PV: Deleted
 Table: 295, Reminder Header
 Field: 5157970, Electronic Document Dispatch: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 5157972, Send As Copy: Deleted
 Table: 297, Issued Reminder Header
 Field: 5157970, Electronic Document Dispatch: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 5157972, Send As Copy: Deleted
 Table: 5107, Sales Header Archive
 Field: 5158202, Archiv DocID: Length reduced
 Field: 5157970, Electronic Document Dispatch: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 5157972, Send As Copy: Deleted
 Field: 99008500, Date Received: Deleted
 Field: 99008501, Time Received: Deleted
 Field: 99008502, BizTalk Request for Sales Qte.: Deleted
 Field: 99008503, BizTalk Sales Order: Deleted
 Field: 99008509, Date Sent: Deleted
 Field: 99008510, Time Sent: Deleted
 Field: 99008513, BizTalk Sales Quote: Deleted
 Field: 99008514, BizTalk Sales Order Cnfmn.: Deleted
 Field: 99008518, Customer Quote No.: Deleted
 Field: 99008519, Customer Order No.: Deleted
 Field: 99008521, BizTalk Document Sent: Deleted
 Table: 5109, Purchase Header Archive
 Field: 99008500, Date Received: Deleted
 Field: 99008501, Time Received: Deleted
 Field: 99008504, BizTalk Purchase Quote: Deleted
 Field: 99008505, BizTalk Purch. Order Cnfmn.: Deleted
 Field: 99008506, BizTalk Purchase Invoice: Deleted
 Field: 99008507, BizTalk Purchase Receipt: Deleted
 Field: 99008508, BizTalk Purchase Credit Memo: Deleted
 Field: 99008509, Date Sent: Deleted
 Field: 99008510, Time Sent: Deleted
 Field: 99008511, BizTalk Request for Purch. Qte: Deleted
 Field: 99008512, BizTalk Purchase Order: Deleted
 Field: 99008520, Vendor Quote No.: Deleted
 Field: 99008521, BizTalk Document Sent: Deleted
 Table: 5900, Service Header
 Field: 5157970, Electronic Document Dispatch: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 5157972, Send As Copy: Deleted
 Table: 5992, Service Invoice Header
 Field: 5157970, Electronic Document Dispatch: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 5157972, Send As Copy: Deleted
 Table: 5994, Service Cr.Memo Header
 Field: 5157970, Electronic Document Dispatch: Deleted
 Field: 5157971, Bill-to E-Mail: Deleted
 Field: 5157972, Send As Copy: Deleted
 Table: 50001, Customizing Setup
 Field: 1040, Shipping Date: Deleted
 Field: 1050, Edit Post. Groups in Gen. Jnl.: Deleted
 Table: 51000, External Entries Setup
 Field: 1, Key: Data type changed
 Table: 75400, Mail Batch
 Field: 50000, Sofortversand: Deleted
 Field: 50001, mit Signatur: Deleted
 Field: 50003, Erstell-Datum: Deleted
 Field: 50004, Sonderbehandlung bei info@: Deleted
 Table: 75401, Mail Batch Line
 Field: 30, CI Name: Length reduced
 Field: 50000, Segment No.: Deleted
 Field: 50001, Salesperson Code: Deleted
 Field: 50002, Suchbegriff Unternehmen: Deleted
 Field: 50003, Contact Company No.: Deleted
 Field: 50004, zHd: Deleted
 Table: 75402, MailIT Setup
 Field: 50000, Quote Report ID: Deleted
 Field: 50002, Order Report ID: Deleted
 Field: 50004, Abnahme Report ID: Deleted
 Field: 50006, Bestellung Report ID: Deleted
 Table: 75403, MailIT Report
 Field: 1, ID: Data type changed
 Field: 3, User ID: Deleted
 Field: 2, Caption: Deleted
 Table: 5157823, Reserved 5157823
 Field: 1, Dummy: Data type changed
 Table: 5157825, Reserved 5157825
 Field: 1, Dummy: Removed from primary key - Data type changed
 Table: 5157917, Reserved 5157917
 Field: 1, Dummy: Data type changed
 Table: 5158202, Document Archive Setup
 Field: 22, Sales Process Nos.: Length reduced
 Field: 23, Purchase Process Nos.: Length reduced
 Table: 5158221, Invoice Monitor Setup
 Field: 2, Default Journal Template Name: Length reduced
 Field: 3, Default Journal Batch Name: Length reduced
 Table: 5158225, DropZone Lookup Values
 Field: 1, Archive Document Type: Data type changed
 Field: 3, Document Type: Data type changed
 Field: 6, Value: Deleted
 Field: 5, Source Type: Deleted

Please do not consider a table synch with the option force set to true!

This is exactly what we do not want because we do not know exactly what data could be deleted when running the sync with force, and if this could empty any needed tables or any needed auxiliary tables.

Even if the Item table and the UPG Item table are not in the list, we cannot guarantee what will be happening when you run this forced sync, and what relations you have between the tables, either on the standard version or based on any customization.

Before you go further, you must solve the errors in the list, till you are able to run a normal Sync with Validation, only with this you could avoid that any needed tables / auxiliary tables would be emptied.

How can we solve the Errors in the List?

1. Write your own Upgrade codeunit, as explained in CU 104075,to control how Dynamics NAV what to do with the Fields that might be deleted during this Sync.The Concept of UpgradeCodeunits was introduced first time in NAV2015. here is an example of it:
   https://community.dynamics.com/nav/b/navvideos/archive/2014/11/05/how-do-i-synchronize-database-schema-using-upgrade-codeunits-in-microsoft-dynamics-nav-2015
   This is an Example on how to customize CU 104xxx:

   DataUpgradeMgt.SetTableSyncSetup(DATABASE::"G/L Account", DATABASE::"MyUPG G/L Account",TableSynchSetup.Mode::Copy);
    DataUpgradeMgt.SetTableSyncSetup(DATABASE::Customer, DATABASE::"MyUPG Customer",TableSynchSetup.Mode::Copy);
    DataUpgradeMgt.SetTableSyncSetup(DATABASE::Vendor, DATABASE::"MyUPG Vendor",TableSynchSetup.Mode::Copy);

   Here the „MyUPGxxx“ Table is an own newly created Auxiliary Table, that we are going to use to store the data and structure of any table that might be affected if we carry on with a force Sync, The rest of the customization there can be controlled by you, based on the data from that table, and it is also explained in the above link.

2. Another possibility is just to merge these objects with the Cronus from the new version.
   Simply you can take the old object ‚ex. Customer or Vendor table, or Whatever table mentioned in the error list and merge it using a Compare-Merge with the same table from the newer version.
   Then export the result as a fob out of the new version.

Then when you import that fob in your upgraded database, then a sync with validation should not be an issue any more.

Then you carry on with your Data upgrade, and the upgrade runs without issues.

Special Thanks for Duilio Tacconi for his great contribution in the area of schema sync. and data upgrade.

Best regards,
Abdelrahman Erlebach