Recordref.TRANSFERFIELDS simulation?

Sebastien_Konsbruck

Hi all,

I've created a function that opens two tables by using RecordRef variables, a source and a target table.
I attempt to copy each record in the source table into the target table by applying the same logic than the NAV build-in function TRANSFERFIELDS (unfortunately it does not exist on RecordRef variables). This means I have to simulate a field mapping based on the Field No. property.

By consequence I cannot use the RecordRef.FIELDINDEX function and instead need to use the RecordRef.FIELD...
However, in order to properly insert a record into the target table, I first need to find all the fields of the source record and store them until I can write them (altogether) into the target table.

My problem is the storage of the field values until I have them altogether in between the same loop...
How can I tell NAV that it should search for each field, try to find a correspondence, and then insert a the new record into the target table?

Thanks for helping me out on this

Variables:

DynamicTableSource : RecordRef variable
DynamicTableTarget: RecordRef variable

"Copy Table ID" : a field value of a record that contains the Table No. of the source table as integer
"Target Table ID" : a field value of a record that contains the Table No. of the target table as integer

TableField : a record variable pointing at the virtual "Field" table

DynamicFieldSource : FieldRef variable
DynamicFieldTarget : FieldRef variable

Code:

CLEAR(DynamicTableSource);
CLEAR(DynamicTableTarget);

DynamicTableSource.OPEN("Copy Table ID");
DynamicTableTarget.OPEN("Target Table ID");
DynamicTableTarget.DELETEALL;

IF DynamicTableSource.FINDSET THEN REPEAT
  CLEAR(TableField);
  TableField.SETRANGE(TableNo,Rec."Source Table ID");
  
  IF TableField.FINDSET THEN REPEAT
    DynamicFieldSource := TableField."No.";
    CLEAR(TableField2);
    TableField2.SETRANGE(TableNo,Rec."Target Table ID");
    IF TableField2.FINDSET THEN REPEAT
      DynamicFieldTarget := TableField2."No.";
      
      //make sure whatever I have to do is only done if the Field No. match      
      IF DynamicFieldSource = DynamicFieldTarget THEN BEGIN 
                         
        DynamicFieldRefSource := DynamicTableSource.FIELD(DynamicFieldSource);
        DynamicFieldRefTarget := DynamicTableTarget.FIELD(DynamicFieldTarget); 

        DynamicFieldRefTarget.VALUE := DynamicFieldRefSource.VALUE; 

        //obivously, this cannot work....
        DynamicTableTarget.INSERT;
      END;                                                               
       
     UNTIL TableField2.NEXT = 0;
     END;

  UNTIL TableField.NEXT = 0;
  END;

UNTIL DynamicTableSource.NEXT=0;
END;

lubost

Hi,

1. Tablefield should be filtered only for "normal" class fields
2. Tablefield2 shoul be filtered for field found in Tablefield
3. DynamicTableTarget should be inserted once, then modified in next loop

vaprog

lubost wrote: »

2. Tablefield2 shoul be filtered for field found in Tablefield

Or you can try to GET it.

lubost wrote: »

3. DynamicTableTarget should be inserted once, then modified in next loop

Rather insert it once after you have processed all fields, that is after UNTIL TableField.NEXT = 0;. lubosts approach works only if your primary key consists of the first field exclusively.

Sebastien_Konsbruck wrote:

My problem is the storage of the field values until I have them altogether in between the same loop...

What's the problem? Just store them in RecordRef, just as you would do with a Record variable. After all, you insert the RecordRef, not the FieldRef. Assign all fields, then INSERT or MODIFY. In case you need to RENAME, try DELETE then INSERT if possible. Otherwise, you probably have to assigning all primary key fields fields first, then RENAME, then assign all the other fields, and finally MODIFY.

Sebastien_Konsbruck

Hi both,

Thanks for your replies.

I tried Lubots approach and in fact can confirm, it works only if the PK consists of the first field.
However, it perfectly makes sense to filter the fields before processing.

Concerning the suggestion to simply insert the found record completely: it does not work, as I don't have the same table structure in the target as in the source table. I only want to copy the fields that match each others' field numbers, exactly as the TRANSFERFIELD function would do it.

This is the Source Table:

Field No. + Field Name
***********************
1, Code (Code 20)
2, City (Text 30)
3, Search City (Code 30)
50804, County Description (Text 30)
60327, Territory (Code 10)
60328, Territory Code ID (Code 20)
60329, City Code ID (Code 10)
60330, City Addition (Text 30)

This is the Target Table:

Field No. + Field Name
***********************
1, Code (Code 20)
2, City (Text 30)
3, Search City (Code 30)
4, Country/Region Code (Code 10)
5, County (Text 30)
50804, County Name (Text 50)

Following the logic of TRANSFERFIELDS, only the red fields are copied (as their number and their type are matching). Now, I could probably count the number of times I have looped through the fields of the source table and hard-code variables like "MatchedField1", "MatchedField2", etc. Then I would stop after I have reached the RecordRef.COUNT function result. This just doesn't seem very efficient to me and I am unsure whether it would work.

Many thanks again for sharing your thoughts.

Best regards,

Juha

You already have all the answers you need from lubost and vaprog.
Transfer all fields that should be transferred and then insert the target record.
I would start by finding the fields that can be transferred as it's the same for all records in the table. In below the numbers of the fields to be transferred are saved in a temporary Integer table

PROCEDURE MyTransferFields@1160530000(SourceTableID@1160530000 : Integer;TargetTableID@1160530001 : Integer);
    VAR
      SourceRef@1160530002 : RecordRef;
      TargetRef@1160530003 : RecordRef;
      FldRef@1160530007 : FieldRef;
      FieldsSource@1160530004 : Record 2000000041;
      FieldsTarget@1160530005 : Record 2000000041;
      FieldsNoToTransfer@1160530006 : TEMPORARY Record 2000000026;
    BEGIN
      FieldsSource.SETRANGE(TableNo,SourceTableID);
      FieldsSource.SETRANGE(Class,FieldsSource.Class::Normal);
      FieldsSource.SETRANGE(Enabled,TRUE);
      IF FieldsSource.FINDSET THEN
        REPEAT
          IF FieldsTarget.GET(TargetTableID,FieldsSource."No.") THEN // does the field exists in target table
            IF (FieldsTarget.Class = FieldsSource.Class) AND
               (FieldsTarget.Type = FieldsSource.Type) THEN BEGIN    // do I want to transfer the field. Add more test if needed..
              FieldsNoToTransfer.Number := FieldsSource."No.";       // if so fill the field number into the fieldlist table
              FieldsNoToTransfer.INSERT;
            END;
        UNTIL FieldsSource.NEXT = 0;
      IF FieldsNoToTransfer.ISEMPTY THEN
        EXIT;   // No fields to the transferred

      SourceRef.OPEN(SourceTableID);
      TargetRef.OPEN(TargetTableID);
      IF SourceRef.FINDSET THEN
        REPEAT
          FieldsNoToTransfer.FINDSET;
          REPEAT     // Assign all fields
            FldRef := TargetRef.FIELD(FieldsNoToTransfer.Number);
            FldRef.VALUE := SourceRef.FIELD(FieldsNoToTransfer.Number).VALUE;
          UNTIL FieldsNoToTransfer.NEXT = 0;  
          TargetRef.INSERT;                   // Insert then record
        UNTIL SourceRef.NEXT = 0;
    END;

Sebastien_Konsbruck

Hi,

Thanks for your help. Using the integer table as a temporary variable is an useful idea.
The below part made me understand why.

Juha wrote: »

FldRef := TargetRef.FIELD(FieldsNoToTransfer.Number);
FldRef.VALUE := SourceRef.FIELD(FieldsNoToTransfer.Number).VALUE;

Also, the modifications on filtering and testing suggested by the three of you show an effect: the codeunit runs noticeably faster.

Thanks to everyone.

BR