Automating Command Line Functions in NAV RTC to Transmit via FTP
I had a request the other day to help a customer create a purchase order export file, and then transmit the file to the vendor via FTP automatically. Back in the olden days of NAV (which I know about because I’m a grizzled veteran of NAV development, complete with gravelly voice), you’d use the SHELL command to send things to the command line; however, in the modern world of the RTC in NAV 2009 and beyond, you can’t do that anymore. Fortunately, we’ve got the Windows Script Host Object Model objects to help us out.
When I got the assignment, I did some Googling around and I found the article, Another Way to Use “Command Shell” on NAV, about running commands from within NAV. I managed to take what I learned from that article and combine it with what I know about FTP file transmission to make something that should work out nicely.
For my customer, I used an XMLport object along with the Outstream and File variable types to create my exported purchase order file. I’m going to skip the instructions on how to do that, since they’re easily found elsewhere, and just talk about automating the FTP transmission. (Note that you could use similar techniques to automate other Windows shell commands if you wanted to.)
For the transmission, we’re going to write an FTP script file that will connect to the remote FTP site, upload the purchase order file, then download it again to the local computer with a different name to verify that the upload finished. After that, we’ll delete the FTP script and the verification file. If you’re testing things out, you might want to skip the steps that delete the files while you’re working out bugs.
To start with, we’ll need to declare some variables. I’ve copied them into this handy chart for easy reference. (Also, it’s really easy to copy from the C/AL Locals menu into Excel.)
| Name | DataType | Subtype | Length |
|---|---|---|---|
| WindowsScriptingHost | Automation | ‘Windows Script Host Object Model’.WshShell | |
| DisplayWindow | Boolean | ||
| CR | Char | ||
| LF | Char | ||
| ScriptFile | File | ||
| WindowStyle | Integer | ||
| TheOutstream | OutStream | ||
| EDIFileName | Text | ||
| EDIFileName2 | Text | ||
| LineBreak | Text | 2 | |
| ScriptFileName | Text |
Table 1 – Variable declaration chart
We use WindowsScriptingHost to actually do all of our command line stuff. The DisplayWindow and WindowStyle variables are necessary because automations want you to send variables instead of constants. The CR, LF, and LineBreak variables are all used together to make line breaks in our script file so that it’s readable. TheOutstream is used to write the text for our FTP script to the file created with ScriptFile. EDIFileName is used to hold the name of the file we’re sending, and EDIFileName2 is used to hold the name of the file we download to verify that our upload completed. Finally, ScriptFileName is used to specify the name of our FTP script.
The code looks like this. Anything in angle brackets should be replaced by you with values relevant to your own local system and FTP server.
CR := 13; // carriage return character
LF := 10; // line feed character
// windows likes CR and LF together
LineBreak := FORMAT(CR) + FORMAT(LF);
WindowStyle := 1;
DisplayWindow := TRUE;
// write the FTP script file
ScriptFile.CREATE([File Path] + ScriptFileName);
ScriptFile.CREATEOUTSTREAM(TheOutstream);
TheOutstream.WRITETEXT(STRSUBSTNO('open %1',[FTP Site Address]) + LineBreak);
TheOutstream.WRITETEXT([FTP Site Username] + LineBreak);
TheOutstream.WRITETEXT([FTP Site Password] + LineBreak);
TheOutstream.WRITETEXT('cd [Target Directory]' + LineBreak);
TheOutstream.WRITETEXT(STRSUBSTNO('put "%1%2"',[File Path],EDIFileName) + LineBreak);
TheOutstream.WRITETEXT(STRSUBSTNO('get "%1" "%2%3"',EDIFileName,[File Path],EDIFileName2) + LineBreak);
TheOutstream.WRITETEXT('quit');
ScriptFile.CLOSE;
CREATE(WindowsScriptingHost,FALSE,TRUE);
WindowsScriptingHost.Run(STRSUBSTNO('ftp -s:"%1%2"',[File Path],ScriptFileName), WindowStyle, DisplayWindow);
IF EXISTS([File Path] + EDIFileName2) THEN BEGIN
IF ERASE([File Path] + EDIFileName2) THEN;
IF ERASE([File Path] + ScriptFileName) THEN;
IF GUIALLOWED THEN BEGIN
MESSAGE(‘File Sent’);
END;
END ELSE BEGIN
ERROR(‘File Did Not Send’);
END;
ScriptFile.CREATE is used to create a new file. Note that you may need to play with your file path to handle the client/server architecture in NAV. (Also, it’s generally a good idea to put files that you’re sending to external entities on a network file share that’s being backed up regularly. That way, you’ve got archives of what you sent and when you sent it to handle the inevitable file error, where you have to go through the file you created and figure out exactly why it exploded when the recipient tried to process it.) I’ve used separate variables to hold the file path and the file name because I like to put all the files I’m sending together in a directory specified in a setup table somewhere. (In the case of the purchase order files I was sending, I added a file path field to the Vendor table so that files for each vendor could be stored separately as they roll out automated purchase order transmission to multiple vendors.)
ScriptFile.CREATEOUTSTREAM(TheOutstream) tells the file that we’re going to write to it via an OutStream variable. We then use TheOutstream.WRITETEXT to write each line to the file. The first three lines in the script file tell the command line FTP client which FTP server address to open along with the username and password to send. (I’m not going to get into handling any encryption or decryption of passwords here; that’s a subject for another article. We’re also deleting the script file after we finish with it, so there’s little need to worry that someone will grab it and find the password.) Once we’ve finished writing all the lines for our script file, we use ScriptFile.CLOSE to tell the file that we’re done and close things down.
The CREATE(WindowsScriptingHost,FALSE,TRUE) command summons our automation object into existence, and then WindowsScriptingHost.Run gives it the command we want to execute.
The EXISTS function verifies that our script ran correctly and we managed to download the file we’d just sent as verification of successful transmission. ERASE gets rid of our script and our verification file; we didn’t need them for anything other than just checking, anyway. And our last few lines of code give us a message so we know whether things sent properly or not.
Now you know how to send files via an auto-generated FTP script. Hopefully, you’ll be able to put this to good use. (And if nothing else, I’ve documented the steps I used for the next time I have to do it.)
Obligatory video game update
I know that everyone really reads my blog entries to learn my deep thoughts about playing Street Fighter, and I wouldn’t want to let down my adoring public. I’m super-excited about Street Fighter V, but until it comes out, I’m still playing Ultra Street Fighter IV. I actually picked up Hugo as an alternate character, and I didn’t really get how to deal with fireballs until I spent some time in training mode, just having the dummy throw lots of projectiles until I learned how to deal with them. Practice is important.
If you have any further questions about this or other development issues, contact one of our development experts at ArcherPoint. If you enjoyed this blog, check out our collection of Development Blogs.