Although the RIM tool was originally designed to assist in the setup of Master data; i.e. Customers, Vendors, Items, etc., the reality is that many are using the Data Migration tool to bring in journal lines to set up beginning balances and to bring over historical data from legacy applications.
With the release of 5.0 SP1, a new data type conversion routine was introduced with codeunit 5302 – Outlook Synch. Type Conversion. There are several known issues with data conversion due to these changes.
All of these issue have been reported to development and are being considered for a fix in a future release of the NAV product.
This post offers some code suggestions and workarounds for the more common and/or critical issues. Many of the suggested workarounds involve one particular codeunit in the Data Migration tool, 8611 – Migration Management, so the following combined change log should be helpful.
This change log applies to 5.0 SP1 application objects ONLY.
Issues addressed by the change log:
1) Overflow on type conversion from Text to Text in Form 8626 when a text field larger than 100 chars is imported.
2) Error on import of fields beginning with a number (i.e. 1099 Code) – The call to member selectSingleNode failed. msxml6.dll returned the following message: Expression must evaluate to a node-set.
3) Imported dates are blank after migration data is applied.
4) Decimal values are converted incorrectly. Ex. – 7,500 becomes 7.5 after the migration data is applied.
5) Negative decimal values are converted incorrectly, resulting in changed values.
6) Error – Expected token ‘EOF’ found ‘$’. Error on fields with $ symbol
7) Error – The expression Text cannot be type-converted to a Boolean value (or Date value). This may occur on Setup Questionnaire, Data Migration or Data Template.
Changes are labeled with the associated number above.
*****Start Code fix*****
Form 8626 Migration Records
—————————
Before:
CODE
{
VAR
MatrixColumnCaptions@1000 : ARRAY [100] OF Text[100];
MigrationData@1001 : Record 8615;
MatrixCellData@1002 : ARRAY [100] OF Text[100];
MigrationColumnField@1004 : ARRAY [100] OF Integer;
MatrixColumnOrdinal@1003 : Integer;
FormCaption@1005 : Text[1024];
TableNo@1006 : Integer;
Text001@1007 : TextConst ‘ENU=%1 value ”%2” does not exist.’;
After:
CODE
{
VAR
MatrixColumnCaptions@1000 : ARRAY [100] OF Text[100];
MigrationData@1001 : Record 8615;
MatrixCellData@1002 : ARRAY [100] OF Text[250]; // #1 Change Text[100] to Text[250]
MigrationColumnField@1004 : ARRAY [100] OF Integer;
MatrixColumnOrdinal@1003 : Integer;
FormCaption@1005 : Text[1024];
TableNo@1006 : Integer;
Text001@1007 : TextConst ‘ENU=%1 value ”%2” does not exist.’;
* XMLport 8610 – Setup DataSchema
———————————
…
NameIn := DELCHR(NameIn,’=’,’Ù”`’);
NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%’,’ ‘),’=’,’ ‘);
NameIn := DELCHR(NameIn,’=’,’ ‘);
EXIT(NameIn);
…
After:
…
NameIn := DELCHR(NameIn,’=’,’Ù”`’);
NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%$’,’ ‘),’=’,’ ‘); // #6 Change line- add $ symbol
NameIn := DELCHR(NameIn,’=’,’ ‘);
IF (NameIn[1] >= ‘0’) AND (NameIn[1] <= ‘9’) THEN // #2 Add line
NameIn := ‘_’ + NameIn; // #2 Add line
EXIT(NameIn);
* Codeunit 8611\Function FieldNodeExists …
——————————————–
Before:
FieldNode := RecordNode.selectSingleNode(FieldNodeName);
IF NOT ISCLEAR(FieldNode) THEN
EXIT(TRUE);
After:
IF (FieldNodeName[1] >= ‘0’) AND (FieldNodeName[1] <= ‘9’) THEN // #2 Add line
FieldNodeName := ‘_’ + FieldNodeName; // #2 Add line
FieldNode := RecordNode.selectSingleNode(FieldNodeName);
IF NOT ISCLEAR(FieldNode) THEN
EXIT(TRUE);
* Codeunit 8611, Function GetElementName
—————————————-
Before:
…
NameIn := DELCHR(NameIn,’=’,’Ù”`’);
NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%’,’ ‘),’=’,’ ‘);
NameIn := DELCHR(NameIn,’=’,’ ‘);
EXIT(NameIn);
…
After:
…
NameIn := DELCHR(NameIn,’=’,’Ù”`’);
NameIn := DELCHR(CONVERTSTR(NameIn,'<>,./\+-&()%$’,’ ‘),’=’,’ ‘); // #6 Change line – add $ symbol
NameIn := DELCHR(NameIn,’=’,’ ‘);
IF (NameIn[1] >= ‘0’) AND (NameIn[1] <= ‘9’) THEN // #2 Add line
NameIn := ‘_’ + NameIn; // #2 Add line
EXIT(NameIn);
* Codeunit 8611, Function ValidateFieldValue
——————————————–
Before:
Field.GET(RecRef.NUMBER,FieldRef.NUMBER);
IF Field.Type <> Field.Type::Option THEN BEGIN
IF Value <> ” THEN
//IF CompanySetupRun THEN // #7 Delete
EVALUATE(FieldRef,Value)
//ELSE // #7 Delete
// FieldRef.VALIDATE(Value); // #7 Delete
END ELSE
IF GetOption(Value,FieldRef.OPTIONCAPTION,OptionAsInteger) THEN
IF CompanySetupRun THEN
FieldRef.VALUE := OptionAsInteger
ELSE
FieldRef.VALIDATE(OptionAsInteger);
IF NOT CompanySetupRun THEN
IF NOT TestRelation(FieldRef) THEN
FieldRef.VALIDATE;
After:
Field.GET(RecRef.NUMBER,FieldRef.NUMBER);
IF Field.Type <> Field.Type::Option THEN BEGIN
IF Value <> ” THEN
EVALUATE(FieldRef,Value)
END ELSE
IF GetOption(Value,FieldRef.OPTIONCAPTION,OptionAsInteger) THEN
IF CompanySetupRun THEN
FieldRef.VALUE := OptionAsInteger
ELSE
FieldRef.VALIDATE(OptionAsInteger);
IF NOT CompanySetupRun THEN
IF NOT TestRelation(FieldRef) THEN
FieldRef.VALIDATE;
* Codeunit 8611, Function ModifyRecordWithOtherFields
—————————————————–
Before:
VAR
MatrixData@1002 : Record 8615;
MigrationTableField@1000 : Record 8616;
Question@1003 : Record 8612;
Field@1007 : Record 2000000041;
MigrationTable@1009 : Record 8613;
DataTemplateHeader@1011 : Record 8618;
QuestionnaireMgt@1006 : Codeunit 8610;
TemplateMgt@1012 : Codeunit 8612;
OSynchTypeConversion@1014 : Codeunit 5302;
FieldRef@1001 : FieldRef;
OptionInt@1008 : Integer;
DateFormula@1010 : DateFormula;
ToValidate@1013 : Boolean;
After:
VAR
MatrixData@1002 : Record 8615;
MigrationTableField@1000 : Record 8616;
Question@1003 : Record 8612;
Field@1007 : Record 2000000041;
MigrationTable@1009 : Record 8613;
DataTemplateHeader@1011 : Record 8618;
QuestionnaireMgt@1006 : Codeunit 8610;
TemplateMgt@1012 : Codeunit 8612;
OSynchTypeConversion@1014 : Codeunit 5302;
FieldRef@1001 : FieldRef;
OptionInt@1008 : Integer;
DateFormula@1010 : DateFormula;
ToValidate@1013 : Boolean;
DateValue@1500000 : Date; // #3 Add
Before:
IF MigrationTableField.FIND(‘-‘) THEN
IF NOT IsKeyField(MigrationTableField.TableID,MigrationTableField.FieldID) THEN BEGIN
FieldRef := RecRef.FIELD(MatrixData.FieldID);
IF CompanySetupRun THEN
ToValidate:= FALSE
ELSE
ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);
OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate)
END;
IF DataTemplateHeader.GET(MigrationTable.”Data Template”) THEN
TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);
After:
IF MigrationTableField.FIND(‘-‘) THEN
IF NOT IsKeyField(MigrationTableField.TableID,MigrationTableField.FieldID) THEN BEGIN
FieldRef := RecRef.FIELD(MatrixData.FieldID);
IF CompanySetupRun THEN
ToValidate:= FALSE
ELSE
ToValidate := MigrationTableField.Validate AND NOT TestRelation(FieldRef);
//OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate) // #3 Delete
Field.GET(RecRef.NUMBER, FieldRef.NUMBER); // #3 Add line
IF Field.Type <> Field.Type::Date THEN // #3 Add line
OSynchTypeConversion.EvaluateTextToFieldRef(MatrixData.Value,FieldRef,ToValidate) // #3 Add line
ELSE BEGIN // #3 Add line
EVALUATE(DateValue, MatrixData.Value); // #3 Add line
FieldRef.VALUE := DateValue; // #3 Add line
END; // #3 Add line
END;
IF DataTemplateHeader.GET(MigrationTable.”Data Template”) THEN
TemplateMgt.UpdateRecord(DataTemplateHeader,RecRef);
* Codeunit 8611, Function ImportSetupDataXML
——————————————–
Before:
IF MigrationData.Value <> ” THEN BEGIN
ConvertXMLDates(FieldRef,MigrationData.Value);
FieldError(MigrationData,EvaluateValue(FieldRef,MigrationData.Value));
IF FORMAT(FieldRef.VALUE) <> ” THEN
IF FORMAT(FieldRef.TYPE) <> ‘Option’ THEN
MigrationData.Value := FORMAT(FieldRef.VALUE)
ELSE
MigrationData.Value := GetOptionString(FieldRef,FORMAT(FieldRef.VALUE));
After:
IF MigrationData.Value <> ” THEN BEGIN
ConvertXMLDates(FieldRef,MigrationData.Value);
FieldError(MigrationData,EvaluateValue(FieldRef,MigrationData.Value));
IF FORMAT(FieldRef.VALUE) <> ” THEN
IF FORMAT(FieldRef.TYPE) <> ‘Option’ THEN BEGIN // #4 Add BEGIN
IF FORMAT(FieldRef.TYPE) <> ‘Decimal’ THEN // #4 Add line
MigrationData.Value := FORMAT(FieldRef.VALUE);
END ELSE // #4 Add END
MigrationData.Value := GetOptionString(FieldRef,FORMAT(FieldRef.VALUE));
* Codeunit 8611, Function ConvertXMLDates
—————————————–
Before:
CASE Field.Type OF
Field.Type::Date:
BEGIN
IF EVALUATE(Date, Value,XMLFormat) THEN BEGIN
Value := FORMAT(Date);
FldRef.VALUE := Date;
END;
END;
After:
CASE Field.Type OF
Field.Type::Decimal: // # 4 Add line
BEGIN // # 4 Add line
IF EVALUATE(Decimal,Value) THEN BEGIN // # 4 Add line
Value := FORMAT(Decimal,0,XMLFormat); // # 4 Add line
FldRef.VALUE := Decimal; // # 4 Add line
END; // # 4 Add line
END; // # 4 Add line
Field.Type::Date:
BEGIN
IF EVALUATE(Date, Value,XMLFormat) THEN BEGIN
Value := FORMAT(Date);
FldRef.VALUE := Date;
END;
END;
* Codeunit 5302, Function TextToDecimal
—————————————-
Before:
InputText := CONVERTSTR(InputText,’.’,’,’);
IF STRPOS(InputText,’,’) = 0 THEN
EXIT;
PartArray[1] := GetSubStrByNo(1,InputText);
After:
InputText := CONVERTSTR(InputText,’.’,’,’);
IF STRPOS(InputText,’,’) = 0 THEN BEGIN // # 4 Add BEGIN
IsConverted := TextToInteger(InputText,IntegeralPart); // # 4 Add line
IF IsConverted THEN // # 4 Add line
DecVar := IntegeralPart; // # 4 Add line
EXIT;
END; // # Add END
PartArray[1] := GetSubStrByNo(1,InputText);
Before:
DecVar := IntegeralPart + (FractionalPart / POWER(10,STRLEN(PartArray[2])));
After:
IF STRPOS(InputText,’-‘) = 0 THEN // # 5 Add line
Sign := 1 // # 5 Add line
ELSE // # 5 Add line
Sign := -1; // # 5 Add line
DecVar := (Sign * (ABS(IntegeralPart) + (FractionalPart / POWER(10,STRLEN(PartArray[2]))))); // #5 Change line
*****End Code fix*****
Laura K. Lake (lalake)
Microsoft Dynamics NA
Microsoft Customer Service and Support (CSS) North America