Apps Details

Introduction

 

NetSuite’s sFTP module lets you send and receive files from an sFTP server.  In this article I will detail a customization to send and receive files using the sFTP module.  The customization will use a custom record to save the connection details. We will be using a key file to login to the sFTP server.  This customization will not detail using a password to login.

Step 1: Create a custom record to house the sFTP configuration

The custom record will allow you to connect to multiple sFTP servers.  The scheduled script that sends/receives files (detailed later) will have a reference to the specific sFTP configuration.

Go to Customization->List, Records, & Fields->Record Types->New.  Create the custom record with fields so it matches the image below.  Here is the description of each field:

  • sFTP User: The user name used to login to the sFTP server.
  • URL: The sFTP server URL.
  • Port: The sFTP server port (Default is 22).
  • sFTP Host Key:   The host key for the sFTP server.
  • Directory:  The directory on the sFTP server where the files are downloaded from or uploaded to.
  • To Process Folder ID:  The internal ID of the folder where files are waiting to be uploaded to the sFTP server.
  • Complete Folder ID: The internal ID of the folder where files are put after they have been uploaded.  When downloading files, this is folder they will be saved to.
  • Key Internal ID: The internal ID of the keyfile used to login to the sFTP server.

 

 

 

Step 2: Create the Key in NetSuite

 

In your sFTP server you should have the option to create an sFTP key. Save the key to a file. Now upload the key to NetSuite:

  • Go to Setup->Company->Keys.
  • Click “Create New”.
  • Under “Details” give it Name and ID.

 

 

 

  • Click the “Files” tab and select the Key file.

 

 

 

 

  • Click Save.  You now see the key.  Make a note of the ID as it will be used when creating the sFTP configuration record.

 

 

 

Step 3:  Create the Host Key

 

Go to a command prompt in Windows and type:

 

ssh-keyscan -t rsa -p <port> rsa <URL of sFTP server> (Example: ssh-keyscan -t rsa -p 22 rsa sftp.yourdomain.com). It will print out something like this:

 

 

Copy this text and put it in a file to reference when creating the sFTP configuration record.

 

Step 4: Create the sFTP configuration record

  • Go to Customization->Records, Lists, & Fields->Records Types.  Select “New Record” for sFTP Configuration.
  • Fill out the record:

 

 

 

Step 5: Create the Schedule Script to Upload and Download files

 

Add and deploy the script (code shown below).  Reference my article “Quick Guide to Adding and Deploying a Script in NetSuite” if needed.  You will need to create a script parameter “sFTP Configuration Internal ID” of type Integer.  When deploying the script you will need to set this to the ID of the record you created which should be 1.

 

The script below does the following:

 

 

  • Gets the sFTP configuration using a script parameter.  The script parameter is the internal ID of the sFTP configuration record.
  • Sends files located in the “To Process” folder to the sFTP server.  After the files are sent, the files are moved to the “Complete” folder.
  • Downloads files from the sFTP server.  It gets a listing of all files in the root folder and then downloads them into the “Complete” folder in NetSuite.

 

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/runtime', 'N/search', 'N/file', 'N/sftp'],
    (runtime, search, file, sftp) => {

        const execute = (scriptContext) => {
            log.debug('==START==');
            try {
                // get the sFTP config
                const sftpConfig = getsFTPConfig();
                const files = getFilesToSend(sftpConfig);
                // send files to sftp site
                sendTosFTPServer(files, sftpConfig);
                // download files from sftp site
                downloadFromsFTPServer(sftpConfig);
            }
            catch(e) {
                log.error('Error', JSON.stringify(e));
            }
            log.debug('==END==');
        }
        const getFilesToSend = (sftpConfig) => {
            let files = [];
            let folderSearchObj = search.create({
                type: "folder",
                filters: [
                    ["internalidnumber","equalto", sftpConfig.toprocessDirectory]
                ],
                columns:
                [
                    search.createColumn({
                        name: "internalid",
                        join: "file",
                        label: "Internal ID"
                     })
                ]
            });
            folderSearchObj.run().each(function(result){
                let fileId = result.getValue({
                    name: "internalid",
                    join: "file",
                    label: "Internal ID"
                });
                files.push(fileId);
               return true;
            });
            log.debug('Files to send',JSON.stringify(files));
            return files;
        }
        const sendTosFTPServer = (files, sftpConfig) => {
            const connection = sftp.createConnection({
                username: sftpConfig.username,
                keyId: sftpConfig.myKey,
                url: sftpConfig.url,
                directory: sftpConfig.directory,
                hostKey: sftpConfig.myHostKey,
                port: sftpConfig.port
            });
            log.debug('connection', "connected");
            let fileName = '';

            for (let i = 0; i < files.length; i++) {
                let fileId = files[i];
                errorMessage = '';
                let myFileToUpload = file.load({
                    id: fileId
                });
                fileName = myFileToUpload.name;
                connection.upload({
                    filename: fileName,
                    file: myFileToUpload,
                    replaceExisting: true
                });
                log.debug('uploaded file', fileName);
                if (sftpConfig.completeDirectory) {
                    // move to complete folder
                    myFileToUpload.folder = sftpConfig.completeDirectory;
                    let fileId = myFileToUpload.save();
                    log.debug('moved file to complete folder');
                }
            }
        }
        const downloadFromsFTPServer = (sftpConfig) => {
            const connection = sftp.createConnection({
                username: sftpConfig.username,
                keyId: sftpConfig.myKey,
                url: sftpConfig.url,
                directory: sftpConfig.directory,
                hostKey: sftpConfig.myHostKey,
                port: sftpConfig.port
            });
            log.debug('connection', "connected");
            // get the list of files in the directory
            const files = connection.list({
                path: ''
            });
            // download each file
            for (let i = 0; i < files.length; i++) {
                const downloadedFile = connection.download({
                    directory: '',
                    filename: files[i].name
                });
                downloadedFile.folder = sftpConfig.completeDirectory;
                downloadedFile.save();
                log.debug('saved file to complete folder', files[i].name);
            }
        }
        const getsFTPConfig = () => {
            // use a parameter to get the internal ID of the sFTP configuration record to use
            const scriptObj = runtime.getCurrentScript();
            const serverConfigId = scriptObj.getParameter({
                name: 'custscript_config_id'
            });
            log.debug('serverConfigId=', serverConfigId);

            const conn = search.lookupFields({
                type: 'customrecord_sftp_configuration',
                id: serverConfigId,
                columns: [
                    'custrecord_sftp_config_user',
                    'custrecord_sftp_config_key',
                    'custrecord_sftp_config_url',
                    'custrecord_sftp_config_port',
                    'custrecord_sftp_config_host_key',
                    'custrecord_sftp_config_directory',
                    'custrecord_sftp_config_to_process',
                    'custrecord_sftp_config_complete'
                ]
            });

            let sftpConfig = {};
            sftpConfig.myKey = conn.custrecord_sftp_config_key;
            sftpConfig.myHostKey = conn.custrecord_sftp_config_host_key;
            sftpConfig.username = conn.custrecord_sftp_config_user;
            sftpConfig.port = conn.custrecord_sftp_port;
            sftpConfig.url = conn.custrecord_sftp_config_url;
            sftpConfig.directory = conn.custrecord_sftp_config_directory;
            sftpConfig.toprocessDirectory = conn.custrecord_sftp_config_to_process;
            sftpConfig.completeDirectory = conn.custrecord_sftp_config_complete;
            log.debug('username=', sftpConfig.username);
            log.debug('port=', sftpConfig.port);
            log.debug('url=', sftpConfig.url);
            log.debug('myKey=', sftpConfig.myKey);
            log.debug('myHostKey=', sftpConfig.myHostKey);
            log.debug('directory=', sftpConfig.directory);
            log.debug('toprocessDirectory=', sftpConfig.toprocessDirectory);
            log.debug('completeDirectory=', sftpConfig.completeDirectory);

            return sftpConfig;
        }
        return {execute}
    });

Conclusion

 

The sFTP module is a powerful module that allows files to be transferred into and out of NetSuite.  This customization allows you to have multiple sFTP configurations without having to hardcode values into a script. If you need help scripting or customizing NetSuite please contact Suite Tooth consulting here to set up a free consultation.

 

If you liked this article, please sign up for my newsletter to get these delivered to your inbox here.

FAQ

Your Guide To
Understanding Us Better

Backed by its functional features and proven applications, NetSuite can elevate your enterprise process and accelerate business functions to a great extent! One of the main reasons why you should consider NetSuite customization for your business is because it can boost operational efficiency and make your business more process-centric by optimizing your fragmented enterprise actions in just a single cloud platform.

Just like any other robust software solution, NetSuite customization holds the capacity to address and solve your business pain point – be it a small problem or a complex issue! A reliable and reputed team of NetSuite consultants like us can help you come up with the best strategies and plan to make ERP unleash its full potential to solve your specific business problems.

The design, implementation, and customization strategies of NetSuite might be developed solely on the basis of the specific industry an organization is dealing with. This is dependent on the operational process and size, which comply with every business operation.

Every company has its own unique and distinct business processes, workflows, personalized software needs, and the likes. One of the most remarkable perks of NetSuite is that it’s extremely customizable. If your company requirements are unique or specific, you require trusted NetSuite consultants who hold extensive experience and expertise with robust software development and architecture for making the solution suitable to your requirements.

At Suite Tooth Consulting, we emphasize building a full-fledged strategy to ideate on a plan on action right from the inception until the “go-live” as well as “post-implementation” rescue timetable. Implementing and customizing a robust ERP like NetSuite is a complex and detailed procedure. It can potentially entail a few days or even a few months for more complex builds. As your top-trusted team of consultants, we shall provide you with a detailed forecast or a breakdown of timeline to give you a heads-up on how long our team can potentially take to wrap the entire process.

Get Connected

How can we help?


    Stay in the loop with Suite Tooth Consulting!

    We aim to bring unmatched expertise and professionalism to your NetSuite initiatives. Let’s talk about how our NetSuite consultancy can make a difference!

    Global Client Satisfaction