Import of Excel data

kub

Hi,

I'm in a dataconversion period and going life with Navion 4.0 by October 1'st 2005. We are currently running Navision 3.70 and are using the Indsutrial template and these tools to convert all our main data. However, when importing a table with dateformulafields (like table 3), all data are importet except for the dateformula fields.

Does anyone know why Navision (or the Excel Mapping tools) does not import the fields with type dateformula and how do I get these fields form Excel to Navision (could I maybe format these fields different?)

Thanks for any help

Kai

zarryn

The utility also does not support Time or the new DateTime datatypes. Look for this code pattern in all of the objects (codeunits,tables,forms,etc.) related to the utility and add the type you are interested in...this example is from codeunit 8601 in the function 'InsertAllFields'.

Field.SETRANGE(TableNo,MappingHeader."Table no.");
Field.SETRANGE(Field.Enabled,TRUE);
Field.SETRANGE(Field.Class,Field.Class::Normal);
Field.SETFILTER(Field.Type,'%1|%2|%3|%4|%5|%6|%7|%8',
                Field.Type::Text,
                Field.Type::Date,
                Field.Type::Decimal,
                Field.Type::Boolean,
                Field.Type::Code,
                Field.Type::Option,
                Field.Type::Integer,
                Field.Type::BigInteger);
IF Field.FIND('-') THEN

add a |%9 to the setfilter and then add Field.Type::Dateformula to the list.

Field.SETRANGE(TableNo,MappingHeader."Table no.");
Field.SETRANGE(Field.Enabled,TRUE);
Field.SETRANGE(Field.Class,Field.Class::Normal);
Field.SETFILTER(Field.Type,'%1|%2|%3|%4|%5|%6|%7|%8|%9',
                Field.Type::Text,
                Field.Type::Date,
                Field.Type::Decimal,
                Field.Type::Boolean,
                Field.Type::Code,
                Field.Type::Option,
                Field.Type::Integer,
                Field.Type::BigInteger,
                Field.Type::DateFormula);
IF Field.FIND('-') THEN

You will also need to update the case statements in the functions 'AddColumnValue' and 'ImportValue' to support the type you are interested in (also in codeunit 8601).

kub

Thanks, that helped for the first part - however I'm quite new in Navision and have no idea what to program for the dateformula in the case statements. I have tried to re-use some of the syntax from the text and date cases, however this only gave me an error during import, that the dateformula field is not valid.

Anyone ready for help - Thanks in advance

zarryn

This code should work for the 'ImportValue' function in codeunit 8601.

Add the code inside the

CASE UPPERCASE(FORMAT(FieldReference.TYPE)) OF

case statement

  'DATEFORMULA':
    BEGIN
      CASE Type OF
        Type::Column:
          BEGIN
            ValueAsText := FORMAT(XlSheet.Range(Source).Text);
            IF ValueAsText = '0' THEN
              ValueAsText := '';
          END;
        Type::Constant:
          ValueAsText := Source;
      END;

      IF NOT EVALUATE(DateFormulaType,ValueAsText) THEN BEGIN
        ExcelErrorInfo := XlSheet.Name+'/'+Source;
        CleanUpOnError;
        ERROR(Text004,FieldReference.NUMBER,FieldReference.NAME,ExcelErrorInfo,ValueAsText)
      END ELSE
        FieldReference.VALUE := DateFormulaType;
    END;

You will need to add a local variable to the function:

Name: DateFormulaType
DataType: DateFormula

Hope this helps.

kub

Thanks very much, the code works fine now!!!