ERP Guru - NetSuite Solution Provider

How I survived SuiteWorld 2012

2012-05-29T13:12:00.003-04:00

Airports, Airplanes, Delays oh MY!

Departure: Monday, May 14 2012 at 6am (EST) from Montreal.
Arrival: Tuesday, May 15, 2012 at 1am (PST) in San Francisco. (How did this happen? Well...this is another story all together which can be left for another time, however the important thing is that I got there in one piece.)

The purpose of this trip was to attend SuiteWorld 2012 (NetSuite's annual customer conference for customers, users, partners, media and industry analysts) and gain as much out of it as possible.

ERP Guru was a sponsor at the event and this was our booth

Considering there were 1000 more attendees this year than last year (and it was completely sold out) it was quite a challenge to attend all the breakout sessions which I had added to my agenda.
NOTE: These "track" sessions were so popular, that certain rooms were not large enough to handle the number of people wanting to attend these breakout sessions. This will be resolved by NetSuite next year in hosting SuiteWorld 2013 in San Jose to accommodate the extra people wanting to attend!

Although I attended a few sessions during my 4 day stay at SuiteWorld, I would like to outline two of them in particular that I felt were of importance, although both were more on the technical side of things. The first was Advanced Platform APIs for Developers and the second was SuiteCloud Platform: The Road Ahead.

Advanced Platform APIs for Developers

This track session was intended for advanced programmers, and it was essentially to learn about the latest SuiteCloud APIs and how we can apply them in our scripts. This session covers a multitude of topics including Scheduled Script Management, custom plugins and the newly added SuiteAnalytics APIs. The latter was the main point of interest for most, as we spent more time on this topic due to the new ability to create Pivot reports by script.

Although it was repeatedly mentioned throughout the session that there is a good chance this new feature might not even make it to the cutting block, NetSuite still "wowed" us with the ability to add embedded formulas in our searches, and a new way to configure row and column hierarchy in pivot reports using the new API nlapiCreateReportDefinition. They also indicated that they might be adding the "or" functionality in searches (but I'll believe it when I see it..and when I do see it, I'll be the first to write another blog about it).

SuiteCloud Platform: The Road Ahead

In this track session, the director of Product Management provided us with a view into the future of the NetSuite SuiteCloud development platform and then was open for discussion with developers and administrators on any SuiteCloud platform including SuiteBuilder, SuiteFlow, SuiteScript, SuiteTalk and SuiteBundler. There were two points of interest that caught my attention, and these might change how we develop and write our scripts here at ERP Guru.

The first announcement they made was regarding SuiteFlow enhancements. They introduced the possibility of accessing the Time and Weekly Time sheet record using workflow. Included in this topic was the mention that they will introduce the ability to access sublists within a workflow, which is a very nice addition to the SuiteFlow family.

Obviously, I do save the best for last (at least my personal favorite) and this is the introduction of the new SuiteCloud IDE. This is an eclipse plugin that Evan Goldberg (Founder, Chief Technology Officer and Chairman of the Board) quickly demonstrated how it works in his Keynote, however we went more in depth in this track session on how this can help all the developers writing scripts. This will allow access to multiple NetSuite accounts and will also allow for sophisticated code completion with regards to API, fields, and records. In addition, the new plugin also projects templates for all script types and functions in your JavaScript code (therefore you don't need to write it up each time).

Overall, a GREAT experience attending SuiteWorld. I am lucky to have been chosen among my peers to go along with a few other employees from ERP Guru. I met a lot of partners, new resources, met up with some of our clients, and even made some new contacts along the way. I learned new things while attending all the various track sessions that occurred during the 4 day event.
Would I do it again? Absolutely
Would I fly with the same airline? Never again!

Importing Gift Certificates

2012-05-15T16:42:00.001-04:00

It is possible in NetSuite to import gift certificates into the system (NOTE: the certificates must already have an authorization code). To do so, ensure the following steps have been taken:

Enable the Gift Certificates feature under Setup > Company > Enable Features > Items & Inventory > Items > Gift Certificate Setup.
Under Setup > Accounting > Accounting Preferences > Items/Transactions > Other Item Preferences > Gift Certificate Auth Code Generation

NOTE: there are three options: System Generated, Enter on Order, and Add-On Item. For this purpose it should be set to Enter on Order.

This can be applied to both physical gift certificates/cards and virtual gift codes, however in order to do so a generic Customer against which to create the Cash Sales (and import the gift certificates onto), and a Generic Gift Item must be created. Using this generic customer, it is possible to import legacy gift certificates

NOTE: An email will be sent to the gift certificate recipient. You can enter the same e-mail for all certificates (ie, the generic customers e-mail), it will not impact who can use the certificate.

Create a CSV import mapping for Transactions > Cash Sale. (You could also use Invoice, but you will need to create payments/journals to close the transactions).

In the mapping, under NetSuite Fields > Cash Sale Items you will find the fields you need to correctly map, with the Gift Certificate specific fields corresponding to item options:

Undep. Funds = No
Account = An account to post the transaction against
Customer

Amount (the amount remaining on each card - use the Note field to keep track of the original amount if needed)
Items : Item (the gift certificate item)
Items : Gift Message
Items : Code (those need to be unique!)
Items : Recipient Email (informational only)
Items : Recipient Name (informational only)
Items: From
Items: Tax Code

Viewing and Managing the Data

Once you have the Gift Certificate functionality set up, you can view and manage the data via Lists > Accounting > Gift Certificates. Through this it is possible to view the original and current value, although the current value can not be edited, the expiration date (this is illegal in some states - ie, PA). A gift certificate can be used in payment through the Order Form > Billing > Payments > Gift Certificate section.

SSP Pages and the Power of Awesome

2012-05-07T15:01:00.000-04:00

I am rarely excited by NetSuite announcements, and I tend to wait a while before taking position on the quality of a new feature...but this time, I am very excited about the new SSP pages. I could barely stay on my chair during the NetSuite presentation on the functionality, and could not wait to start coding one for the pleasure of it.

The feature is still in Beta phase according to the description in the 'Enable Features' page (SuiteScript Server Pages (Beta)), but according to NetSuite it should work properly at this point in time.

So, let's dive into this pool of infinite potential (I told you I was excited), and cover some of the things that can be done with SSP pages in the NetSuite Webstore.

Once the feature is enabled (Enable Features), it is possible to access the SSP Application in the Website Setup section. This is where we can create all of our more-awesome-than-the-other-applications SSP Application. For a quick reference, view the screenshot below of the page we will need to work with in order to create our innovative checkout pages.

To start, set a name for the application which will be used in the generated url to access the application. This can always be changed later manually to match your needs. The other important section is the Application Folder which will contain a grouping of related applications, and its URL Root is created by the application folder name (because it is sitting in the live hosting file) and the name of the application.

For those of you who have a keen sense of observation, you may have noticed the Scripts section in the service function. With these we are getting closer to where the magic happens. For those with scripting knowledge, note that service uses the same parameter as a Suitelet (a nlobjRequest and a nlobjResponse).

The Touch Point sub-list is where you will identify which page you want to change and with which you will create the SSP Page. It is possible to change the checkout pages using your own logic, and if you've been trying to alter those pages through different technique, you know how hard it was. The SSP pages create a plethora of possibilities, and the projects you can now achieve.

---NOTE: This next section contains code samples
Once the page you want to change has been identified, it is time to create the actual SSP pages. Please note that currently this is the only way to get the SSP pages to work.

In the pages, it is possible use all the <% ... %> tags we have used in the past. This greatly accelerates the creation of the pages because we can reuse the header, footer or navigation we created in the web store theme. For example, if we want to add the header to the page, we would only have to use the following in the SSP page :
...
<table>
<%=getPageTop()%>
</table>
...
And voilà. We have our theme header generated in our SSP page.

Finally, the thing I'm the most happy to see is a supported access to the shopping session object. If you have ever had to work to get information from the cart and the session, you know how difficult it can be to fetch the right information. Through nlapiGetWebContainer(). getShoppingSession() we finally have access to the sales information. For example, I need the summary of an order (total, subtotal, shipping cost, etc), I can simply do the following :

<%var summary = nlapiGetWebContainer().getShoppingSession().getOrder().getOrderSummary()%>
<%for(var field in orderSummary){%>
<tr>
<td><%=field%></td>
<td><%=orderSummary[field]%></td>
</tr>
<%}%>

Simple! In essence, we now have the possibility to use JSP-like pages. With only these two possibilities I would have been satisfied but there is more, so much more we can do with this functionality.

May: Tips & Tricks

2012-05-02T11:00:00.000-04:00

1 - Paying a Vendor Bill with Credit Card

Create a Credit Card type account in your Chart of Account
Create a Vendor representing your Credit Card bank (for example: "RBC Visa"). On the Financial Tab, under Default Expense Account, make sure to select the account created in step 1.
Pay Bills normally on any outstanding vendor bills, making sure to pay them using the Credit Card account created in step 1. This will decrease the account
To "pay" the Credit Card, use the Write Check transaction. Here, you will select the Vendor representing the credit card created in step 2. The Expense subtab should now list the default expense account, which is the Credit Card account. Now, on the body, select the Bank account you're using to pay the credit card.
Once you've done this, the Write Check will increment the value of the Credit Card account back up, while decrementing your Bank account.

2- Commission Schedule (Tip)

When viewing the list of commission schedules in NetSuite, there is a column called "In Use".

Tip: It should be noted that even if your schedule is used in a commission plan, the In Use column might not be set to Yes. It is only set to Yes when a commission is calculated using that particular schedule.

3 - Form Languages

In order to have a form in a specific language (ie, French France), a user has to log into NetSuite, change their default language to the language they wants to create the form in (Home > Set Preferences).

Once the language is set, the user should navigate to the form, click on 'Customize', and save the form. This operation will save the form in the current language.

The user can now set their language back to the original one, and when they navigate back to print a form, they will now have the option to Print in (Other Language) on the print button.

Transfer Orders

2012-04-20T15:49:00.000-04:00

A Transfer Order is the transfer of items from one location to another. This feature is available in NetSuite when (and only if) the Multi-Location Inventory feature is enabled (Set-up > Enable Features> Item & Inventory > Multi-Location Inventory).

In NetSuite, a Transfer Order can have four different statuses: Pending Approval, Pending Fulfillment, Pending Receipt, and Received. Let's go through what each of these means in the Standard Workflow:

1. Pending Approval
The Transfer Order will maintain this status until either it is manually set to Pending Fulfillment, or is approved via the Approve Transfer Orders window, which can be accessed through Transactions > Inventory > Approve Transfer Orders.

2. Pending Fulfillment
In this stage, the item is committed from the source location, which is viewable in the Quantity Committed column of Location under Item > Inventory, and the Quantity on Order is incremented on the Transfer Location. At this point you can now Fulfill the Transfer Order, which will create a standard item fulfillment which is ready to be packed and shipped.

3. Pending Receipt
Now, once the Transfer Order is in the Pending Receipt status, the Quantity on Hand at the From Location will be decremented, but the Quantity on Order remains on the Transfer Location. At this point you can now receive the Transfer Order.

4. Received
Once the Transfer Order has been received, the Quantity on Order will be decremented proportionate to the Quantity on Hand that has now been incremented.

EXAMPLE:

A company has custom built items which they only use certain quantities of items to build. Taking this into account, the company would like the extra quantities to be moved to a Scrap Location. The Solution?

Create a Custom Column Field - Quantity to Scrap
Create a Transfer Order to commit from inventory for each item with a quantity to scrap (when the Sales Order is created)
When the Sales Order is Billed:

Transform Transfer Order into Item Fulfillment
Transform Transfer Order into Item Receipt

Quantities to scrap should thus be moved from Location to Scrap Location with the correct inventory impact.

April: Tips and Tricks

2012-04-03T16:32:00.000-04:00

1- Remitting taxes (EU or CAN)

In many European countries, just like in Canada, you remit to the government only the difference in taxes that you collected from sales minus what you were charged when buying (Liability minus Asset).

To pay a tax liability in NetSuite:

Enter the appropriate tax codes whenever you create sales and purchase transaction records.
Close the tax period before you do your tax reporting and tax payments. (Go to Setup > Accounting > Taxes > Manage Tax Periods)
Run and review the VAT on Sales and the VAT on Purchases summary and detailed reports. If necessary for adjustments, reopen the tax period and run the reports again.
If VAT on Sales is greater than VAT on Purchases, prepare and post an appropriate journal entry that: Debits the VAT on Sales account for the amount shown on the VAT on Sales, report Credits the VAT on Purchases account for the amount shown on the VAT on Purchases, report Credits the VAT Liability account for the net amount.

When it is time to pay your tax liability, you can write a cheque to the tax agency. You can write a tax liability cheque for any non-US edition or nexus at Transactions > Bank > Pay Tax Liability (Administrator).

2. List of browsers officially supported by NetSuite

Platform	Supported Browsers
Windows XP	Internet Explorer 7.x, 8.x
	Firefox 3.6, 11.x
	Safari 5.x
	Google Chrome 17.x
Windows Vista	Internet Explorer 7.x, 8.x, 9.0
	Firefox 3.6, 11.x
	Safari 5.x
	Google Chrome 17.x
Windows 7	Internet Explorer 7.x, 8.x, 9.0
	Firefox 3.6, 11.x
	Safari 5.x
	Google Chrome 17.x
Mac 10.4	Firefox 3.6
Mac 10.5	Firefox 3.6
	Safari 5.x
Mac OS X 10.6.4	Firefox 3.6
Mac OS X 10.7.2	Firefox 11.x
	Google Chrome 17.x

3 - Setting up a custom favicon in your NetSuite e-commerce store

In a NetSuite hosted eCommerce site, the favicon, or shortcut icon, automatically defaults to a NetSuite `N` graphic. It is possible to customize this so your company's own 16x16 pixel graphic named favicon.co.

Upload the favicon to the root of the file cabinet under web site hosting files > live , or web site hosting files > live > site.

After uploading, insert the meta tag to ensure that your icon shows up. This can be inserted under the "Additions to Head" field under Web Site > Site Templates.

Add the following line (with the correct information) and voila!

March: Tips and Tricks

2012-03-06T15:49:00.000-05:00

1 - Report Scheduling

It is possible to set up any report in NetSuite to e-mail automatically via a schedule. You can set up your Report Schedules to run when you like (every day, weekday, week, month, or year), as well as define who it should be sent to.

To do so you must do the following once you have created your report:

Select 'Schedule' in the bottom right hand corner, and fill out the form
Now, select the 'Recurrence' tab and set up as desired

You can view or edit this schedule at any time.

2 - Third Party Billing

Third party billing accounts can be configured at two levels:

Global level in Setup > Company > Company Information. Both a Fedex and a UPS account number can be specified and these will be used according to the selected shipping item selected and which integrated shipping carrier its using. IE, if selecting a UPS shipping item, the UPS account number will be used. These values will be used whenever a customer's third party billing information is not set.
On The customer record, on the
financial tab. These values override the Global values if they are set.

On the Sales order / Item Fulfillment, you need to select one of the shipping items using shipping integration

Once all that is done, on the fulfillment transaction you will see a Carrier tab appear. The third party billing information will be on this tab.

NOTE: it's important to remember that Third Party Billing is an Integrated Shipping funtionality because the shipping fees are automatically billed to one of the carriers NetSutie can communicate with. Third party billing will therefore be used whenever you use a shipping item configured to use the integrated shipping.

You must have:

at least one Integrated Carrier account configured.
configured Shipping Items using the Shipping Integration functionality (Integrated Label checkbox selected).

3 - Default Role for New Customers

You can change the default role when giving customers access to the Customer Center role by going to Setup > Company > General Preferences and selecting the role in the field Default Role for New Customers.

We don’t only sell, we rent and loan too!

2012-03-01T16:16:00.000-05:00

NetSuite doesn’t have a specific module for rental and loans (or trials) but it doesn’t mean that the process can’t be supported. In this post, I will show you how to simply implement these processes using existing NetSuite functionality.

Figure 1 General Rental Process

Before we start, there are a few features that are going to be needed to setup the rental process correctly:

Inventory - Multi-location inventory
Advanced Shipping (fulfillment)
SO, Accounting, AR
Optional Features:
Serialized inventory ( to track the item)
Advanced Billing ( to billing schedule for invoicing)
Bins ( another way to handle inventory available for rental)

Inventory Locations

The first thing to do is to create two inventory locations:

1- Available To Rent: a location where all the items available for rental are stored and separated from the inventory available for sales.

2- Inventory at Customer: a location that designates items that are rented out to a customer. It is important to set ‘Make Inventory Available’ to false to make sure that the item is not rented twice or sold while it’s in the possession of a customer.

Figure 2 At Customer Location Setup

The use of inventory transfers will move items from one location to another.

Sales Order

Next, the Sales Order form requires certain modifications to include fields specific to the rental process. I recommend creating a new form used only for the rental process.

New Sales Order Fields:

1- Return Date, a date field to specify when the return of the rental is needed.

2- Rental Inventory Transfer, a list field used to connect the Inventory Transfer to the Sales Order.

3- Failure to Return Price, a currency field that will have the cost to the client if he doesn’t return the rented item.

4- Is Rental, a checkbox field used to differentiate regular Sales Order and rental Sales Orders.

Figure 3 Sales Order Rental Fields

To create the Sales Order, it is important to setup a non-inventory item for the actual inventory item available for rental. It is the non-inventory item that is going to be used in on the Sales Order to invoice the monthly fees.

Billing

Billing can be setup in two different ways. You can use memorized transactions or a billing schedule if you have the Advance Billing setup in your NetSuite instance. The main difference is based on the quantity and price that is going to be setup on your non-inventory item. In the case of a memorized transaction, the price will be the cost of one period and the quantity will be the number of periods covering the length of the rental. In the case of a billing schedule, the price will be the cost of the entire length of rental and the quantity will be the percentage of each period compared to the total length of the contract.

It is important to note that Advance Shipping is needed to have the ability to bill before fulfilment because the rental Sales Order is not actually fulfilled before the last payment is made.

Return Process

The return process, in any case, will require an inventory transfer from the At Customer location back to the Rental location. An additional location can also be created to inspect the item before making it available for rental again.

In the case of a return at the end of the contract, the Sales Order will be fully invoiced and closed.

If there is no return, a new Sales order need to be created to bill the actual inventory item with the failure to return price.

Figure 4 Rental Process in NetSuite

And there you have it; your NetSuite instance is now able to handle a rental workflow. Simple isn’t it? Happy Renting!

Author: Nader Hanna

Meet the Parents: Commissions in NetSuite

2012-02-13T17:14:00.000-05:00

NetSuite’s commissions module allows a lot of flexibility for setting up the way your company wants to pay your sales persons and/or partners. Using the commission schedules you can build complex commission plans in which you’ll be able to assign to specific people for a given time period.

A common business model is to pay commissions based on the type of item sold. Let’s take the example of an electronics company who wishes to promote the sales of video games. This company would want to give an 8% commission only for the items who fit the mould of the video game category; setting this up in the commission schedule is not as straightforward as it sounds. Using the above example we set up the basis of the schedule as follows:

We will base the Commission on Sales per Transaction Line Item, which means that the commission will be paid based on the Sales amount of the item.

The next step is to categorize the Transaction Line Item and this is where it gets interesting. You only have eight options here : Classes, Parent Classes Only, Items, Parent Items Only, Locations, Parent Locations Only, Departments and Parent Departments Only. The limitation of these categories is as follows:

First, if you choose the Items option every single item will be listed and you will have to find each individual video game item and set its commission value. This can get complicated if you have 10 thousands items. And you would need to come back and edit the schedule for each new video game item added to your system. This is simply not a viable option for our situation.

The second option is to use one of these three: Location, Class or Department. This will allow you to classify your items based on the common NetSuite locations, classes and departments fields (Setup-Company-Departments/Locations/Classes). The issue here is that you might already be using these classifications for other purposes since these fields can be used on pretty much every single record in NetSuite. Let’s say that you already use them to classify your transactions, it would be counterintuitive to use them for items also. So now you’re stuck using the limited Items category, right? Meet the parents.

If you select the Parent Items Only category you will have to set the commissions only for each parent. So, all you need to do is have an item called Video Games and set up each sub item of this category to have Video Games as their parent.

Then on the commission schedule you can select to apply a commission of 8% directly on the parent item Video Games and it will affect each child item linked to it. This gives you a lot of flexibility since you can have any number of levels in your hierarchy. Let’s say that you want to have a sub-category for Wii Games all you would need to do is create an item called Wii Games and set this item to have Video Games as a parent.

When you select Categorized by Parent Items Only on your commission schedule you will then see only the parent items, which simplifies setting up the commissions immensely since you now only have to go through your small number of categories instead of your large amount of items.

Two things to keep in mind when using the parent items for commissions:

Each new item added to the system that you want to pay commissions on must be correctly categorized (setting up his parent)
Each existing item that you want to pay commissions on must be correctly categorized also. If you have a lot of items you can use either a Mass Update or a CSV Import to update your existing items.

You now have a strong base on how to use the parent items to your advantage when setting up your commissions.

Author: Frédéric Jannelle

February: Tips & Tricks

2012-02-02T15:18:00.001-05:00

1 – Find deleted record information

Ever needed to find information from a deleted record? In order to see records in NetSuite that have been deleted, modified, or added, there is a hidden page in NetSuite that can be found using the Global Search. The page is called “Audit Trail Page”, and you can search based on record type, user having done the modification, and date of the modification.

2 – Pass a filter as a parameter to a saved search, from a hyperlink on a record

It is possible to pass a filter as a parameter to a saved search, from a hyperlink on a record in NetSuite. For this example: a hyperlink on Departments will redirect the user to a search displaying the list of Employees for this department.

1. Create the saved search of employees: in the "Available Filters" tab, add a filter for "Department" and Show in Footer = yes.

2. Preview the search: all employees are listed. Now filter on any Department using the dropdown "Department" in footer. Look at the URL and note the DEPARTMENT=xx (where xx is 11 in this example):

https://system.netsuite.com/app/common/search/searchresults.nl?searchtype=Employee&Employee_DEPARTMENT=11&sortcol=Employee_USETIMEDATA_raw&sortdir=ASC&csv=HTML&OfficeXML=F&pdf=&style=NORMAL&report=&grid=&dle=

3. We now have the URL to paste in the custom field of Departments. In this field of type Hyperlink:

Uncheck "Store Value", Enter a label in "Link Text" that will hide the URL, * In the Validation & Defaulting tab, enter the URL as Default Value, with a little wrapping and by changing DEPARTMENT=11 by DEPARTMENT={internalid} :

concat(concat('https://system.netsuite.com/app/common/search/searchresults.nl?searchtype=Employee&Employee_DEPARTMENT=',{internalid}),'&sortcol=Employee_USETIMEDATA_raw&sortdir=ASC&csv=HTML&OfficeXML=F&pdf=&style=NORMAL&report=&grid=&searchid=251&dle=')

5. The Hyperlink on a Department will now call the saved search passing it the Department's internal id.

3 – Tax Codes – Advanced Features

NetSuite determines the proper Provincial Sales Tax (PST) code through the following criteria in the following order:

1. If the customer shipping address is not in Canada, the tax code that will be used is the one selected in the Tax Code for International Sale field on the Canada tab at Setup > Accounting > Taxes > Set Up Taxes .

2. If the customer record is marked PST Exempt on the customer record, then the tax code that will be used is the one selected in the Tax Code for Out-of-Province Sale field on the Canada tab at Setup > Accounting > Taxes > Set Up Taxes . Also, see Processing Goods and Services Tax (GST) Refunds -Canadian Edition/Nexus.

3. If the province in the customeru2019s shipping address is the same as yours, then, the tax code selected on the item record is used. If another value is selected in the tax item field of the customer record, then that value is used.

4. If there is a tax code that matches the province on the customeru2019s shipping address, the matching tax code is used.

5. If there is no tax code that matches the province on the customeru2019s shipping address, the tax code that will be used is the one selected in the Tax Code for Out-of-Province field on the Canada tab at Setup > Accounting > Taxes > Set Up Taxes . NetSuite determines the proper Provincial Sales Tax (PST) code through the following criteria in the following order:

6. If the customer shipping address is not in Canada, the tax code that will be used is the one selected in the Tax Code for International Sale field on the Canada tab at Setup > Accounting > Taxes > Set Up Taxes .

7. If the customer record is marked PST Exempt on the customer record, then the tax code that will be used is the one selected in the Tax Code for Out-of-Province Sale field on the Canada tab at Setup > Accounting > Taxes > Set Up Taxes . Also, see Processing Goods and Services Tax (GST) Refunds -Canadian Edition/Nexus.

8. If the province in the customeru2019s shipping address is the same as yours, then, the tax code selected on the item record is used. If another value is selected in the tax item field of the customer record, then that value is used.

9. If there is a tax code

Use of an Online Customer Form as a URL listener to create Leads in NetSuite

2012-01-17T15:28:00.001-05:00

Here is a little trick to use the built-in NetSuite Online Customer Form to receive data from an URL and create a NetSuite Lead. This is accomplished without a click from the end-user, and without their knowledge of going through NetSuite.

By avoiding the use of a script (Suitelet), this allows for easier customization and less implementation time, as setup and maintenance can be done by someone with only limited HTML knowledge. The main application of this trick is online advertising or re-use of an existing Contact Us form; as soon as you generate an URL with proper parameters, you can create a Lead in NetSuite!

In this tutorial, we will create a NetSuite Lead for customer Carey Price of the company ERP Guru having the email test.mail@erpguru.com

This tutorial has 3 parts:

Set-up the Online Customer Form in NetSuite
Write some hacked HTML
Test-it!

Note: this is a hack, unsupported by NetSuite.

PART 1: SET-UP THE ONLINE CUSTOMER FORM IN NETSUITE

1) Create a New Online Customer Form:
Setup > Marketing > Online Customer Forms > New and choose "Custom HTML Template"

2) In the “Select Fields" tab of the newly created Online Customer Form, add the fields you need to populate in the future NetSuite Lead.
Add First Name, Last Name, Company Name and Email. For every field, be sure to select "Hide = Yes":

Note: Keep in mind that problems with mandatory fields will be displayed in a page with a NetSuite logo, which kind of "breaks" the invisibility of the whole process.

3)In the "Set Up Workflow" tab of the Online Customer Form, indicate what NetSuite should do once it receives the date from the URL.

For this example we will choose to create a "Unqualified Lead", with the source set to "Web". (It is also possible to notified a Sales Rep if so desired)

The beauty of this tab is the "Redirect to URL" field, where it is possible to choose a page where end-user will land without knowing that a Lead was created in NetSuite.

PART 2: WRITE THE HTML CODE

4) The next step is the creation of a form template that contains the "hacked" HTML code our Online Customer Form will use.

In the Online Customer Form, click on the field "Template" and select "New":

You will be directed to the "Online Form Template" page, where you need to paste the HTML code from the step below.

5) The HTML code you need to paste in your Online Form Template has 2 important sections:

A: A <NLFORM></form> section containing every field used in step 2 of this tutorial. In the example at step 2, First Name, Last Name, Company Name and Email were used. Thus the HTML template will contain <NLFIRSTNAME>, <NLLASTNAME>, <NLCOMPANYNAME>, and <NLEMAIL>.

B: A custom code placed AFTER the </html> tag, which will override the window.onload function inserted by NetSuite, will submit the form as soon as it's loaded.

The creation of a function "submitLead" is recommended, to be called by window.onload as seen in the below image.

Please note that by default the form name created by NetSuite is main_form. This name is used when writing document.forms[« main_form »].submit(); that submits the form.

You can download the template used for this example HERE.

PART 3: TEST IT!

6) Save the Online Customer Form and go to it's 'External' tab to get the Publishable Form URL
This URL should begin with "https://forms.netsuite.com/app/site/crm/externalleadpage.nl?"

7)Append parameters to this URL with "&".

In the example in step 2, the fields First Name, Last Name, Company Name and Email were used. Thus the following parameters need to be appended to the Publishable Form URL:

&firstname=Carey&lastname=Price&companyname=ERP Guru&email=test.mail@erpguru.com

8) Paste the complete URL in your browser, press Enter and go to your NetSuite Lead List:

Example:
https://forms.netsuite.com/app/site/crm/externalleadpage.nl? compid=0000000&formid=2&h=00000000000007e565a1&firstname=Carey&lastname=Price &companyname=ERP Guru&email=test.mail@erpguru.com

The lead "Carey Price" is created in NetSuite without the need of a single click. Notice that you should have landed on the Online Customer Form "redirect to URL" without seemingly going through NetSuite.

WHY THIS IS MAGIC:
You can add fields with NetSuite's UI and adjust the form template with basic HTML knowledge, all without scripting!

KNOWN IMPACT OF THIS HACK:
When generating the Online Customer Form, NetSuite adds a bunch of generic code. As we override the window.onload function, the page_init() function added by NetSuite is never called. Thus, you may need to add some code to your custom submitLead function.

For your information, here is the content of the page_init() never called:

*************************************************
function page_init() {
window.weekstart ='1';
window.dateformat ='DD-Mon-YYYY';
window.longdateformat ='DD Month YYYY';
window.timeformat ='fmHH24:fmMI';
window.timeformatwithseconds ='fmHH24:fmMI:SS';
window.datetime_am_string ='am';
window.datetime_pm_string ='pm';
window.datetime_short_months = new
Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","");
window.phoneformat ="(123) 456-7890";
window.isvalid = true;
setWindowChanged(window, false);
setIsInited(window,false);
page_init2();
page_init3();
setWindowChanged(window, false);
page_init4();
setIsInited(window,true);
window.status = '';
}
function page_init2() {
}
function page_init3() {
}
function page_init4() {
nlapiPageInit('create');
nlapiSetFieldValue('nsbrowserenv', 'istop=' + (window==top ? 'T':'F'));
}
**********************************************

Written by: Félix Guinet

Special thanks to Guillaume Bouchard Lafond and Pablo Herrera

January: Tips and Tricks

2012-01-05T11:37:00.001-05:00

1 -Date Shortcut

Easy tip: For any date field in NetSuite it is possible to simply hit the date box and enter "T" for today's date to appear.

2 - Free Shipping with an Item

It is possible to offer free shipping promotions in NetSuite when customers buy certain items. When this preference is used, you must select whether only one of the items in the promotion can make an item ship for free, or if all the items must be purchased for the order to ship for free.

When this preference is used, the shipping for the entire order automatically adjusts to zero when the item(s) is added to the cart.

In order to offer free shipping on certain items you must:

1. Go to Lists > Accounting > Shipping Items
2. Select "Edit" next to the shipping item you would like to be free when the items are purchased (or, alternatively create a new shipping item for the promotion)
3. On the Free Shipping Items subtab, select the item(s) that should be offered for the promotion sans shipping charge for the specified shipping method and click Add (you can also click the Add Multiple button to quickly add several items).
4. Save

In order to require all items selected to be on an order before free shipping is offered, check the All Items Must Be Purchased box.

New PST for Quebec (as of January 1, 2012)

2011-12-21T22:12:00.006-05:00

(une version française ci-dessous)

The PST for Quebec will be increasing from 8.5% to 9.5 % on January 1, 2012. In order to avoid any unpleasant surprises, a few changes must be made in the NetSuite accounts of Quebec users. Below are the steps on how to do so:

Step 1: Create a new Tax Code for the new PST rate

Go to Setup --> Accounting --> Tax Codes
Click on New
Enter a Tax Name and optionally a Display Name and Description (example: PST_QC_9.5)
Enter the rate as 9.5%
Set the Province to Quebec
Set the Tax Type to PST
The following values also need to be set (if they weren't already)
Set the Tax Agency to Ministère du Reveny QC
Set the Purchase Tax Account to: PST Expenses QC
Set the Sales Tax Account to: PST Payable QC
Click Save

Step 2: Create a new Tax Group

Go to Setup --> Accounting --> Tax Groups
Click new to Create a New Tax Group
Enter Tax Code CA-S-QC-2012
Set Province to Quebec
In GST/HST select the applicable GST Tax code
In PST, select the new PST Tax code created in Step 1 (PST_QC_9.5)
Make sure you check the Piggy Back box in order to have the PST calculated after the GST has been calculated.
Click Save

(Optional) Step 3: Create a new Tax Schedule

Only complete step 3 if you have enabled the Advanced Taxes Feature
Go to Setup --> Accounting --> Tax Schedules
Click New
Enter a relevant name for the schedule in the Product and Description fields
In the Province List, modify the Sales Tax Code what was created in Step 2 (CA-S-QC-2012)
If needed, modify the Purchase Tax Code for QC as well
Click Save

Step 4: Set the new tax Group as default on January 1st, 2012

Go to Setup --> Accounting --> Setup Taxes
Set the Default Tax Code to the one defined in Step 2.
Save

If you have a tax schedules attached to items and/or relationships, those items will also have to be updated with the new tax schedule.

Please Note:

For the first few months of 2012, extra care must be taken when creating new transactions. Ensure that you are using the new Tax Schedule and not the previous one. The Old Tax Code will be used if you have Credits, RMAs etc.

ERP Guru has provided the above information in for you to perform the necessary steps yourself. However if you'd rather have us make the modification in your account, it will be our pleasure to assist you.

More information on the new tax rate can be found on the Quebec Goverment website.

Nouveau taux de taxe de vente du Québec ( TVQ)

La TVQ augmente de 8.5% à 9.5% le 1er janvier 2012. Afin d’éviter des effets non désirés, certains changements devront être apportés à la configuration des taxes dans votre compte NetSuite. Vous trouverez ci-dessous, les étapes à suivre afin de faire les modifications nécessaires.

Étape 1: Créér un nouveau code de tax pour la TVQ:

Configuration --> Comptabilité --> Codes fiscaux
Cliquez sur Nouveau
Entez le nom (example: TVQ_QC_9.5)
Entrez le taux de 9.5%
Mettre la Province à Québec
Mettre le Type d’impôt à PST
Les valeures suivants doivent aussi être configurés
Mettre le centre des impôts à: Ministère du Reveny QC
Compte de taxes sur les achats : PST Expenses QC
Compte de taxes sur les ventes: PST Payable QC
Cliquez sur Enregistrer

Étape 2: Créer un nouveau groupe de taxes

Configuration --> Comptabilité --> Groupes impôts et taxes
Cliquez sur Nouveau
Entrez le Code fiscal: CA-S-QC-2012
Mettre la Province à Québec
Dans la case GST/HST, selectionnez l’item approprié.
Dans la case Taxe sur les ventes, selectionnez le code de taxes de l’étape 1 (TVQ_QC_9.5)
Cochez la case Gigogne
Cliquez sur Enregistrer

Étape 3 (Optionelle): Créer un nouveau barème d’imposition

Complètez l’étape 3 uniquement si vous avez activer la fonction Impôts et taxes avancés
Configuration --> Comptabilité --> Barèmes d’imposition
Cliquez sur Nouveau
Entrez un Nom dans la case produit.
Dans la liste des provinces, modiez le code fiscal pour les ventes et le code fiscal pour les achats. Entrez la valeure de l’étape 2(CA-S-QC-2012)
Cliquez sur Enregistrer

Étape 4: Établir le nouveau code de taxe comme valeur par défault le 1er Janvier 2012

Configuration --> Comptabilité --> Configurer les impôts et taxes
Sélectionnez le Code fiscal par défaut (Étape 2)
Enregistrer

Si vous avez des barème d’imposition/taxes directement sur les items ou sur les dossiers de clients, il faudra mettre à jour ces informations.

Veuillez noter:

Pour les premiers mois de l’année 2012, les utilisateurs du système devront faire attention aux codes de taxes sur les transactions. Assurez-vous d’utiliser le nouveau code de taxe. L’ancien code de taxe devra rester actif pour les crédits et retours de marchandises.

ERP Guru vous donne ces instructions afin que vous puissiez faire les modifications nécessaires. Cependant si vous avez besoin d’assistance ou si vous désirez que nous prenions en charge les modifications pour vous, il nous fera plaisir d’effectuer les travaux.

Vous pouvez avoir plus d’information au sujet de la hausse de taxe à l’adresse suivante.

Increasing Functionality of the NetSuite Component for Talend Data Integration

2011-12-19T14:29:00.004-05:00

Overview
Since discovering tNetsuiteInput (read about it here) on Talend Exchange, the integration team here at ERP Guru have dedicated resources to build new functionalities for it. The baseline concept of the component was good, however it was lacking certain key features that would greatly benefit any project. Consequently, this is why our integration team has added multiple functionalities, such as:

Between and NotBetween search operator support
Die on error option
Request level authentication
Support for Multi and Enum type search
Support for CustomRecord search
Support for joined search

So let's walk through the steps required to build a simple project with both input and output components. We will first search for an Employee with a specific email address, then take its internal ID to update the email in our NetSuite instance. (NOTE: This process would normally take hours to develop but with the new components it will only take a few minutes)

Steps

1. Import components from Talend Exchange

To begin, open Talend Integration Suite and create a new project. Once this step is completed, you need to uninstall any previous version of the component and then download both the Input and Output components from Talend Exchange.

You can download both components from the Talend Exchange website, or, alternatively, using the interface in Talend Studio as shown in the image below. Please ensure that you download the latest revision of the components.

Please note that at this point, some issues may appear. If you where using the old version of the component, there may be a conflict between the old and new version. You can trigger the Reinitialization of the Generation Engine by hitting Ctrl+Shift+F3 keys and changing the WorkSpace.

2. Add components to job

In the component palette, navigate to Business > NetSuite, you can now drag and drop both NetSuite componenta to your job. For the purpose of this walk through, you may also add three other components: two tLogRow and one tMap. Arrange them as shown in the following image.

The component tNetSuiteInput_1 will be used to fetch data according to the values defined in the user interface. These values allow you to search for normal body fields but also custom fields. In this version, you can now add even more filtering options by defining a record to join to the search.

3. Configure components

Some configuration of the components is required. You need to set the valid credentials of a NetSuite account for both Input and Output components. The employee used to access the NetSuite instance must have Web Services rights enabled with the proper role. For this example, you will select Employee for Record Type. The schema then need to be edited. Delete every field except for InternalId, ExternalId, Email and ExpenseLimit, so that only those fields will be propagated into the Main Flow of Talend.

You then need to define what are your search criteria. Here, we are going to search for an Employee with the ExpenseLimit between 200.0 and 800.0.

With this step done, you can now double-click on the tMap_1 component. For this example, propagate only InternalId, ExternalId, and ExpenseLimit to the right-hand side, then click on the field row2. ExpenseLimit and write an equation that adds 25.

You now need to tell the Output component which operation to do. In this case, we will want to set the new ExpenseLimit to the Employee that we just found. In the Output component, click on the Action drop down list and select "update" you will also need to change the Record Type to "Employee" to match the Input type.

4. Run job

Now that we have finished the configurations of our components, we can run it!

You can see a similar result in your Run console. In this case, we only had one Employee that was matching the search criteria. The Output component automatically adds two columns to the main flow that will help you debug your Job by providing a Boolean according to the success of the Action performed. Should the action fail, a simple message is returned to the component for debug purpose.

Do not hesitate to contact us for more information on these components and on our offerings regarding NetSuite integrations.

December: Tips and Tricks

2011-12-07T10:59:00.005-05:00

1 – Adding an email signature in NetSuite

When communicating with customers, it may be desirable for emails to have a specific name format and arrive from an email address other than that stored in NetSuite for the primary user. For example, the marketing rep may have a user email address of firstname.lastname@company.com, but would like their emails to come from marketing@company.com.

This can be accomplished in NetSuite by going to Home > Set Preferences and under User Profile in the General subtab. Fill in the following:

· Nickname

· Signature

o HTML Code is valid

o If you want the signature to show up in all emails, then select the “Add Signature to Messages” box; if you want it only on specific emails, use a CRMSDK tag from within specific emails (ie, NLSIGNATURE)

· From Email Address (this can be any email address)

2 – Changing the Default Landing Page on Login

It is possible in NetSuite to set your primary dashboard to another Tab Dashboard other than the Home Dashboard (ie, Transactions, Reports, Activities, etc.).

To set a different Tab as your landing page on login navigate to Home > Set Preferences and select the Appearance subtab.

Under the Centers & Dashboards section, look for the “Landing Page” field, and select the tab that you would like to set as the default dashboard when you log in to NetSuite and save it. Now, when you log in to NetSuite, the selected dashboard should be the first tab you see.

3 –Merging or Deleting Duplicate Records in NetSuite

NetSuite provides the ability to detect and manage duplicate records. Searches performed for duplicates of customers, contacts, partners and vendors have the ability and offer the opportunity to merge duplicates into one record.

Users can specify criteria for records that are duplicates, records that are not duplicates, and which record to keep as the master record. It is also possible to define how to handle duplicate search results (either delete the duplicates or merge information from duplicates into a single master record).

To set these preferences, navigate to Setup > Company > Set Up Duplicates Detection Feature and set up accordingly.

Shipping Rules: Tips, Tricks and Best Practices

2011-11-21T12:57:00.001-05:00

With so many different items and so many different ways of shipping them, no wonder there is the need to expound shipping rules to accommodate the way we charge for shipping. How do we go about doing this in NetSuite?

Shipping items can be applied to sales orders and made available for customers to choose from on web store orders. This allows for a variety rates and shipping methods to be available. Below is a brief summary and suggestions for setting up shipping items.

Shipping and Handling Rates

NetSuite has 6 different ways of calculating shipping rates and 6 ways of calculating handling.

Shipping Rate Options

· FedEx/UPS Real-Time Rate - Select a real-time rate option from FedEx or UPS for shipping within the US or internationally. You must register your FedEx and/or UPS accounts at Setup > Set Up Shipping to use this option. (This option is great to bill exactly what the carrier charges)

· Flat Rate – Charge a flat rate for shipping on all orders.

· By weight – Enter information to charge by item weight.

· By Item – Charge shipping based on the shipping cost on the item's record. Enter a default amount for items that don't have a shipping cost.

· Percent of Total – Charge shipping as a percent of the total.

· Shipping Table – Charge shipping based on a range of order totals or order weights. When you select this option, you can enter a starting value (can be as low as 0). The charge of the first line applies up to the value of the second line, and so on.

Handling Rate Options

· No Handling Charge - Select this option if you do not charge for handling. (default value)

· Flat Rate - Enter the amount to charge for every item you ship.

· By weight - Enter the amount to charge based on item weight.

· Per item - Charge handling based on the handling charge on the item's record. Enter a default amount for items that do not have a handling charge.

· Percent of total - Charge handling as a percent of the total.

· Handling Table - Charge handling based on a table of charges you create.

Free Item Shipping

A list of items that incur free shipping is also available, but please note that any order that contains at least one of these items will result in the full order be free of shipping charges. If it is important not to zero out the shipping costs of other items, other options should be used such as charging by item which allows you to set a pre-defined charge for each item. In this method, we can set all of our free items to 0 cost, and have specific costs associated to all other items.

Shipping and Handling Rules

This tab section is where we can elaborate on the shipping costs as well as define when this shipping item is available for our web store customers. This is also where we determine the tax applicable to the shipping charges.

Various limit values can also be set in this tab to indicate minimum and maximum shipping costs, as well as having free shipping over a certain order amount. These are particularly useful for weights and percent of total to prevent extremely low charges like those under 1$ for example, or over 100$.

Four separate rules are defined to determine availability of this shipping item.

Ship To: To determine either the countries that are eligible or not eligible for shipping, based on shipping address. This is the only rule available outside of web store orders.
Web Site: To determine which of your web sites allow or disallow this shipping item.
By Total: To determine availability based on order total.
By Weight: To determine availability based on order weight

Combining Shipping Items

All of the preceding options for rules and rates can be further fine tuned by setting up separate shipping items with complementary rules to allow more complex shipping costs.

For example, one could set up two shipping items, one used for US customers, another for international customers. US customers could use the first item with a flat rate, and the international shipping item could be rated based on shipping weight.

Another example would be 2 shipping items within the same country, one based on total percentage, the other based on weight. The following table shows the customer options when both are available.

Order Cost	Order Weight	shipping item (5$/lb)	5% of total
100$	4 lbs	20$ shipping	5$ shipping
500$	4 lbs	20$ shipping	25$ shipping
100$	8 lbs	40$ shipping	5$ shipping
600$	1 lb	5$ shipping	30$ shipping

This next table shows the costs when applying complementary rules for order amounts, calculated by weight for orders under 300$, and calculated by percent of total when over 300$.

Order Cost	Order Weight	Less than 300$, shipping item (3$/lb)	300$ or more, 5% of total
100$	4 lbs	20$ shipping	N/A
500$	4 lbs	N/A	25$ shipping
100$	8 lbs	40$ shipping	N/A
600$	1 lb	N/A	30$ shipping

November: Tips and Tricks

2011-11-07T11:42:00.008-05:00

1 - Grouping Inventory Items

With online retailing, some item types group items together into a single product. Some products may be sold sometimes in a group (ie, a collection of albums), some may come in a kit (ie, magic kit), and others are assemblies.

✓ Item Group: When you add a group to a purchase or sales transaction, individual lines are added for each member of the group, with a subtotal line showing the entire group’s value.
The price of a group is always equal to the sum of the prices of its members. When you fulfill an order for a group, each member item appears as a separate line item and you fulfill them as if they were sold separately. Groups can’t be sold in a NetSuite Web store.

✓ Kit/Package: The greatest difference between groups and kits is that you may assign a price and revenue account to a kit, independent of the prices of its members.
When you fulfill an order for a kit, it appears as a single line item — in other words, you can’t fulfill different components of a kit at different times.
Both kits and groups may contain inventory items, but don’t have their own on-hand stock count. Kits can’t be purchased, but they may be sold in a NetSuite Web store.

✓ Assembly/Bill of Materials: You build assemblies out of other items, then use them like regular inventory items — tracking how many are in stock, committing them to orders, and fulfilling them as a single line item.

Assemblies may contain inventory, non-inventory, other charge, service items, or even other assemblies. You may stock assemblies by purchasing them as well as building them, and they may be sold in a NetSuite Web store.

2 - File Download with online form

NetSuite has the functionality to download a file after completing an online form. Before someone can download the file, they will be required to complete a form.

In order to do so, you need to have the desired document uploaded into the Filing Cabinet, and create the online form (Setup > Other Setup > Online Customer Forms) with the desired fields.

Once you have your file uploaded and the form created you can publish your file download with online form. Go to Website > Publishing > Publish Forms >New and select form type "Download file with online form".

There are 3 main fields to set up:

Select the online form (the one you created) that will have to be filled out prior to downloading the form
Select the downloadable file from the File Cabinet
Select the category on the website to publish the form

Once you have saved, any links to that document with the form ID will be rerouted to the form before the document may be downloaded.

3 - Using NetSuite's List Shortcuts

It is possible to find records quickly in NetSuite via list shortcuts. There are lists provided in NetSuite for all the major record types including customers, transactions, opportunities and events.

To sort a list by a particular column, click the column header; an arrow in the header will show whether the sort order is ascending or descending. Clicking the header again will reverse the sort order.
Lists always display a footer section at the very bottom of the page, and the section will contain filters that allow you to adjust list results
The footer section contains a View drop-down list. Views allow you to change the columns of data in the list, or to filter the results.

CSV Import Tips, Tricks and Best Practices

2011-10-24T11:49:00.007-04:00

The following are Tips and Tricks from ERP Guru's CSV Import Manual, which documents our collective learnings about doing CSV Import migrations in NetSuite. For simplicity's sake, I have extracted the top few things that should be helpful on any CSV import.

VLOOKUP

The “vlookup()” Excel function is the single most important tool in your arsenal. It is a cross-referencing function. The syntax is as follows -

=vlookup(SOURCE, LOOKUP RANGE, COLUMN TO RETURN, false)

SOURCE: This is the value you are looking for in the following LOOKUP RANGE. This is normally a single cell in the sheet you’re running your formula from

LOOKUP RANGE: This is a large matrix from which you’re trying to match data. This can be a selection of as many columns as you want. Excell will however run it’s match on the FIRST column of your selected range. All other columns are simply potential values to return.

COLUMN TO RETURN: This is a numerical value. Writing 1 here would mean you’re returning the data from the first column from the matched row from your range. Something else (like say 5) would similarly return the value from that column. Obviously, your LOOKUP RANGE must contain enough columns to match the column number you want!

false: This is always false, don’t ask yourself any questions. It’s false.

As this is a basic Microsoft Excel command, you should be able to find plenty more information and help on it by Googling it.

Build New External ID

This is the single most important point of any migration work. ALL record types should have a new External ID constructed, which will be used in all CSV migration efforts to positively and uniquely identify the record.

The New External ID column should be built by taking a existing unique identifier and tagging on suffixes or prefixes to make it further unique and classify the data.

For example, in a simple scenario of migrating Item records from one Netsuite account into another account, the New External ID could simply be [old internal id]-[some suffix] (ex: 12345-USA).

For more complicated imports, such as the case of One World migration, it is best to identify per-subsidiary data by using as suffix the internal id of the target subsidiary. For example, if migrating an invoice to a Europe subsidiary whose internal id is 5 in the new OW account, the New External ID might be 12345-005. It is also acceptable to use a country suffix (for example 12345-UK).

If possible, this Next External ID should be built into the source account’s Export Search, using Formula (text) field. The formula should be something like concat({internalid},’-UK’)

Map New External ID as key

As we were very careful to create New External ID keys during the Preparation phase, during our Import phase we now want to make sure to map this everywhere. New External ID should be mapped on ALL records we create. Further, Internal ID should NEVER be used anywhere - always External ID (unless you are specifically running an UPDATE of existing record, as opposed to creating new data). Any joined record being called by its external ID should also be mapped, by pressing on the little pencil icon and setting the reference field to External ID.

Verification by Key building

To quickly and effectively verify lines of data, we use the concept of a “data key”. This is basically done by concatenating all the fields that are validation points into one giant string, and then running a VLOOKUP between your two files. For example, let’s say I have the following CSV snippet

	A	B	C	D	E
1	External ID	Department	Customer ID	Amount	Date
2	1123-AAA	Admin	1002	54.99	10-25-2011

My verification key will be built using this formula:

=A2&” “&B2&” “&C2&” “&D2&” “&E2

Which will result in the following string:

1123-AAA Admin 1002 54.99 10-25-2011

So, if I do the same in my other file (being careful to use the right value for Internal ID/External ID so the two match), in one VLOOKUP operation I can check if I have perfect matches on my Verification Keys. If I do, then I have just validated the entire record. I can then focus on investigating all the lines that did not match up to understand what my data problems might be.

One debugging technique is to scale down the number of fields which my Validation Key comprises of. I could for example take out Department and see if my data matches at that point - if it does, I now know I have a Department problem. for this reason, it is recommended that Validation Key and VLOOKUP formulas be kept “live” (e.g. not copy/pasted to values only) for as long as useful, that way changes can be made quickly without the need to retype the entire formula.

October: Tips & Tricks

2011-10-05T15:06:00.007-04:00

1 - Preferred Vendors

You can assign a preferred vendor for items for purchase or resale. This can make things easier in that when you create a new purchase order, if the preferred vendor of the first item will be selected. In addition, if you dropship an item, NetSuite will use the preferred vendor automatically.

You can also link multiple vendors to an item. With administrator rights, click on the Setup tab, then Company->Enable Features. From the “Items & Inventory” tab, select the “Multiple Vendors” checkbox, then click Save.

Now when you edit an item, you will see a drop-down list of vendors on the basic tab. Select the “Preferred” box next to one of these to make it your preferred vendor for that item.

2 - Optimizing for search engines

If you are using NetSuite to host your website, there are a number of things you can do to make sure you get the best-possible rating from search engines:

Change and add new content regularly.
Ensure any page on your website can be accessed in five clicks or fewer so that customers can find the page they’re looking for more easily.
Ensure the names of website sections and links are descriptive. The more descriptive the links are, the more likely the search engine will bring people to the right page.
Make use of page titles by entering text in the Page Title field on website items.
Use meta tags to add text describing the content on a page.
Use links between pages to make it easier to jump from one page on your website to a related one.
Add alt-text for images

3 - Dashboards
NetSuite dashboards provide ultimate flexibility in organizing your workspace. Setting up your dashboard to meet your individual needs is one of the most important and effective ways to ensure success in NetSuite and to give you an edge over your competition.

NetSuite offers many kinds of portlets so you can display the data you need:

✓ Activities: Shows daily and upcoming activities, with links to detailed activity records.
✓ Custom Search: Shows the results of a saved search. Custom search portlets display three columns of data when placed in the right or left columns of a dashboard, and eight columns when placed in the center column of your dashboard.
✓ Lists: Shows the type of records you choose with links to view (and edit, if you have permission) individual records.
✓ Key Performance Indicators: Shows summary data and key business metrics, with options to highlight results that don’t meet defined thresholds, and to show comparisons between date ranges. As shown in Figure 4-1, the KPI portlet brings your critical business data to the forefront of the dashboard.
No matter what role you use or what job function you serve, you’ll want at least one KPI on your dashboard. If none of the standard KPIs meet your needs, you can create custom KPIs using saved searches.
✓ KPI Meters: Shows a graphical meter of KPI data, with options to flag metrics that meet a specific threshold and to compare across date ranges.
✓ KPI Scorecard: KPI scorecards can provide complex comparisons among multiple KPIs over multiple date ranges or accounting periods. Scorecards also can include Excel-like formulas with KPIs and functions in their expressions.

Dynamically Displaying Tax Codes in PDF Layout

2011-09-20T14:02:00.000-04:00

NetSuite has the capability to display the actual tax code of a transaction on any PDF printout. If you track taxes for multiple nexuses, and use tax schedules to determine the tax treatment of items sold in each nexus this could be useful.

The following preferences are available when configuring tax for nexuses except Australia, Canada, Japan, Philippines, UK, and US.

In this example, I use the Estimate record/form to display the actual tax code that is used on the transaction.

Here are the steps that you will need to follow to achieve this:

1. Navigate to Setup > Accounting> Setup Taxes > United States > Per-Line Taxes on Transactions = T

2. Setup > Accounting > Setup Taxes > Germany (or any other supported nexus) > Print Tax Code Summary on Sales Forms = T.

3. Customize your transaction form of interest.

4. Change the PDF layout to Transaction Layout with Tax Summary.

5. Customize the Transaction Layout with Tax Summary PDF Layout by navigating to Setup >Customization >Transaction Form PDF Layout.

Online Advertising and NetSuite - NetSuite Webstores Part 5

2011-09-13T11:51:00.002-04:00

The internet has become an ever expanding resource for personal and business use. With its far-reaching consumer base, the internet's continual growth has provided advertisers access to previously inaccessible consumers via online advertising. In its most common use, online advertising is typically comprised of a variety of banner, email, and keyword advertising methods.

One of the most useful features in NetSuite is the built-in ability to track and report critical data from your online advertising campaigns. The NetSuite application includes:

Search engine campaign creation wizard
Keyword campaign management capabilities
Several pre-built reports to manage both Google AdWords and/or Yahoo Sponsored Search purchases

Through lead tracking you can obtain useful information about your company's online advertisements, such as which advertisement content generates the most click-thrus, where leads come from, and more critically, where the most purchasers originate. NetSuite allows you to track advertisements in real time, allowing you to adjust, cancel or increase the budget of an advertisement based upon its performance, which enables you to maximize your return on investment through online advertising campaigns.

Getting Started

Create Marketing Categories

Setup -> Sales & Marketing Automation -> Campaign Categories.

NetSuite comes with preinstalled categories, we suggest expounding on these.

Example: parent category = Online, children = Social Media, Google Adwords, Press Releases, Banner Ad, etc.

Create Marketing Campaigns

Example: you wish to track your Sept 2011 press release, create a campaign and assign the category "online, press releases" or you wish to track a Google Adwords Campaign related to widgets, create a campaign and assign the category "online, google adwords"

Create Your URLs

Using the campaign IDs, add the lead source to the end of each advertisements URL in your Google Adwords account (note: the number at the end of the URL must correspond with the campaign ID in NetSuite or it will not track properly).

http://www.yourwebsite.com/productpage?leadsource=XX

**append your URL with ?leadsource=campaign id

Add Custom URLs

Use the custom URLs in your online advertising. For example, if you are posting a press release online include the custom URL as a hyperlink, or use your custom Google Adwords URL in your ad.

Start Tracking and Analyzing

NetSuite will begin to capture information from the custom URLs. For example if someone clicks on your Google Ad with the custom URL:

"http://www.yourwebsite.com/widget?leadsource=27" and fills out an online form or makes a purchase, NetSuite will create a lead/customer record. This data will then appear in various reports.

Noteworthy reports:

Lead Source Analysis
Campaign Response
Campaign ROI
Sales By Lead Source

Once these lead sources are added, NetSuite will begin tracking the results of your online advertising campaigns. If tracking is properly enabled then the following data should be returned: unique visitors, leads generated,cost per lead, number of purchasers, cost per purchaser, profit, return on investment and total revenue.

From this data you can then compare and contrast the campaigns to see which has the highest ROI, and is thus the most effective online advertisement for your company/product/brand.

The NetSuite module is a tool that will help companies to establish a clear picture of what keyword marketing dollars produce at each sales stage, allowing them to make more informed decisions about their marketing efforts. That will include tracking leads generated from keywords, through conversion, to revenue, profits, and ROI.

Like this post? Check out the other parts of the Netsuite Webstore blog series:

Part 1 - Using NetSuite eCommerce as a Content Management System (CMS)

Part 2 - Adding Information Items with Pictures and Forms

Part 3 - Search Engine Optimization (SEO) and your NetSuite Webstore

Part 4 - Website Tracking and Analytics

September: Tips & Tricks

2011-09-07T11:22:00.005-04:00

1 - Automatic Image Re-size

NetSuite has the capabilities to automatically re-size images in your website. This removes the need to upload different sizes of the same image, and saves time and server space.

How it’s done:

Go to Setup->Other Setup->Image Resizing

Check the box under "Enabled" next to each type of image to be automatically re-sized.

Enter the maximum pixel height and width desiredfor each type of image.

Click “Save”

*Please note that NetSuite can only resize images less than 5 Megapixels.

2 - Reorder Points

To simplify what would otherwise be a tedious and error-prone manual process, you can set up reorder points and preferred stock levels on your item records, and place purchase orders for the appropriate items and quantities with one click by using the Order Items page.

Reorder point: When the stock level of an inventory item dips down to or below the reorder point, it’s time to order more of that item. You can set the reorder point value above zero to give yourself a buffer of items to sell until the next shipment arrives.

Preferred stock level: The quantity you want to have on hand after you restock the inventory item.

You can set or edit the reorder point and preferred stock level under the Inventory tab on your inventory item records. If you also use the Multi-Location Inventory feature, you may specify a different reorder point and preferred stock level for each location.

3 - Automatically Put Cursor in Global Search Box

A convenient short-cut for those frequenting their Global Search Box:

To have the cursor automatically displayed in the Global Search Box, simply hit ALT-G, and voila! you are good to go.

Bank Statement Automation in NetSuite

2011-08-23T10:11:00.009-04:00

Bank statement reconciliation is an important step in the accounting process. It can be time-consuming to manually manage sales from approval to deposit and reconciliation, so here are a few tips to make your NetSuite experience more pleasant.

First, let's discuss the difference between a cleared transaction and reconciled one. A transaction starts its life as neither cleared nor reconciled. When you get confirmation that the money transfer is complete, the transaction is reputed as cleared. At the end of the month, when you get your official bank statement, every transaction can then be verified against the statement, corrected if necessary, and become reconciled. One way to view it is: cleared is for you, reconciled is for the auditor.

Many NetSuite users participate in e-business and deal with a very large number of transactions, which include a credit card processor such as PayPal's Payflow. In this case, it is useful to retrieve deposits via a CSV file import. Such a file is usually provided by the payment service provider. Unfortunately, NetSuite doesn't support direct third-party payment integration, nor does it support automated importation of bulk CSV data. Ideally, we would have liked to script a process by which we can work with deposits, but bank deposits are not an exposed part of the NetSuite API. This leaves us with a rather unseemly work-around by which we have to clear each transaction and mark them as reconciled. This solution works, but it will still make the reconciliation process difficult when dealing with exchange rates, ultimatel leaving us with rounding errors.

Since we are considering the problem of automated reconciliation, a custom record with matching fields should be created to receive the csv data. It's good practice to add an extra field (checkbox) in the custom record to flag processed entries, so as to limit the number of operations when running the script, which is described shortly hereafter.

For help importing csv data, see your NetSuite Help Center

Once the data is imported, the process of creating a SuiteScript deployment, script and .js file is fairly straightforward.

1. Search for unprocessed records of the custom type we have set up for this purpose,

2. Find the matching transaction for each,

Navigating to the Cash Sales list

3. Set the Account (account) in which the payment processor makes the deposits (usually one per processor or at least one per csv file) on the Cash Sale record,

Manually setting a Cash Sale's account

4. Set the Cleared field (cleared) to t, i.e. true, on the Cash Sale record (usually found in the Chart of Accounts)

The cleared (Clr) field in the chart of accounts

Once all deposited transactions are marked as cleared, we can reconcile them with one click on the "Mark all cleared to reconcile" [sic] button as illustrated below.

Navigating to Reconcile Bank Statement

The elusive Mark All Cleared to Reconcile [sic] button

As of this writing, this is the most automated this solution can be. There's no telling what NetSuite may decide to expose through its API in the future. We can always hope...

Website Tracking and Analytics - NetSuite Webstores Part 4

2011-08-11T10:48:00.021-04:00

Web analytics is the measurement, collection, analysis, and reporting of internet data for the purposes of understanding and optimizing web usage; it is especially useful in helping to estimate how website traffic changes or fluctuates after the launch of a new advertising campaign. Web analytics provides information about the number of visitors to a website and the number of page views. This helps companies gauge traffic and popularity trends which is useful for market research.

On-site web analytics measures the performance of your website in a commercial context. This data is typically compared against key performance indicators for performance, and used to improve a web site or marketing campaign's audience response.

One of the most powerful aspects of NetSuite is its ability to produce real-time business intelligence, from simple at-a-glance dashboard widgets to comprehensive reports that compile whatever data you desire. Useful features include saved searches, KPIs directly on the dashboard, and a variety of reports.

It is very true that the inherent value in any business software is the ability to effectively analyze the data it contains. NetSuite includes a long list of extremely powerful reports, including financials and analytics (this is even more extensive if you have the SiteAnalytics module). NetSuite’s onscreen reports are interactive and can be output in a variety of document formats, including print, CSV, and PDF. You are given the capability to modify an existing report to meet your needs, or simply create a new one from scratch. Some of the reports included with the SiteAnalytics module are listed in the image below:

In addition to the standard NetSuite tracking abilities, tracking HTML and pixels provides data for both internal and external reporting tools such as Google Analytics. You can place these tracking elements in any greeting, message, or description field for items, categories, or tabs in your NetSuite site. When inputting these, ensure that you are on the HTML Source Code section in these fields before pasting the code (this ensures that the HTML mark-up is not displayed in the content).

In order to access reports you must have the correct permissions (if you do not see the Reports tab on your home page, check with your Administrator). It is important to note that your permissions define whether you can view or modify report formats, and whether you can view or modify the underlying data. As a NetSuite user, you can also set preferences in the system determining the way that you would like your reports to function.

From the Home tab menu, choose Set Preferences
Select the Reporting/Search tab
Change the options you want to set--there are several to choose from: Report by Period, Print Company Logo, Display Report Title on Screen, Display Report Description, Default Bank Account, Show Forecasts as Weighted, Minimum Forecast Probability etc.

In order to run an existing report, simply click on its name. Alternatively, you can select the 'Customize' link to go straight to the Report Builder where you can adjust the formatting and data elements (clicking this next to a financial report will open the Financial Report Builder) or select "New Report" to create your own report.

Whether you are using an out-of-the-box report, customizing an existing report, or creating your own report from scratch, NetSuite's analytics reports provide comprehensive insight to your sites traffic and popularity, and are infinitely useful in market research to give you the clearest view of your company`s online status.

Like this post? Check out the other parts of the Netsuite Webstore blog series:

Part 1 - Using NetSuite eCommerce as a Content Management System (CMS)

Part 2 - Adding Information Items with Pictures and Forms

Part 3 - Search Engine Optimization (SEO) and your NetSuite Webstore

August: Tips & Tricks

2011-08-02T13:00:00.005-04:00

1 - RSS/Atom Feeds on your Dashboard

It is possible to publish information from news sources and RSS feeds on your personalized dashboard. There are certain feeds that NetSuite offers as a default; however you can also display custom RSS feeds from other sites or create a custom feed to publish messages to users of your NetSuite account.

Display custom RSS feeds from other sites with these steps:

Choose Personalize Dashboard so that the “Add Content” pane appears.
Select RSS/Atom Feed.

To remove a feed, click the X in the upper-right corner of the portlet.

*Note that a RSS feed that an admin has set up is automatically added to your dashboard.

2 - What’s the difference between KPIs, KPI meters, and KPI scorecards?

A KPI shows a quick table of the key performance indicators that you select to be displayed. The table includes the KPI name, the period being compared (this month versus last month, for example), the current value, the previous value, and the percentage change. This portlet provides a quick overview of a number of KPIs.
A KPI meter shows how you’re doing on a particular KPI.
KPI scorecards are powerful analytical tools that are mainly used by managers to monitor the relationships among KPIs (ie, they can monitor the company’s fiscal health).

3 - Setting a minimum order amount

In some circumstances or business models, it may be necessary to require that customers order a minimum amount (ie, if credit card companies are charging you a certain percentage per order etc).

In order to set the minimum order amount in NetSuite:

Navigate to Setup >Web Site >Set Up Website
Select the Shopping tab.
Enter the minimum order amount desired for orders

Following this, any order that totals less than the amount you set will receive an error message. Alternatively, you can also set a minimum order quantities (not dollar amount) for individual items by filling out the Minimum Quantity field under the Sales/Pricing tab.

AKB9Y2RKW3DV