Hi there.
I am trying to import some invoices into A/P and it keeps telling me that my file layout is incorrect. Well, I can't figure out where I'm going wrong so I'm hoping that someone can help me. This is how my file is laid out, and I'm pretty sure I have all of the correct field selections selected. Are there any errors that you notice right off the bat?

H AGAPPE IN 39034 D 1 3020 48.00
H AGAPPE IN 39034 D 3 WATR SW DEMO 800.00
H AGAPPE IN 117470 D 1 3020 108.00
H AGAPPE IN 117470 D 3 WATR SW DEMO 1867.20
H AGAPPE IN 117472 D 1 3020 44.55
H AGAPPE IN 117472 D 3 WATR SW DEMO 776.10

Thanks,
Jean

Further to my first post, the field selections that I have are:

Hdr-Record Type ‘H’
Hdr-Vendor Number
Hdr-Trans Type (string)
Hdr-Invoice Number
Det-Record Type ‘D’
Det-GL Account (3020 - gst)
Det-GL Dept (blank)
Det-Job (WATR)
Det-Phase (SW)
Det-Category (DEMO)
Det-Amount Payable

Maybe this will help??? Hopefully....

Jean

Hi Jean,

In the Import Invoices template for Adagio Payables, do you have the option 'Header fields on each record' selected? With the layout you posted, this would be required - because you have the information such as 'H AGAPPE IN 39034' repeating for both details.

Is this a fixed length format for the file you are importing? The invoice numbers have different lengths (one has 5 digits and the other two have 6 digits). If you don't have the appropriate number of extra spaces for the invoice number, this would mean that the rest of the text on the line would be off by a characters. This isn't a concern if the comma-separated or Excel formats are used when importing.

Some records have 8 pieces of information and others have 10. Presumably 3020 is a GL account code and WATR is a distribution code. You need to put in appropriate spacing to accomodate 'missing' fields - such as not requiring a distribution code for the first line, or not requiring a GL account for the second line. You'll need to investigate whether the import file is OK and matches the template for the Detail information portion.

Yes, I have "Header fields on each record" selected.

I am importing from Excel, so the length of the invoice number shouldn't matter.

Because 3020 is a GL acct code and WATR is a distribution code I'm not quite sure how to line up the information on the spreadsheet. I've looked at the sample data (APINV.xls) but it isn't any help at all.

In Excel my data is laid out as follows:
Line 1:
Column A: H
Column B: AGAPPE
Column C: IN
Column D: 39034
Column E: D
Column F: 1 (Det-Detail Type)
Golumn G: 3020
Column H: (blank)
Column I: (blank)
Column J: 48.00

Line 2:
Column A: H
Column B: AGAPPE
Column C: IN
Column D: 39034
Column E: D
Column F: 3 (Det-Detail Type)
Golumn G: WATR
Column H: SW
Column I: DEMO
Column J: 800.00

I've tried moving the 48.00 on line 1 from Column J to Column H, but that doesn't work either. I'm determined to make this work, although I could've easily just manually entered all of the invoices in the time it's taken me to not figure this out yet. Now it's the principle of it....I must make this work.

Hi Jean,

Thanks for posting the rest of the information - that shows what the problem is. You have 11 fields selected in the import template, but only 10 columns in your Excel file. This doesn't batch and thus gives the invalid layout message. In fact, you should have 12 fields and columns to fit everything.

As identified by your column outlining, column G is being used for both a GL account and Job number - this is not correct. Each column in the Excel spreadsheet must represent a specific field from the import template. Also, you have not selected the 'Det-Detail Type' field in the import template

I believe your template should be: (with matching Excel column letter)
(A)Hdr-Record Type ‘H’
(B)Hdr-Vendor Number
(C)Hdr-Trans Type (string)
(D)Hdr-Invoice Number
(E)Det-Record Type ‘D’
(F)Det-Detail Type
(G)Det-GL Account (3020 - gst)
(H)Det-GL Dept (blank)
(I)Det-Job (WATR)
(J)Det-Phase (SW)
(K)Det-Category (DEMO)
(L)Det-Amount Payable


In Excel the data should be laid out as follows:
Line 1:
Column A: H
Column B: AGAPPE
Column C: IN
Column D: 39034
Column E: D
Column F: 1 (Det-Detail Type)
Column G: 3020
Column H: (blank)
Column I: (blank)
Column J: (blank)
Column K: (blank)
Column L: 48.00

Line 2:
Column A: H
Column B: AGAPPE
Column C: IN
Column D: 39034
Column E: D
Column F: 3 (Det-Detail Type)
Column G: (blank)
Column H: (blank)
Golumn I: WATR
Column J: SW
Column K: DEMO
Column L: 800.00

This should work, and though it may have been slower than manual entry this time, it will be a lot faster the next time!

Yay!! It works! You have made me a very happy woman!
Thank you so so so very much...I'll be able to rest easy this weekend.

Cheers,
Jean