cancel
Showing results for 
Search instead for 
Did you mean: 

Data Import error - Valuation Method (ODBC -1029)

0 Kudos

Hello all, first post on the SCN. I did some search for similar problems, but nothing seemed to help here.

We are using Business One, two different companies and databases. We were at the time of the year to update the price list, so we used the Administration -> Data Import/Export -> Data Import -> Import from Excel.

One company (US only) imports perfectly, but the other database (Canada + US) gives me the following error message on 91 / 1036 entries.

Row Number xxx : [Items - Valuation Method] , 'Field cannot be updated (ODBC -1029)

Since the errors only appears on one of the two databases, I ruled out that the excel sheet we used to be the problem.

The excel sheet only has 4 columns : The item identification, the price list that it needs to update, the price, and the currency (USD or $ for us).

Neither of the two companies use any valuation method. In fact, neither companies have the field at all in Item Master Data, and its grayed out in the Company Details.

We also made a dump of the entire item database, and compared what items were imported successfully and those that generated the error, and while the column Valuation Method either had nothing or A, there was no pattern to it. Some errors had nothing, some had A in it.

I checked for any other differences between items that were imported and the ones with error, and cannot see any visible differences, so all I can think here is that its either an invisible setting when the item was created, or there's some character that is creating a fault somewhere (like an empty space).

One of the topics found on this error message (ODBC -1029) said that we might need to request a script to fix the database, but that topic was back in 2006, I'm not sure if it still applies, 8 years later.

Essentially I ran out of things to try on my end. Now I can only ask to the experts.

Thank you for your time!

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Looks like we figured it out.

Despite not being able to change them from SAP, we went into the database and added "A" to the Valuation Method of all items. I'm not sure why some of the items were showing as invalid despite already having a "A" in that field, but seems that it did the trick either way.

Thank you for everyone's time!

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Daniel,

What is the valuation method that you're using on that particular item that having a problem? I will suggest to check all item and ensure that those items are having valuation methods.

Thanks and Regards,

Lailus

0 Kudos

The company that has troubles importing does not allow us to change the Valuation Method for each item. The field is missing entirely from the Item Master Data. I included a screenshot.

The field is grayed out in the Company Details, but its set as Moving Average. The other company uses Moving Average by default as well, from the looks of it (the field does appear in Item Master Data, but its grayed out).

We used the same excel sheet to import data in both companies, just need to figure out why its 100% on one side and not on the other side.

Just to be a bit more clear, we cannot select or visualize the Valuation Method on the items that are not getting imported. Its invisible.

When we exported the full list of items, there was no pattern between the items that are not imported and those that are imported properly.

To be clear, the Valuation Method for :

Items that are imported properly are A or blank.

Items that are not imported and generate an error are A or blank.

There is no logic between those that work and those that do not, which is why I can't figure it out on my own.

Thank you!

Former Member
0 Kudos

Hi Daniel,

Have you try to use DTW?  If not, you can try and run it as administrator to see if it's working.

Regards,

Lailus

0 Kudos

We have attempted using DTW, but similar results. The error message we get say

[OITM.EvalSystem] Field cannot be updated (ODBC -1029)

What's confusing is that we are *NOT* attempting to change the Valuation method or EvalSystem at all. The only thing we are attempting is updating the price lists for already existing items.

In DTW, the same items seem to get the error message as using the Data Import.

The importation of the USA side of the database works perfectly, but the CAN side gets error, some 90 for 1000 items. DTW runs into the same issue, it can update most but not all of them. The same items generate errors using the import and DTW.