Background

Now, we can upload excel to create sales order(s) in background via App “Manage Sales Orders”. The system creates one or multiple sales orders by the data stored in the excel, and after the sales order(s) were created the user can display a log in order to verify the result.

You can refer the steps to import sales orders with this blog ‘Amazing, now you can import lots of sales orders at one time!!!‘.

But sometimes we may face the issue ‘Invalid Length’ on field Request Quantity(Item) after we change the cell format from Text to General or Number in Excel. the Request Quantity we fill in the excel may be normal(not very long, e.g.: 40.861), how does this happy and why?

Reproduce Steps

1. Download Excel Template from App
2. Fill all the mandatory fields value in excel, and then change the number category (via format cell) of field Request Quantity from Text to number, set the request quantity to 40.861
3. Import the excel data, then you can see the error message ‘Invalid Length’ on field Requested Quantity

Why

The reason is that the number show in excel(40.861) different from the data stored store in excel(40.860999999999997). And you can refer the Microsoft explanation: Why does Excel Give Me Seemingly Wrong Answers?

“The IEEE 754 floating-point standard requires that numbers be stored in binary format. This means a conversion must occur before the numbers can be used in calculations. If the number can be represented exactly in floating-point format, then the conversion is exact. If not, then the conversion will result in a rounded value which will represent the original value. Numbers that appear exact in the decimal format may need to be approximated when converted to binary floating-point. For example, the fraction 1/10 can be represented in the decimal format as the rational number 0.1. However, 0.1 cannot be represented precisely in binary floating-point of finite precision. 0.1 becomes the repeating binary decimal 0.0001100110011, where the sequence 1100 repeats infinitely. This number cannot be represented in a finite amount of space. So in Excel, it is rounded down by approximately 2.8E-17 when it is stored.”

How to Fix

Change back the cell format from Number (or General) to Text.