DTW Chart of Accounts error

[Former Member]

Hello Exports

Thanks for all that you do for us.

I am have difficulty DTWing new, additional Chart of Account information into an existing database. I have searched the forum and found several posts, but nothing seemed to address my particular issue.

As I understand, CoA information requires only 3 columns: AcctCode or FormatCode, Name and FatherAccountKey. So as a test, I've only included these columns. All of my lines at this point fail with the following error

Enter Valid Code Application-defined or Object-defined error65171

I am wondering if part of the problem is the DTW template for CoA. The template from SAP has the following column headings:

FormatCode.....Name.........FatherAccountKey _FormatCode.....AcctName.....FatherNum_

I've been told by our consultant that the template is incorrect and that the column headings should be identical, i.e.:

FormatCode.....Name.........FatherAccountKey _FormatCode.....Name.........FatherAccountKey_

I have tried to import the data both ways, and they both fail with the same error message.

Again, thanks for everyone's help.

Randy Davis

Denver, CO

[Former Member]

Thanks to everyone who replied with help. I actually spent 3 hours this morning on the phone with Zal Parchem, who helped me figure out the problem. In lieu of points, he requested that I just buy him a beer. Problem is, he'll probably make me buy it in Germany. Now that's an expensive beer!

Solution:

1. I needed to include the Code (AcctCode) field as the first column and fill it ordinally (line 1 = "1", line 2 = "2", etc). DTW will automatically covert this to the proper "_SYS" number.

2. Account segments needed to be set up in the chart of accounts. This one confused me a bit. I already had the CoA segmented. What I didn't do was to set up the new segment code under Admin/Setup/Financials/Account Segmentation. You need to click on the row header of the appropriate segment in order to enter this information.

3. Apparently, the requirements for the import file has changed somewhat: It use to be that both column headings were required to be the same. With the new version of DTW, the templates are formatted with the DTW fields on the first line and the SAP fields on the second line. In older version, you had to have both lines of column headers be the same. Apparently, you can do it either way in this version.

4. The template indicates that several fields require "tNo" or 'tYes" as the only valid values. This causes an error of "Data too long for field". Changing this to "N" or "Y" worked.

5. The template indicates that the field AccountType (ActType) requires "at_Expenses", "at_Revenues" and "at_Other" as the only valid values. This, too, causes an error. Instead, use "E" for Expenses, "I" for Revenues (Sales) and "N" for Other.

Randy Davis

Denver, CO

[Former Member]

Hi Randy,

You need to try add Code column with the same data as FormatCode data.

Thanks,

Gordon

[Former Member]

Hi Randy.....

Better if you create Template from DTW itself it will help you to run the DTW successfully.

When you create the template for COA it arranges the columns in proper manner as you can see the difference in Templates......

Regards,

Rahul

[Former Member]

Thanks for everyone's help, but so far I still get errors.

Rahul, you said to export my own template. It is the same template that comes with DTW. This template has different field names on the top two column headers. But I was told that the column headers need to be the same.

I attempted to import using this template It still does not import. I tried both way: keeping the column headers as created, and also replacing the second column header with the first, so that both are identical. I tried leaving all the columns inlcuded, and also deleting the unused columns.

Suda, when I included the columns that you recommended, my error message changed. Now, when I try to import, it comes back with "Data too large for the field". I get this error message for all lines, not just some of them. Not sure is this is progress or not. I double-checked whether I had the correct data in each column and I believe that I do.

Thanks again for you help. Do you have any other thoughts on this. Tell me what you think.

[Former Member]

HI

If you are using the DTW latest version,then the field name of the table will appear in the second row of each column...

No need to worry on that the difference between them.

Can u post anyone of the lines here?

[former_member583013]

Hi Randy,

Stepping here after a very long time. Hopefully I know this. The statement that the chart of Accounts template expects two rows with the same column name is in fact correct. Actually you need to fill more columns than what you have stated for the import to work.

First and foremost are you trying to import the account headers or the GL Account itself. If you are importing the GL account you need to complete the following columns.

In the Cash Account column you will enter tYES for Cash Accounts such as Cash on Hand, Bank Accounts, etc from the Assets drawer.

AccountType	   ActiveAccount CashAccount FatherAccountKey FormatCode   Name
at_Other           tYES               tYES    Cash and Equiv  111000000000 Bank of America
at_Revenues        tYES               tNO
at_Expenses        tYES               tNO

Please do not delete any columns from the DTW template. You may also refer to this article which I wrote in 2008.

http://wiki.sdn.sap.com/wiki/display/B1/SAPBusinessOne...ToGo-10.DataTransfer+Workbench

Best

Suda