XMLport basics (Beginner)

boone · 2014-01-11

Hey guys. I'm new to NAV - have only been working with it for 2 months, so try to go easy on me

.

My company is in the process of switching to 2013 R2, and in the process we are changing a lot of the structure and processes that we previously used. One of the things we need to do is import expense details (we receive a | delimited file with 256 columns from Concur) into purchase invoices. I think that the XMLport will be the best way to do this, but I am struggling to get it to work. I've been looking through examples and stuff for a couple days, but I just can't get it hammered out. What do you guys recommend as a starting point for learning XMLports?

This has been the most help to me, so far, but I'm still having trouble translating it to exactly what I need:
https://www.youtube.com/watch?v=qNYrmZX ... e=youtu.be

If you guys could get me started, I think I can run with it. Thanks in advance!

geordie · 2014-01-13

Hi, you can get started taking a look at these links:

- Creating XMLports
- XMLport Fundamentals
- Designing XMLports
- XMLport Generator

Feel free to ask for any further question.

boone · 2014-01-14

Thanks for the help, geordie. I've made some progress, but I'm still having some troubles. My biggest issue was that I didn't realize that to designate the | as the delimiter I needed to put just the "|", I had been using "<|>". Now I'm having a problem where I can't assign one of my incoming fields to the "Posting Description" - it uses the "Invoice <Invoice No.>" instead.
Here's my extremely simple version so far, with Posting Description not functioning properly:

EDIT: This falls under
PurchaseHeader - Import::OnBeforeInsertRecord()
/EDIT

MyPurchaseHeader.INIT;
MyPurchaseHeader."Document Type" := MyPurchaseHeader."Document Type"::Invoice;
MyPurchaseHeader.VALIDATE("Buy-from Vendor No.",VendorNo);
MyPurchaseHeader."Posting Description" := Description;

A couple things that I will need to work out also:
The imported file does not have an Invoice No within it, I want it to use the automatic numbering (which it is), but there will be multiple lines that need to be imported as lines for a single invoice. I think I can adapt DenSter's video to work, if I can use the Posting Description as the check to see if the invoice exists already...
The imported file uses a different employee designation than "Buy from Vendor No." (in my test run, I substituted in some real numbers just to test it) - can I write some code to pull the proper Vendor No using the "Call Sign" (which is a 1:1) before my VALIDATE statement? If so, can somebody help me with just that basic code...

I'm sure I'll continue to have more questions/needs, but I'll ease into it and maybe be able to solve them on my own once I understand the initial questions.

Again, thanks guys...

geordie · 2014-01-15

boone wrote:

Thanks for the help, geordie. I've made some progress, but I'm still having some troubles. My biggest issue was that I didn't realize that to designate the | as the delimiter I needed to put just the "|", I had been using "<|>".

Yes, brackets are used to identify property default values: if you choose a custom value there's no need to use them.

boone wrote:

Now I'm having a problem where I can't assign one of my incoming fields to the "Posting Description" - it uses the "Invoice <Invoice No.>" instead.

This happen because "Posting Description" field is evaluated in OnInit function, called in OnInsert trigger.

boone wrote:

A couple things that I will need to work out also:
The imported file does not have an Invoice No within it, I want it to use the automatic numbering (which it is), but there will be multiple lines that need to be imported as lines for a single invoice. I think I can adapt DenSter's video to work, if I can use the Posting Description as the check to see if the invoice exists already...
The imported file uses a different employee designation than "Buy from Vendor No." (in my test run, I substituted in some real numbers just to test it) - can I write some code to pull the proper Vendor No using the "Call Sign" (which is a 1:1) before my VALIDATE statement? If so, can somebody help me with just that basic code...

Since you need to perform some addition controls on data imported, I suggest to load data in temporary tables (on XMLport table element set Temporary = true) and loop through them in OnPostXMLport trigger.
You can use the following code to arise from:

IF PurchHeaderTemp.FINDSET THEN
  REPEAT
    PurchHeader.RESET;
    PurchHeader.SETRANGE("Posting Description",PurchHeaderTemp."Posting Description");
    IF PurchHeader.FINDFIRST THEN BEGIN
      //Add lines ...
    END ELSE BEGIN
      CLEAR(PurchHeader);
      PurchHeader."Document Type" := PurchHeader."Document Type"::Invoice;
      PurchHeader.INSERT(TRUE);
      PurchHeader.VALIDATE("Buy-from Vendor No.",PurchHeaderTemp."Buy-from Vendor No.");
      PurchHeader."Posting Description" := PurchHeaderTemp."Posting Description";
      PurchHeader.MODIFY;
    END;
  UNTIL PurchHeaderTemp.NEXT = 0;

Hope this helps

boone · 2014-01-16

Thanks again, geordie. I haven't been able to get your new suggestions implemented, or even worked with, yet, but I think it will be a big help. I was suddenly presented with a change of priorities so this original task has been put on the backburner.

Another thing we are needing is a daily update of exchange rates. I know this is a somewhat common thing on here, and I've looked at some of the threads about it. I've come up with 2 options that seem to work best for my situation. The first will be pulling in a csv from Yahoo Finance's API, which has only 3 columns. Name (USD/MYR, USD/GBP, etc.) - Rate - Date. I have gotten this to pull in fine, but from what my boss has told me, we also need the rates in the opposite direction (MYR/USD, GBP/USD, etc.). For some, that will be easily gotten from the API, but, for example, MYR/USD isn't available. Obviously, it is the mathematical inverse of USD/MYR, so I figured I could put in a code to convert that, but I'm thinking I will need to run the conversion and create a second record for each line in the csv, which I don't know how to do. Am I overthinking this and there's is some much simpler method? Is my method fine/easy and I just haven't been able to find the right coding yet?

As always, I appreciate the help!

boone · 2014-01-18

I was able to finish my exchange rate XMLport, but I'm not sure if it is the optimal method. It really shouldn't matter, as it takes no time to run at all and I'll only be importing 5 (at least for the time being) rates per day. But I figured I might as well figure out whether or not my method is a good one or not so that I don't proceed in my endeavors with poor code.

Anyways, I'm downloading this file: http://download.finance.yahoo.com/d/quo ... 1d1&e=.csv
which is just
"USD/MYR",3.2984,"1/18/2014"

forex - Import::OnBeforeInsertRecord()
fromcurrency := COPYSTR(currencies,2,3);
tocurrency := COPYSTR(currencies,6,3);
datelength := STRLEN(date);
datelength := datelength - 2;
strtdate := COPYSTR(date,2,datelength);
eval := EVALUATE(startdate, strtdate);
myforex."Currency Code" := fromcurrency;
myforex."Relational Currency Code" := tocurrency;
myforex."Starting Date" := startdate;
myforex."Exchange Rate Amount" := 1;
myforex."Adjustment Exch. Rate Amount" := 1;
myforex."Relational Adjmt Exch Rate Amt" := myforex."Relational Exch. Rate Amount";
inverse := 1/myforex."Relational Exch. Rate Amount";
myforex.INSERT;
Record.NEXT;
myforex."Currency Code" := tocurrency;
myforex."Relational Currency Code" := fromcurrency;
myforex."Starting Date" := startdate;
myforex."Exchange Rate Amount" := 1;
myforex."Adjustment Exch. Rate Amount" := 1;
myforex."Relational Exch. Rate Amount" := inverse;
myforex."Relational Adjmt Exch Rate Amt" := myforex."Relational Exch. Rate Amount";

Right now I have it just as an action that I have to click after manually downloading the csv each day. If I understand correctly, to automate it, a codeunit has to be written to reference the XMLport, right? Unfortunately, I don't have a developer's license, but my company is reviewing whether or not we should buy one, but I have emailed our vendor to request a work order for implementing a codeunit for the XMLport - can't imagine it should take long to write...

My other question/what I'm gonna start working on - I can edit that URL feed to include multiple exchange rates, rather than just USD/MYR. We have multiple "companies" in different areas of the world, each needing just 1 exchange rate (unless we use USD there, in which no exchange rate is needed, obviously). Currently my XMLport requires just that the csv only hold the exchange rate for the company it is being run in....what I'd like would be to have all 5 rates in the csv, so it's only 1 download, rather than 5. I imagine this can be done using an IF statement, but since I'm still learning the basics I'm not sure. I'm going to try this out and hope I can figure it out, but if I'm wrong and somebody would like to save me a lot of tiem and grief that would be nice

.

Finally - if/when I get the single file import working - is it possible to run it only once and have it update each company? Would this be something that would go into the codeunit?

As always, thank you muchly!

geordie · 2014-01-20

I try to answer to the questions

- Automating download: yes, you need to call it from a simple codeunit, with some code like this:

varXmlFile.OPEN(“FilePath\myXmlfile.xml”);
varXmlFile.CREATEINSTREAM(varInputStream);
XMLPORT.IMPORT(XMLPORT::XMLportName, varInputStream);
varXmlFile.CLOSE;

- Multiple exchange rate: in my opinion should be better to edit the URL for downloading a rate each time. It can be a little
slower but this allow to create an "atomic" which provide data download for a specific currency, which you can call every time you need.

- Multicompany: if you have all companies included in a unique database it's possible to use CHANGECOMPANY statement on your "myforex" variable 4 times to perform insert in each of them.

XMLport basics (Beginner)

Comments

Categories