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
- 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
- Saved Search
- 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.
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 sets
NetSuite Saved Search - Use a saved search
Refresh - 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
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.
Note: In order to bring in a data from a saved search you need to drop it into an already created template. Please ensure you have the CloudExtend template built prior to bringing in your saved search.
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 your template. CloudExtend Excel was built to manipulate data and send it back to NetSuite therefore a template is required to hold the data. Only the fields in your template will be populated, ie if you have 20 search results columns in NetSuite but only 10 in a CloudExtend Excel template only those 10 columns will be populated.
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 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.
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 successful
Turns 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.
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 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.
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).