XMLPort - Flat File Import - Header + Line on one line

ccbryan63 · 2012-04-20

Hi folks,

I'm trying to figure out a way to import purchase invoices from a CSV that has header information and line information on the same line, such as

VendorID, VendorInvoice, InvoiceDate, Department, GL Accout, Quantity, Amount

I have set up an XMLport to the point where it will create a Purchase Header record for each line in the CSV, but I'm not sure how to (or if I can) get it to treat certain fields as belonging to the Header and others the line. Also of course the line would need to get the same order number as the header...

And one more thing... say I want to populate a field in the Header or Lines table with a value that isn't in the table... I presume I'd have to do that in code but which trigger (and what code!)?

Thanks for any help,
Chandler

DenSter · 2012-04-20

You would import values into variables instead of fields, and take care of assigning fields in the table of your choice to the value of those variables. The trigger you are looking for is import::OnBeforeInsertRecord.

Play around with it, and see if you can figure it out

ccbryan63 · 2012-04-23

Thank for the reply DenSter. I get how to pick up the flatfile values into variables and then run code to create a new Purchase Line record, for instance, and insert the variables into a new record.

There are a couple of things I'm still missing though: first, I can't figure out how to modify a field in the xmlport table... for instance, I want to set the Document Type to Invoice for each imported row.

At this point in testing (I'm not even worrying about the lines right now, just the header) my XMLport, taking into account fields that don't need inserting into the Purhcase Header) looks like this:

Node Name Node Type Source Type Data Source
Root Element Text <Root>
PurchHdr Element Table <Purchase Header>(Purchase Header)
ColumnNo Element Text <ColumnNo>
VendName Element Text <VendName>
VendID Element Field <Purchase Header>::Buy-from Vendor No.
ThirdParty Element Text <ThirdParty>
InvDate Element Field <Purchase Header>::Posting Date

My flat file looks like this:

,Approved Invoices - Date Range + 3rd Party (AMD) 3/12/12 Thru 3/19/12,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,Law Firm Office, VENDOR ID,If Applicable - 3rd Party Payable To,Invoice Date
18,"Buzzard & Hawke LLP, Nairobi",10000,,3/7/2012
7,Banks & Allen PLLC - Athens,20000,,3/7/2012
8,Banks & Allen PLLC - Athens,20300190,,3/8/2012
12,"Smith & Jones, LLC, Sparta",01254796,,3/9/2012
,Total,,,,,,,,,,,,,,,,,"$35,418.41 ",,,

I figured out how to skip the first three header lines... in PurchHdr - Import::OnAfterInitRecord I do this:

If row < 4 then begin
   row := row + 1; 
   currXMLPort.skip;
end;

I am creating Purchase Header records... but I can't find where to put the code for setting the new record's Document Type. If I refer to 'PurchHdr; or 'Purchase Header' or <Purchase Header> in the code it doesn't recognize it, nor does it like 'Rec'. Creating a separate record variable for Purchase Header doesn't seem to me like it will refer to the proper table. Any thoughts?

Also, I think I will need to grab the Document No. value from the newly created Purchase Header record to put on the lines... that I guess is really the same question as the Document Type: How to refer to the Import table in code?

Thanks again...

DenSter · 2012-04-23

First, you need to flatten your file, so that on each line in your file, you have header fields as well as line fields. Something like this:

HdrField1  HdrField2  LineField1  LineField2  Linefield3

The first header field might have the document number, and the second field the vendor number. The first line field might be the line number, the second line field the item number and the third field the quantity. You would have a file like this one:

INV123  VENDOR-X  10000  1908-S  10
INV123  VENDOR-X  20000  1906-S  6
INV678  VENDOR-Z  10000  LS-81  2

Your XMLPort would have a dataitem for the line table, and instead of field elements, you would use variables to catch the header fields, for instance DocNo and VendorNo. In the Import::OnBeforeInsert trigger, you would then program the header portion something like as follows:

IF NOT (PurchHeader."No." = DocNo) THEN BEGIN
  IF NOT PurchHeader.GET(PurchHeader."Document Type"::Invoice,DocNo) THEN BEGIN
    PurchHeader.INIT;
    PurchHeader."Document Type" := PurchHeader."Document Type"::Invoice;
    PurchHeader."No." := DocNo;
    PurchHeader.VALIDATE("Buy-from Vendor No.",VendorNo);
    PurchHeader.INSERT(TRUE);
  END;
END;

At this point you should have a valid purchase header with document type 'Invoice', and you can start assigning line values, like this:

PurchLine."Document No." := PurchHeader."No.";
PurchLine."Document Type" := PurchHeader."Document Type";

Etcetera.

Try different things, see if you can make it work. Figuring out how to do stuff like this is what it means to be a developer.

DenSter · 2012-04-23

From the C/AL editor, press F5 to open the object browser. On the left side you should find the reference to your dataitem. If the current name is confusing ("Purchase Header", <Purchase Header>, (Purchase Header)... which one to use?!?!?!), try putting silly names in there to figure out which one works.

Put CrazyBryan in the Node name and see if you can use that. Put HelloWorld in the angle brackets and see if that works. Try it with and without the quotation marks. By using unique names like that you should be able to figure out which one you can use in the code.

ccbryan63 · 2012-04-23

Thanks again DenSter. I showed a truncated version of my flat file, because I was trying to break the problem up into sections... first, assign Document Type and figure out how to get the Document No.

Thanks for your patience... I'll keep beating on it.

DenSter · 2012-04-23

Well the invoice number would be in the file, and if it is not in the file, you need to create one from the numbering series. If you don't know how to do that, then open the purchase invoice form, and turn on the debugger. Create an invoice from scratch and observe what happens in the debugger.

Do you have a senior that can show you how to do stuff like this?

DenSter · 2012-04-24

This post had me thinking about how I would approach this problem, and because I had some time last night I played around with an XMLPort to import data from multiple tables in one flat text file. I figured you'd appreciate seeing what I came up with so I put together a YouTube clip about this topic.

Enjoy: http://youtu.be/qNYrmZXUOxE

Fiza · 2024-10-01

Hi DenSter, dont u mind to share how to modify existing purchase line?

DenSter · 2024-10-07

Instead of simply initializing a new record, you'd add some logic to read the records and make changes to those records where needed. You'd probably need to include line numbers in the file to make that easier

XMLPort - Flat File Import - Header + Line on one line

Comments

Categories