Options

Recordref.TRANSFERFIELDS simulation?

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;

Comments

  • Options
    lubostlubost Member Posts: 618
    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
  • Options
    vaprogvaprog Member Posts: 1,128
    edited 2015-11-10
    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.
    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.
  • Options
    Sebastien_KonsbruckSebastien_Konsbruck Member Posts: 30
    edited 2015-11-09
    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,
  • Options
    JuhaJuha Member Posts: 39
    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;
    
  • Options
    Sebastien_KonsbruckSebastien_Konsbruck Member Posts: 30
    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

Sign In or Register to comment.