CloudExtend Excel for NetSuite Overview

CloudExtend Excel for NetSuite allows users to access and edit NetSuite data directly from within Excel, in real time and with a single click. Take full advantage of Excel’s data manipulation functions to retrieve, update, add and delete NetSuite data from multiple record types

CloudExtend also allows you to stream in NetSuite Saved Searches to use for reporting purposes. More on that below.

Finance, Sales and Marketing, Operations, NetSuite Admins and all NetSuite users can benefit from CloudExtend Excel.

This guide will help you understand the basic concepts of CloudExtend Excel. Topics covered include:

What are templates?

Building templates

Template building tips

Retrieving data from NetSuite

Download

Saved Search

Refresh

Updating existing NetSuite data

Creating new NetSuite records

Deleting records

Sharing templates

Creating multi-line transactions

What Are Templates?

Templates are used by CloudExtend Excel to define the NetSuite fields that users will interact with. The CloudExtend Excel Add-In allows fields to be selected via an easy to use interface. When a template is loaded on a Worksheet it becomes an Excel table.

Row 1 is reserved to hold the names of the NetSuite fields and should not be edited unless you are a power user (the names are hidden but can be exposed by clicking in a cell on Row 1). Table headers (Row 2) can be freely edited to make the field names more user friendly. Once a template is loaded into Excel it can be used to retrieve and/or update data from NetSuite. It can even create net new NetSuite records and transactions.

Building Templates

Step 1: Click on the + sign to create a new template OR Click the icon to the right of the + sign to import templates we’ve already built. The pre-built templates can be used as is or customized.

Step 2: Type or scroll to select the NetSuite Record or Transaction the template will be associated with. CloudExtend Excel even supports Custom Records. In the example below a template will be created for Vendor bills.

Step 3: Select the fields you want to add to the Template. You can type in any part of the field name or scroll to locate it. (NOTE: For performance purposes we only show the 1st 100 fields. Simply type ahead and your fields will populate.)

Tip: You can rearrange fields by dragging and dropping.

Step 4: Create a name for your newly built template and click Save.

Step 5: Once your CloudExtend Excel template is saved you can load it into Excel by clicking on the template name (1). You will be warned that loading the template will clear the contents of the Active Worksheet (2). Select Yes if this is OK and your template will load as an Excel table.

Template Building Tips

Tip 1: NetSuite field ID’s - When creating a CloudExtend Excel template, the display name that appears on the record in NetSuite may be different than the field ID. The field ID is what is initially displayed in the template field list. If you are not sure of the field ID open the record in NetSuite. To find the field ID simply click on the display name once you see the ? sign and scroll down to the bottom. The field ID will be displayed. Once your template is loaded you can change the name of the field in the table header to make this easier for your end users.

Tip 2: Required fields - CloudExtend Excel respects the required fields of the form selected (or the default form if a custom form was not selected). To see which fields are required refer to your NetSuite form (create a blank new record). Enter in a value in the entity field and press tab. Fields with an asterisk that are empty are required.Fields with an asterisk that are pre-filled have default values. Leaving this empty in CloudExtend Excel during an upload will result in the default value being populated. Save time and leave these values empty if you plan on uploading default values. In the example below (vendor bill) the red fields are required and the yellow fields, while required, will autofill with default values and can generally be left empty during an update.

Retrieving Data from NetSuite

Now that you have built your template it’s time to use it.

There are three ways to retrieve data from NetSuite:

Download - Use the template’s data filter to avoid downloading large unnecessary record setsNetSuite Saved Search - Use a saved searchRefresh - Typically used to bring in a very limited number of records via Internal ID or to call NetSuite to return the most recent results of the selected records

Download

Retrieves all or select (with Data Filter) records from NetSuite to Excel

Step 1: Clicking download will bring in all NetSuite Records associated with the template. This is not typically desired therefore Celigo recommends using a Data Filter (or NetSuite saved search as described later). Click on Create Filter (1). Add a rule or rules by clicking on the + sign or on 'Add a new rule' (2). Create your rule or rules (3) and press save (4).

The example below shows a filter created to limit the download all Vendor Bills with a transaction date on or after December 1, 2017. You can create as many rules as you would like.

Step 2: After you click download all the vendor bills from December 1, 2017 will populate as shown below. Now you can manipulate the data and send it back to NetSuite if you wish by updating as described later.

Saved Searches

Two ways to run your NetSuite saved searches directly in Excel

Option 1: Raw data mode (FREE)*

This is the fastest way to get your saved search results into Excel for reporting purposes. When you select this, CloudExtend Excel will download data from your search into NetSuite*. Once the data is available in Excel, you can further use this data to create you pivot tables, reports, charts, and even lookup tables for other CloudExtend Excel templates. Data download via raw data mode cannot be sent back to NetSuite.

Note that the column order of your NetSuite saved search is not respected. Column names may also be hard to decipher (NetSuite passes them over with field names from their web services). It's easy to fix this though, just enter a custom name for your column in the 'results tab' of your saved search.

You'll also note that picklist fields (like customer on a Sales Order search) will display the internalID of the record as opposed to the name. The best way to get the name to display is to reference the field on the related record in your saved search results, ie if you want to see your customer name field, scroll to the bottom of the fields list where you see related records (they all have ... at the end) and select Customer and then choose the field name you want to see in your results.

Step 1: Click on the hamburger icon on the top left hand side of the CloudExtend Excel toolbar.

Step 2: Click on Saves Searches.

Step 3: Select the appropriate search type. If you are looking for a vendor bill saved search you would not search for vendor bill but instead Transaction because that is the type of search it is categorized as in NetSuite.

Tip: If you are unsure of the what the type is, you can go to saved searches in NetSuite and find it (see below).

Step 4: Now you will select the saved search you want to bring in. You may filter your list by typing to the left of the magnifying glass.

Step 5: Choose the Download as raw-data option.

Step 6: Your data will download into Excel in raw form.

Summary Saved Searches and Calculated Columns

*Support for downloading summary saves searches as well as displaying fields with calculated columns is only available to Enterprise customers who have installed the Celigo Cloud Extend CENS bundle into their NetSuite account and are not applicable to Template mode downloads.

Option 2: Template Mode (available to paid subscribers only)

If you are a paid subscriber and you want MODIFY the data you download then you will need to choose the option to download into a template. Note, you could use a saved search for this or you could use your templates data filter.

In this scenario data downloaded can be modified in Excel and send back to NetSuite.

Downloads take longer due to all the metadata required to also send changes back to NetSuite.

Step 1: Click on the hamburger icon on the top left hand side of the CloudExtend Excel toolbar.

Step 2: Click on Saves Searches.

Step 3: Select the appropriate search type. If you are looking for a vendor bill saved search you would not search for vendor bill but instead Transaction because that is the type of search it is categorized as in NetSuite.

Step 4: Now you will select the saved search you want to bring in. You may filter your list by typing to the left of the magnifying glass.

Step 6: After you choose your template, click yes and your template will load and data will begin to populate the template from NetSuite. From here you can make edits to your data and send it back to NetSuite (Update).

Refresh

Refresh is available when you have one or more internal ID’s populated in the internal ID column. It is a quick way to bring in one or more records from NetSuite for editing.

Step 1: Take an internal ID from NetSuite and paste it into the Internal ID. Click on ‘Refresh’ and it will populate all the fields in your template.

Step 2: Your results stream in and you can start making changes to your data.

Update

Takes information from the CloudExtend Excel and pushes it back to NetSuite.

Picklist Values Explained

CloudExtend Excel dynamically identifies picklist values while you type data into sheet (or you can use the Picklist drop down list in the toolbar to find your values.)

Example : If you have an account number “44551 Salaries”, you can just enter “44551” in Excel cell while entering your data and CloudExtend will fetch the complete picklist value from NetSuite and update the cell dynamically. If it finds multiple matches for entered value, user will be alerted to pick the correct value by clicking on the view picklist values in task pane.

Users will see the following visual cues to know if a picklist value is automatically identified or not:

Excel cell turns grey when CloudExtend is identifying the value of picklist Turns normal (white) once lookup is done and successfulTurns yellow if there is no match or there are multiple matches (i.e. when manual intervention is required).

The below video demonstrates dynamic picklist fetching:

Step 1: In the example below the Location field is being updated from Boston to Los Angeles. I can either type ahead OR I can click under location and click on ‘View Picklist Values’ (in the add-in). Choose Los Angeles and drag down to fill in the remaining lines.

Step 2: Click ‘Update’ and then check off ‘All Rows’ and then click ‘INSERT/UPDATE’ in the middle. Those 13 vendor bills will be updated in NetSuite with the new location of Los Angeles. The internal ID will turn green on each row as the updates are successfully completed.

Notifications

CloudExtend Excel will give you 3 types of color coded notifications after an update:

Internal ID’s in green indicates a successful update.

Messages in red indicate an error. The error needs to be fixed before the record can be saved to NetSuite. Tip: After the error is fixed upload just the affected row.

Messages in yellow indicate a notification from NetSuite but are still successfully updated.

Creating new NetSuite records

CloudExtend Excel tables already interact with NetSuite. When your table loads CloudExtend Excel begins to cache possible values for drop down fields in the ‘Picklist’ at the top right oft he Add-In. You can simply start entering values in Columns to begin.

A more common use case, however, is for end users to use Excel formulas (or even copy/paste) to populate the table. On example would be transfer orders. You may have another worksheet with recommended allocation of inventory (1). In this case I copied and pasted the data from that worksheet to my CloudExtend template to create a new transfer order (2). Once updated, you will see a new Internal ID has generated in green which means the transfer order has been created in NetSuite successfully (3). Now if you hit refresh you can bring in all the data (4).

Delete

Delete NetSuite records from Excel.

Step 1: Click on ‘Delete’ then check off ‘All rows’ (or select only the rows you want to delete ) and then ‘DELETE’.

Step 2: The transfer order is now deleted in NetSuite (as evidenced by the “Operations Successful”message in the add-in as well as the color gray in the Reserved and internal ID columns).

Tip: Accidentally deleted the data? No worries, simply remove the values from the internal ID’s and click on ‘Update’ to send the data back to NetSuite.

General Tips

Sharing Templates

Step 1: Create your template in CloudExtend and save it as an Excel Template or Workbook. Share or send the workbook to another CloudExtend user.

Step 2: The end user should open the workbook and then open CloudExtend by going to 'Data' and then Manage NetSuite Data.

Step 3. Now you can use the template. Note, the template is currently part of the Workbook and will not be saved to your list of templates.

If the recipient wants to save or modify the template into their own template library (not required) they can do the following:

Step 1: Simply create a data filter and save it.

Step 2: Go to your templates by hitting the 3 small lines at the top left of CloudExtend.

Step 3: Find the shared template and hit edit, then next and change the name to whatever you wish. Hit save. Now the shared template is saved in CloudExtend and modifications can be made to it as normal.

Creating multi-line transactions

To create transactions with multiple lines (line level detail) ensure that all header level fields are identical. During an update this will create one transaction (as evidenced with an identical internal ID returned for each row.) If any of the header level fields are different it will create different internal ID’s. In the below image The yellow arrows showcase 1 internal ID (record) with multiple line items. The red arrows showcase 2 different internal ID’s (records).