Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Using CCDS with a NetSuite Saved search

One of the many advantages to using our Adaptive data integration is the ability to use saved search from NetSuite as a data source. 

It is possible to set up a Saved Search from NetSuite as a Data Source in Adaptive by itself, however the connection will then rely on NetSuite’s API and it has some limitations when it comes to what information and fields it can pull through. In order to be unrestricted and pull in anything that you need out of a saved search, you can use CCDS (custom cloud data source) method to extract all of the information you need. 

Natively, the basic NetSuite integration method does not support this so below is a guide on how to achieve this. 

We will be using a CCDS in the below guide to use to connect to a net suite saved search. But first, we must generate the saved search, below are steps on how to do that (if you do not have the bundle installed).

NOTE: all files mentioned below can be found in the SS CCDS files.zip.

NetSuite Process: 

  1. We have to install the NetSuite Bundle first to get the ability to generate link for the .csv file for the saved search.

  2. Select Search & Install Bundles (Customization>>SuiteBundler).

  3. Enter 255571 – the ID of our latest bundle (follow up with Support if this step changes).

  4. Press search, the bundle information will appear.

  5. Install the bundle by clicking on the blue Install button.

  6. hit OK in the prompt that appears.

  7. The status will say "Pending" for the bundle ( Eventually, after pressing the Refresh button a few times it will appear with a green check mark).

  8. After the bundle has been installed, make sure that the File cabinet (Documents>>Files>>File Cabinet) in the bundle has an "Adaptive Integration".  Make sure you know the internal ID of this folder.

    • One easy way to find the internal ID is to hover over it and look at the bottom of your browser (the URL will say something like folder=1322 at the end).

  9. After the Bundle gets installed (green check appears under Status) you need to tell it which Saved Search to save to the File Cabinet.

NOTE: Extra details are available in a file called "NetSuite CCDS Bundle Install Instructions (004)" from the archive attached to this article. 

Once the Bundle is installed, you will need to make sure that you have a saved search available that contains the data you wish to import. First, we will need to review Script Deployment details.

  1. Navigate to Customization>>Scripting>>Script Deployments. 

  2. Sort by Script name (by clicking on the “SCRIPT” column header. 

    • Note: We’re looking for scripts that begin with “SS”. 

  3. Find the ones titled "SS Saved Search Data to File Cabinet" (one of them will be scheduled and another will not be). 

  4. Run the one that is scheduled by clicking on "Save and Execute" (click on the arrow next to the "Save" button). 

  5. Click on the  “View” on the script deployment that has the "Script" field of "SS Create CSV file". 

    • Note: It will bring up some details about this script. 

  6. Navigate back to the file cabinet and click on the  Adaptive Integration folder, you’ll see the file just created by the Saved Search and copied to the File Cabinet by the bundle. 

  7. You’re probably going to be fetching this and related files via CCDS; and you’ll need to know the URL for where these files are located. 

  8. To get the URL click the edit button next to the file name. there, you will see the link to this saved search. right-click and copy link location and paste it in a safe place. 

    • Note: if you copy/paste this link into your browser, it will download the .csv file that we will be working on.

Adaptive Process:

Now that we have the URL, we can now begin setting up the CCDS data source in Data Designer:

  1. Navigate to Data Designer by going to Integration>>Data designer.

  2. Click on the "Data Source" section to expand it.

  3. Click Create new Data source.

  4.  Select "Custom Cloud Data source" and give it a name.

  5. On the resulting pop up, make sure that "Don't require OAuth" radio button is selected.

 

At this point, the Data source is created but is blank. At the top, you will see 3 tabs: "Data source Settings", "Designer Settings" and "Scripts".

 

We will work on the "Scripts" tab next. By default, there will be a sample script in there, called "SampleCCDSJavaScript". We don't need that one, so you can delete it from this tab (tick the box next to this sample script and hit Delete Script at the bottom). 

Once that is done, we will need to put in new scripts in there. Attached to this article are a couple of javascript files that are mostly set up to work right away: file names are theScript.js & FileDefs.js. For the next few steps, you would need to download & install a code editor, such as Notepad++ or Visual Studio Code or another code editor that supports javascript. 

  1. Open theScript.js in the code editor.

  2. Press CTRL+A, then CTRL+C to copy the entirety of the script. You do not need to edit anything in this file, just copy it in its entirety.

  3. Back in Data designer, click on "New Script" at the bottom. 

  4. Paste the copied contents of theScript.js file in the code viewer in Data Designer.

  5. In the pop up code viewer, hit Apply & then hit Save at the bottom left of Data Designer.

     
  6. Move to the "Designer Settings" tab. 

  7. Under the "Settings Component" area on the left, find the Static folder and in the folder, find the Text entry.

     
  8. Click, drag & drop that anywhere in the middle.

  9. Click on the little Edit icon next to the name of the static text field you just added.

  10. Under Setting name, input "NetSuite File Cabinet URL".

  11. The last field (will be called "NetSuite File Cabinet URL") will need to contain the URL of our Saved Search file that we got in the first part of this exercise.

     
  12. Next, we will need to work on the FileDefs.js file.

  13. Open the saved search excel file via the URL and take all the column headers and transpose paste them in another sheet. The reason for this is that we need to define the column type that will be used by the CCDS data source.

    • Note: They will mostly be either a Datetime type column, a Text column or a Float column. 

  14. Next to the column headers, put in the type you think best matches the type of data included in the file. You can flip to the main worksheet to actually see the kind of data included in that column. 

  • Note: Datetime type will be used for columns that include dates or date & time string; Text column is used for any text type data included; Float type column is used for all numbers that include a decimal. 
  1. Once all the columns have been assigned, we can move on is editing the FileDefs.js file in our code editor.

  2. Open a new blank tab or a new file in the code editor and paste the contents of the file to this new tab (Press CTRL+A, then CTRL+C to copy the entirety of the script). You could call this file fileDefs_specific and save it in a safe place on your computer. 

  3. At the beginning of the file, you will see a number of entries that define the column/structure of the CCDS data source. We will need to take our column headers/names and their types and replace whats in the file with our specific structure from the file.

    • Note: You can either type over what's in the file already or copy/paste values from your excel worksheet into the code. Some of the columns names and entries in the FileDefs.js file might overlap with your specific saved search and you can leave that alone.

  4. Once the copy/paste exercise has been completed, you can delete unnecessary lines from the code, but make sure that the last line of that code block does NOT have a comma at the end.

    • Note: There is an entry for every one of these lines called Order, so make sure that from the first line to the last line, the order is set up properly, 1 to however many lines you have. In the FileDefs.js file, there are 32 lines, so the order entry goes from 1-32. 

  5. Once that is set up, if you are not using multiple Saved Search files, you will need to make one final edit to the file. Include "/*" bit of code on the line following this line: "urlList.push(dataSource.getSetting("NetSuite File Cabinet URL").getValue());". This will make sure that the code will stop evaluating after getting the NetSuite File Cabinet URL. You will notice that everything below this bit of code will turn a different color in the code editor (green in the case of Visual Studio code). 

  6. Now, we will repeat steps 2 through 5 and call the script in Adaptive "FileDefs".

  7. At this point, you should have 2 script files in the "Scripts" tab of the CCDS. 

  1. Navigate to the "Data Source Settings" and in the Actions pane, click "Test Connection" and make sure you see a message that says "Connection was successful". 

  2. Once the connection has been tested, click on the "Import Structure" button in the Actions menu. this will populate the column headers & types that we were working on previously. 

  3. At this point the headers should be there but no data, so as the last step, we will click on the "Import data" to get the data from the saved search to the staging area of the data source. 

  4. Verify that the data came over properly. You should be done with the CCDS set up as of this step. 

Some errors or challenges that you might face during the set up:

  1. Link scrambling: some email clients and set ups might scramble certain links as they come through so if you are attempting to email it, this might occur. It is best to put the URL of the Saved Search in a notepad file and send it in an email. 

  2. General syntax errors: make sure that you followed the steps above to eliminate any JavaScript syntax errors. 

  3. In some saved searches, the first column will be a non-data column that contains either a start or something that isn't a piece of data. In this case, you would need to create a new line at the very beginning of the file definitions and define it as "Ignore" or something to that effect. Type is text and length should be set to 10. You can do this in the code editor or the code viewer in Adaptive directly.