Using Upgrade Codeunit to move fields from one table to another

rsaritzky

Hi all,

I'm working on a NAV2009 database that has added a "Sales Header 2" table, because due to the limit of 4000 bytes per record, could not add all the custom fields they use (they use Lanham E-Ship and ChargeLogic, which adds about 90 fields to the table before any client-specific custom fields).

The way this was overcome in NAV2009 was to create a "Sales Header 2" table, where they could create additional fields, along with code all over the place to read SalesHeader2 any time SalesHeader was read, and update SalesHeader2 any time SalesHeader was updated. Needless to say, this is a lot of code in a lot of places.

Fast-forward to NAV2013/2015/2016, and the record size is now 8000 bytes, so we now have record space to move the fields from Sales Header 2 back to Sales Header, and remove all that extra code and overhead everywhere.

So, I am looking at the new Upgrade codeunit subtype, and trying to determine if I can use an upgrade codeunit to move these fields. The first step is easy - transferring the data to a Temporary table using the DataUpgradeMgt.SetTableSyncSetup function.

But from that point, the path is unclear (to me). Can I reference a different table in an upgrade codeunit? i.e. my path is
Copy fields from Sales Header 2 -> Temp Table
Create new fields in Sales Header
Copy fields from Temp Table->Sales header

The examples I have found so far talk about changing a field in a table, so the data flows from Table->Temp->back to same table.

The second issue I have is that the fields in Sales Header 2 have different field numbers than they will have when they move to Sales Header. I am inferring from the samples I have seen that the creation of the Temporary table fields is by field number, that is, if you are upgrading only one field, you can make a copy of the original table, then delete all but the field you are changing. I am assuming (but do not know for sure) that the function works similar to TRANSFERFIELDS which references fields by number, not by name.

Does anyone know this? Creating the Temp table with the same field numbers as Sales Header 2 is fine - the trick is getting field No. 16 "Released By" to copy back into the new Sales Header field 50116 "Released By".

I realize that this may in fact not be possible, or require a manually-coded intermediary codeunit to move field numbers. But I'm wondering if anyone has tried anything similar?

Thanks

Ron

DenSter

Why would you use temporary table? You already have a separate table with the data in it. Just add those fields to the Sales Header table and load them up from the SalesHeader2 table. The temporary table is only useful if existing fields in the std table are replaced by other fields. In that case you need to clear them before you can remove the fields, then you add the new fields and load them up from the temp table. In your case you already have the fields in a separate table, so there's no need for the temporary table.

rsaritzky

Hi Daniel,

I was considering (hoping?) to use the performance improvement of doing this on the SQL side. This was implied in articles describing the upgrade codeunit. But you are absolutely correct - I could write a "regular" codeunit/function to copy all the field values into the new fields in Sales Header. I was just exploring this new feature.

But I really appreciate the comment. Thank you!

Ron

DenSter

Oh I wasn't aware that this was done on the SQL side with std upgrade codeunits, that would for sure be much faster than C/AL