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

Comments

• DenSter Member Posts: 8,307

  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

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• ccbryan63 Member Posts: 115

  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...

  0
• DenSter Member Posts: 8,307

  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.

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• DenSter Member Posts: 8,307

  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.

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• ccbryan63 Member Posts: 115

  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.

  0
• DenSter Member Posts: 8,307

  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?

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• DenSter Member Posts: 8,307

  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

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• Fiza Member Posts: 1

  2024-10-01

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

  0
• DenSter Member Posts: 8,307

  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

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0