Articles in this section

NetSuite Hierarchy Integration

The NetSuite sFTP integration automates the synchronization of Clients, Jobs, and Tasks from NetSuite directly into ClickTime. By reading CSV files from a secure server, this integration ensures your project data remains consistent across both platforms without the need for manual data entry.

IMPORTANT: This integration is a managed service enabled by the ClickTime Professional Services Team. It requires an individual implementation fee and your ClickTime account must be on a prepaid 12-month contract.

Interested? Email support@clicktime.com.

Quick Start Checklist

  • ✅ Your account is on a prepaid 12-month contract.
  • ✅ You have Administrator credentials for ClickTime.
  • ✅ You can produce UTF-8 encoded, Comma-Delimited CSV files from NetSuite.
  • ✅ You have identified an sFTP hosting preference (ClickTime-hosted or internal).

How to Get Started

The implementation is a collaborative process. ClickTime Professional Services will configure the internal "Mapping Profiles," while your administrators will manage the automation via the Data Transport tool:

  1. Access Management: Sign into more.clicktime.com using your ClickTime Admin credentials.
  2. Configure Credentials: Navigate to Open Jobs > Settings. Enter your sFTP Host, Username, and Password.
    • Note: If ClickTime is hosting your sFTP via Couchdrop, these credentials will be generated and provided by your Professional Services lead.
  3. Create a Schedule: Click "Open Jobs" and define your task:
    • Data Type: Select "Netsuite: Clients Import", "Netsuite: Jobs Import" or "Netsuite: Tasks Import".
    • Direction: Select "Inbound".
    • Cron Expression: Set your Cron Expression (schedule)

Scheduling & Cron Expressions

Automation is controlled by Cron Expressions. These strings define the execution schedule across five time-based slots.

Slot Represents Allowed Values
1st Minute 0-59
2nd Hour 0-23
3rd Day of Month 1-31
4th Month 1-12
5th Day of Week 0-6 (Sun=0)

Cheat Sheet Symbols:

  • * = Every possible value in that slot.
  • , = A list of specific values (e.g., 1,15,30).
  • / = Incremental steps (e.g., */30 for every 30 mins).

Common Examples:

  • Daily at Midnight: 0 0 * * *
  • Every hour, on the hour: 0 * * * *
  • Every weekday at 6:00 AM: 0 6 * * 1-5

Monitoring: Once your job has been created, you can use the "View Job Schedules" and "See Jobs Status" options to.

Under "View Job Schedules" you can:

  • See your saved jobs with the following information:
    • Transfer Direction (Inbound/Outbound)
    • Data Type
    • Entity Type
    • Filter Preference
    • Cron Expression
    • Last Run
    • Next Run
    • Schedule Created On
    • Actions (trigger the job to run now/delete)

Under "See Job Status" you can:

  • Toggle between Outbound/Inbound in the top left
    • See Transfer Direction (Inbound/Outbound)
    • Data Type
    • Entity Type
    • Job Status
    • Comments
    • JobCreated
    • JobStarted
    • JobCompleted

Requirements & Hosting

  • File Specification: All files must be UTF-8 without a Byte Order Mark (BOM). Files should be Comma-Separated Values (CSV).
  • Hosting Options: You can use your own internal sFTP server or utilize ClickTime’s hosted solution via Couchdrop.com.  If you choose to use your own internal server, you will need to work with our Professional Services Team to make sure this is configured correctly.

About ClickTime-Hosted sFTP (Couchdrop)

Couchdrop is a cloud-native sFTP provider used by ClickTime to securely bridge data between your NetSuite instance and our Data Transport tool.

  • Authentication: Couchdrop supports both standard Password authentication and SSH Key-based authentication (RSA/ED25519).
  • IT Whitelisting: If your corporate firewall or NetSuite environment restricts outbound traffic, your IT team may need to whitelist Couchdrop's static IP addresses. Please contact Support for the current IP list.
  • Directories: The root directory will contain an /inbound folder for your NetSuite CSVs and a /processed folder where files are moved automatically after a successful run.
  • Administrator Access: Access to the Data Transport tool is restricted to ClickTime account administrators.

 

Technical Specifications & Mappings

The integration matches records using the NetSuite Internal ID. If a record with that ID exists in ClickTime, it is updated; if not, it is created (unless marked Inactive).

Clients (Pattern: netsuite_clients*.csv)

NetSuite Column ClickTime Field Transformation Logic
Internal ID ClientNumber Exact Match
Company Name Name Saved as: Company Name (Internal ID)
Inactive IsActive F = Active, T = Inactive

Jobs (Pattern: netsuite_jobs*.csv)

NetSuite Column ClickTime Field Transformation Logic
Internal ID JobNumber Exact Match
Customer : Internal ID ClientNumber Matches against existing ClickTime Clients
ID Name Saved as: ID (Internal ID)
Status / Inactive IsActive Requires specific string matching (see below)

Tasks (Pattern: netsuite_tasks*.csv)

NetSuite Column ClickTime Field Transformation Logic
Internal ID TaskCode Exact Match
Project : Internal ID JobNumber Matches against existing ClickTime Jobs
Title Name Saved as: Title (Internal ID)

Custom Fields: To map a NetSuite column to a ClickTime custom field, the CSV header must be prefixed with NetSuite_ (e.g., NetSuite_Territory maps to the ClickTime field Territory).

 

Data Logic & Validation

  • The Sequence Rule: You must process imports in the order of Clients ⟶ Jobs ⟶ Tasks. If a Job is imported but its parent Client Internal ID cannot be found in ClickTime, the record will fail to import.
  • Job "Active" Logic: A Job is only active in ClickTime if Inactive=F AND the Status matches one of the following exactly: In Progress, Awarded, or Pending. All other statuses result in an inactive ClickTime Job.
  • New Record Safeguard: The integration will not create a new record if it is marked as Inactive (T) in the source file. It will only update existing records to an inactive state.
  • Data Formatting: Internal IDs are case-sensitive. Ensure NetSuite exports do not include trailing spaces or hidden characters.

Troubleshooting & FAQs

👆 "No files found matching pattern"
  • Verify the file extension is exactly .csv (not .CSV or .txt).
  • Confirm the file is in the root /inbound folder. Subfolders are not scanned.
  • Check the pattern configuration in the Data Transport tool (e.g., netsuite_jobs*).
👆 "No SFTP Settings found"

This occurs when a schedule is triggered but the user who created it hasn't saved sFTP credentials in the Settings tab of the Data Transport portal.

👆 Parent Record Resolution Failures

If a Task fails with "Invalid Job Number," check the Project : Internal ID column in your Task CSV. This ID must already exist as a JobNumber in ClickTime.

👆 Permission Denied on File Move (Couchdrop)

If files remain in /inbound after a "Successful" run, the sFTP user lacks Write/Delete permissions to move the file to /processed. If using Couchdrop, contact ClickTime Support; if using your own server, adjust your IT permissions.

 

Feedback & Support

Need help with your NetSuite integration or want to request a technical test cycle? Email support@clicktime.com.

Related articles

Comments

0 comments

Article is closed for comments.