Thursday, February 28, 2019

nlobjResponse getHeaders Function in Detail

Notes:

  • getHeaders method is defined on nlobjResponse object
  • nlobjResponse object is created only by nlapiRequestURL
  • getHeaders method is NOT available on nlobjRequest - e.g. on Suitelet request parameter
  • getHeaders method returns an array. Each item of this array is a string

Sample code on how to list all headers:

function suitelet(request, response){	var res = nlapiRequestURL('URL'); // page is loaded and nlobjResponse object is stored in "res"	var allHeaders = res.getAllHeaders(); // get all headers from the response		//all headers will be shown in a table	var out = '<table border="1">';		//run through all headers	for(var h in allHeaders) {				//we know that header 'Set-Cookie' is a header with multiple values		if(allHeaders[h] == 'Set-Cookie') {					//split the 'Set-Cookie' header			var headers = res.getHeaders(allHeaders[h]);						//run through all headers in the 'Set-Cookie' multi-valued header. hS is header name, headers[hS] is its value			for(var hS in headers) {				out += '<tr><th>' + hS + '</th><th>' + headers[hS] + '</th></tr>';			}		} else {			//single header			out += '<tr><th>' + allHeaders[h] + '</th><td>' + res.getHeader(allHeaders[h]) + '</td></tr>';				}	}	out += '</table>';	response.writeLine(out);}

The example function can be used in Suitelets for testing HTTP headers of other NS pages or 3rd party applications.

Set Multiple Item Names as a criteria filter in an Item Saved Search

1. Navigate to Reports> Saved Searches> All Saved Searches> New
2. Select Item as Search Type
3. Assign a name on the Search Title field
4. On the Criteria tab> Standard sub tab> Filter column, add the following:
            a. Formula (Numeric) is equal to 1 then 
            b. Formula = Case when {name}='XXXX' OR {name}='XXXX' then 1 else 0 end

*Note: Replace XXXX with the exact item name. If you wish to search for more items simply add " OR {name}='XXXX' " for every item you wish to be added in the search.

5. Hit Save & Run

Saved Search to Generate a List of Item Receipts Created From Return Authorizations

1.    Navigate to Reports> Saved Searches> All Saved Searches> New

2.    Select Transaction as search type

3.    Assign name on the Search Title field

4.    On the Criteria tab> Standard sub tab> Filter column, set the following:

a.     Type is any of Item Receipt

b.     Formula (Text) contains Return Authorization then copy-paste this formula:

{createdfrom}

c.     Main Line is true (Yes)*

5.    On the Results tab> Columns sub tab, add the fields you wanted exposed on the report

6.    Hit Save & Run

Note: Criteria for Main Line may be set to true or false depending on the fields you wanted exposed on the search- if the field is sourced from the Main Line then set the criteria to 'yes'. If the field is sourced on the line item details then set the criteria to 'no'.

Access ODBC Views for Specified Account via MS Excel


Image

MS Excel can be used to connect to NetSuite ODBC interface:

  1. Open a new sheet in Excel.
  2. Navigate to Data > From Other Sources > From Data Connection Wizard
  3. Choose ODBC DSN, click Next
  4. Choose NetSuite.com, click Next
  5. In the login dialog, enter NS email and password
  6. Wait for the dialog box where the database and tables can be selected. Uncheck the checkbox "Connect to specific table" to select multiple tables
  7. Follow other dialogs to complete the report

This feature is available in MS Excel 2010 and newer.

Customized NetSuite Report Providing a Forecast of Future Billings by Month based on Billing Schedules Assigned to Sales Orders

1.    Navigate to Reports > New Report > Forecast

2.    Enter Report Title

3.    Select the field you want to report on:

Metric = Forecast Amount 

4.    Select the format of the report:

Mark the radio button for Matrix.

5.    Select how you want to subtotal the report:

Component = Entity

Field = Name (Grouped)

6.    Select how you want to summarize data across columns:

Column = Month

7.    Click More Customization button.

8.    Under Edit Columns, add Forecast: Transaction Number.

Set Column Label = Order Number

Click <-Move button to position this column after Entity

Click on Forecast Amount (Weighted) column.

Set Column Label = Amount

9.    Under Filters, add Forecast: Transaction Type equal to Sales Order, Invoice. Done. Add.

10.  Preview/Save.

Note:   This should give you a baseline custom report from which you can further enhance or modify to your business requirements.    Your individual results may vary due to your accounting and financial configuration.

Upload an Excel Document to Adaptive Insights

This article is intended for accounts integrated with Adaptive Insights, NetSuite's partner in delivering financial management solutions.


1. Navigate to Home > Reports tab.

2. On Reports tab, click on New Report drop-down menu.

3. Choose the Upload File option.

4. Enter the Name and Description.

5. Save as either Personal report or Shared report.

6. Hit Upload.

 

Users are able to use an Order Specific Type Advanced Promotion code on a site which should only be available on a different site.

When you're using Advanced Promotion and you selected Site A on the field Site, but you're able to use the Coupon Code on Site B.

Follow the steps below to make sure all pre-requisites are met:

1. Navigate to Customization > Plug-ins > Manage Plug-ins and check the 3 implementations from the page (Order Specific, Item Specific and Buy X Get Y).

Note: Enabling the 3 implementations is crucial to properly initialize the implementations needed to run the advanced promotion

2. Create an Order Specific Promotion, Navigate to Lists > Marketing > Promotions > New.
3. Select Order Specific Promotion.

Note: Advanced Promotion Codes should always start from the Advanced Promotion Form, it is important to select the correct type of Advanced Promotion when you navigate to Lists > Marketing > Promotions > New.

4. In the Site section, select Website A.
5. Enter a Promotion Name and select a Discount Item.
6. Set Apply Line Discount = T
7. Codes tab > enter a Coupon Code.
8. Eligibility tab > Order subtab > Add Order Criteria = Set Order Total to $100.
9. Discount tab > Add Order Discount > New Order Discount > Promotional Offer = $20.
10. Click Save.

Note: After doing this, applying the promotion to Website B should show an error message:

This coupon does not apply to this site.
Please correct or remove the Voucher code and click Apply

Exclude/Include Certain Types of Credit Cards from the Web Store Checkout

It can be handy to include or exclude certain types of credit card payment options from the web store checkout.

In order to do so navigate to:

1. Setup > Accounting > Accounting Lists

2. From the Type drop down menu at the bottom select Payment Method

3. Click Edit next to the type of card payment to be excluded

4. Check the Inactive option and Save

To include a new type of card hit New at the bottom right and fill up the information.

Improve Performance on Records by Changing Workflow/Script Event Type

Having multiple workflow and/or scripts on a record can slow down how fast it loads.  If some are not used in all circumstances it is better to be more specific about when they run.  For workflows this means specifying a Trigger Type, Event Type and any conditions.  Doing this will prevent the workflow from running.  For example if a workflow is only used when a record is created, then putting requirements on when it runs will prevent the workflow from looking at every action and transition in the first state if none of them will get triggered anyway.

Changing scripts so that they have an Event Type can also improve performance if there are multiple scripts on a record.

Display a Custom Description in an Item Fulfillment Email

Scenario:

While creating a sales order, a custom description is entered for the item on the sales order. But the custom description does not show in the Item Fulfillment email.

Solution:

1.Navigate to SetupSite Builder > Set Up Web Site > Click Edit next to your Website Name

2. Go to Cart tab > tick/enable the Show check box next to Sales Description

3. Click Save.

Now the custom description will display in the Item fulfillment email.

Custom Field That Sources The Transaction Total Displays a Negative Amount


SCENARIO:

User creates two custom fields for a custom record type. The first field pulls up the list of transactions and the second sources from the Total of the selected transaction:

  • Transaction (custom)
    Type = List/Record
    List/Record = Transaction
    Store Value = T
  • Amount (custom)
    Type = Currency
    Source List = Transaction
    Source From = Total
    Store Value = F

When a user selects a purchase order from the Transaction field, the Amount shows a negative value.

RATIONALE:

The sign (negative or positive) of the custom Amount field in this scenario is based on the type of transaction this amount is sourced from.

The following transaction types will render a negative amount:

  • Bill
  • Item Receipt
  • Purchase Order

The following transaction types will render a positive amount:

  • Bill Credit
  • Estimate
  • Invoice
  • Sales Order

Note: If the custom Amount field is to source the transaction Total Amount (instead of Total), the sign of the amounts for the above transaction types will be reversed.

Save or Update the Credit Card Details in the Payment Information page of the Customer Center

A customer is able to change, edit or update the credit card information by logging into their account in Customer Center.

 

This option is tied up to a Shopping Preference called "Display 'Save My Credit Card Info' field" that is found by navigating into Setup > Site Builder > Set Up Web Site > Shopping tab > Payments Page  portlet.

 

If this is check, a tick box appears in the Payment Information page of the Customer Center that is called 'Save My Credit Card Info' where they can check if there is a need to change or update the credit card that is currently saved.

 

If this is unchecked, customers cannot opt out of having their card information saved.

Access to Custom Records from the Vendor Center

Note : Vendor Center roles by default cannot be customized to be given access to Custom Record Types.

Create a custom tab:
1. Navigate to Lists > Website > Tabs > New.
2. Click New Presentation Tab.
2. Enter Name.
3. Unmark the Display In Website checkbox.
4. Audience tab > Roles > Select Vendor Center.
5. Click Save.
 
Create a custom record:
1. Navigate to Customization > Lists, Records, & Fields Record Types > New.
2. Enter Name.
3. In the Access Type field select Use Permissions List.
4. In the Permissions tab > Add Vendor Center with Edit Level.
5. Click Save.
6. In the Fields tab > Enter fields by clicking on the New Field button.
7. Click Save for each new field.
8. Navigate to Customization > Lists, Records, & FieldsRecord Types.
9. Locate the record type that was created > Click on its List link.
10. Copy the URL from the browser address bar.

Create a link to the custom record in the vendor center.
1. Navigate to Lists > Website > Information Items > New > Click Formatted Text.
2. Enter Name.
3. In the Detailed Description field switch to Source editing mode then enter the following:

  • Click <a href="place custom record list url here">here</a> to access the vendor task list.

4. If multiple site, select the web site under the Site column where the Information Item will be displayed.
5. In the Site Category column select the custom tab created above.
6. Click Save.

Workflow or Server Side Script not setting custom form

The Custom Form field by default gets set to the preferred form.  When using a script and setting the field server side, the field gets overridden by the preferred form when the record is loaded.

To fix this for workflows the trigger on must be Before User Edit or later client side (Eg. Before User Edit, After Field Edit, After Field Sourcing, etc).

For SuiteScripts this must be set using a client script.  Page Init will have it set immediately.  All other functions will work as well for client scripts.

Sample Java code to obtain Custom Transaction Forms via SuiteTalk

Here is a sample code to use getSelectValue using Java to obtain custom transaction form internal id:

private void getCustomFormInternalId() throws RemoteException

    {
    GetSelectValueFieldDescription fieldDescription =
     new GetSelectValueFieldDescription();
     fieldDescription.setRecordType(RecordType.customer);
     fieldDescription.setField("customform");
     fieldDescription.setFilter(new GetSelectValueFilter());
     fieldDescription.getFilter().setFilterValue("Standard Customer Form");
     fieldDescription.getFilter().setOperator(GetSelectValueFilterOperator.is);
     GetSelectValueResult result = _service.getSelectValue(fieldDescription, 1);

     if (result.getStatus().isIsSuccess())

    {

           RecordRef recordRef = (RecordRef)result.getBaseRefList().getBaseRef(0);
           System.out.println("Internal Id: " + recordRef.getInternalId());        

     }

    else

    {

           displayError(result.getStatus().getStatusDetail());            

     }

    }

 

Assign Task after a certain number of days, excluding Holidays

The sample script allows a customer to automate calculation of date when a certain Task or Case is to be escalated after X number of days, excluding Holidays.

Note: Holidays are implemented as custom record

 

function setTaskDateCreation(){
 
 var daysToAdd=5; //number of days based on use case
 //var dateCreated=nlapiDateToString(new Date());
var dateCreated=nlapiGetFieldValue('trandate');
var dateOfTask;

 for(var i=1; i<=daysToAdd; i++){
 
  dateOfTask=nlapiAddDays(nlapiStringToDate(dateCreated), i);
 var holiday;
holiday= isHoliday(dateOfTask);
 
  if(holiday==true) 
      daysToAdd = daysToAdd + 1;   
 }
 
 return dateOfTask;
 
 }
 
 

function isHoliday(dateToCheck){
var filters= new Array(); 

  filters[0]= new nlobjSearchFilter('custrecord_date', null, 'on', nlapiDateToString(dateToCheck));
 var results=nlapiSearchRecord('customrecord_holidays', null, filters,null);
 if(results!=null){
 return true;
 }
 return false;
 }

Upload File to File Cabinet via Vendor Center

Currently,  Vendor Center does not have standard NetSuite permission that can be customized so that they can be allowed to upload file to File Cabinet. However, a Suitelet can be deployed on Vendor Center which will function as the file upload facility. Below are the steps to work around the current limitation:

1. Create Suitelet using the following code and Deploy it to vendor Center Role:

function uploader(request, response)
{
   if (request.getMethod() == 'GET')
   {
      var form = nlapiCreateForm('Upload File');
   var fileField = form.addField('file', 'file', 'Select File');
      fileField.setMandatory(true)  
      form.addSubmitButton();
      form.addResetButton();
      response.writePage(form);
   }
   else
   {
    
        var file = request.getFile("file")
       file.setFolder(166); //internalid of Folder
  var id = nlapiSubmitFile(file);
 
  }
 
var form = nlapiCreateForm('Upload File');
var text1=form.addField('success', 'label', 'Upload Succesful!');
response.writePage(form);


   }


2. Deploy Suitelet as a custom Link on the Vendor Center Home Dashboard.

2.a. Setup>Customization>Center Categories> New
2.b. Center Type= Vendor Center
2.c. Section= Home
2.d. Category= Upload File
2.e. On Sublist link, Select the Suitelet deployment.
2.f. Label= Upload File

3. Login using Vendor Center Role and click the link "Upload File" from the Home Portlet.

 

Do I have to purchase Inbound Single Sign-on to setup SAML Single Sign-on?

There are some cases where users might think that NetSuite's feature 'Inbound Single Sign-on' has to be purchased to get started with SAML Single Sign-on. However, this should not be the case. 'Inbound Single Sign-on' does not need to be purchased to setup SAML Single Sign-on. SAML Single Sign-on is a free feature of NetSuite. One simply needs to enable SAML feature in Setup > Company > Enable Features > SuiteCloud tab and continue the setup from there.

Usually, if the user's preferred authentication mechanism is SAML, they will fall under one of the following categories. If they do, then they should keep in mind that they do not have to purchase 'Inbound Single Sign On':

a. They have bought an identity management product (for example: PingOne from Ping Identity) that aims to access a SaaS software like NetSuite through SAML.
b. They do not want the token-based Inbound SSO feature of NetSuite, and prefer to have a third-party manage their identity. Thus, they can take a look at SuiteAnswers article 'Getting started with SAML: List of third-party Identity Providers for SAML SSO' (Solution ID: 30090) to find the third-party companies that can help them with their setup.

'Inbound Single Sign-on' only needs to be purchased if the user's preferred authentication mechanism is token-based. Please see SuiteAnswers article 'Inbound Single Sign' (Solution ID: 15039) for more details about this feature.

Create a sublist with a fixed number of rows with editable fields

To create a sublist with a fixed number of rows (e.g., 'list' instead of 'inlineeditor' style), and also allow the user to edit the values in the cells of the sublist, you have to set the display type of the fields you add to the sublist.

var list = form.addSubList( 'var_record', 'list', 'List', 'custpage_list');
list.addField( 'custtext', 'text', 'Text' ).setDisplayType('entry');

Join Search Script using T and True filters that return different number of Results

Opportunity Saved Search on the NetSuite UI with the following criteria below returns three records on the Results page when run on a test account.

The equivalent script for this is seen below. However, it is returning a different number of records when results are seen which is fourteen.


This is caused by the value on the 4th parameter of the nlobjSearchFilter API. Changing the value from true to 'T' on the line number 3 on the code above will resolve the concern and it should return the same number of records when results are seen.

filters[0] = new nlobjSearchFilter('isinactive', 'salesrep', 'is', 'T'); 

Workflow > Set a specific Custom Form for New Transaction Triggered via Make Copy

Below is a sample workflow to set a specific custom form for a new transaction triggered via make copy of an Estimate.

Steps:
1. Create a new workflow with the following summary settings (Customization > Scripting > Workflows > New)
 a. Name = Default Custom Form - Make a Copy
 b. Record Type = Transaction
 c. Sub Types = Estimate
 d. Event Based = On Create: Checked
 e. Trigger Type = Before Record Load
 f. Event Type = Copy

2. Click State 1 and add a  Set Field Value action

3. Below are the settings used for the Set Field Value
 a. Trigger On = Before User Edit
 b. Condition = "Custom Form != My_Custom_Estimate"
 c. Parameters> Field = Custom Form
 d. Value> Static Value> Selection = My_Custom_Estimate

Note: The criteria for the condition in the "Set Field Value" action was set to ensure that the form is not stuck on a loop since changing the custom form causes it to reload and trigger the "Before User Edit" again.

Wednesday, February 27, 2019

Get the name of a Folder via script

To get the name of the folder we can use the nlapiLoadRecord and get the value of the name field.

Below is a sample code that gets the folder name of a specific file in your file cabinet.

function getFolderName(){

 var file = nlapiLoadFile(8401); //8401 is the internal id of one of your files in your file cabinet
 var folderid = file.getFolder();
 var folderObj = nlapiLoadRecord('folder', folderid);
 var foldername = folderObj.getFieldValue('name');
 nlapiLogExecution('DEBUG','Folder Name',foldername);

}

Get all child records of a customer via script

To get all the child records of a customer via script, we can use nlapiSearchRecord api. We just need to use joined searches.

Below is a sample script that captures all the children of a customer record.

function getChildren(){

var filters = new Array();
// 915 is the internal id of the parent customer
filters[0] = new nlobjSearchFilter('internalid', 'parentcustomer', 'anyof', '915');

var columns = new Array();
columns[0] = new nlobjSearchColumn('internalid');

var results = nlapiSearchRecord('customer',null, filters, columns);

//results would be an array of the internal ids of the child customers

for (var i=0; i<results.length; i++){

nlapiLogExecution('DEBUG','Child #'+i,results[i].getValue('internalid'));

}

}

Pass Value from Suitelet to Suitelet

Image


1. Create and Deploy a client script with the following code (Customization > ScriptingScripts > New > Client):

ID: customscript_lab_client



2. For Suitelet #1, create a form with a button and set the client script and its function to the button (Customization > Scripting Scripts > New > Suitelet):

ID: customscript_lab_suitelet1


3. Retrieve the field value in Suitelet #2 (Customization > ScriptingScripts > New > Suitelet):

ID: customscript_lab_suitelet2



When the button in Suitelet #1 is clicked, the client script attached to it will retrieve the value of the custom field and append it to the URL of Suitelet #2.  When the new window containing Suitelet #2 opens, the parameter can then be retrieved through the query string.

No Shipping Methods Available when UPS or USPS Real Time Rates is Selected in Web Store


If the message No shipping methods available for your address is shown and shoppers are not able to select a shipping method. Check the Default location for sales orders. This should be blank in order for the real time shipping methods to calculate if the address is valid.

The error is reproduced when there is a Default Location setup for Sales Order:

To Check:

1. Navigate to Setup > Accounting > Accounting Preferences > Order Management tab.

2. Set Default Location for Sales Orders to null/blank

3. Click Save

Resolve the Error "Invalid item reference key X for entity X" When Adding Items to the Shopping Cart

One possible way to resolve this error is ensuring that the subsidiary used by the Shopper during the web site session is also assigned to the Custom Customer Center role used:

1. Navigate to Setup > Users/Roles > Manage Roles
2. Edit on the Custom Customer Center role used
3. Select and assign the session subsidiary
4. Save.

Status of the Purchase Order when the Close Button is Hit

Image



If the purchase order contains Expenses only (Expense-only purchase order), closing the purchase order would result to Fully Billed Status.

If the purchase order contains Items only (Items purchase order), closing the purchase order would result to Closed Status.

If the purchase order contains Expenses and Items, closing the purchase order would result to Closed Status.

There is not much fundamental difference between the two as you could still reopen a closed (Items purchase order) or fully billed purchase order (Expense-only purchase order).

Process Invoice Using a Cash Sale

In Transaction > Sales > Create Sales Order > New, when billing a sales order it leads to a cash sale invoice. 

Factors that affect Billing Sales Order are Terms and Payment Method. Payment Method takes precedent over terms and a Cash Sale will be generated instead of Invoice.

To confirm:
1. Create a copy of a Sales Order with payment method selected in Billing tab.
2. Remove the payment method in the Billing tab.

3. Click Save.
4. Click Bill.

Note: It should now lead you to an Invoice form.



Create a Custom KPI Comparing Date Ranges of a Saved Search (i.e. This month vs. last month)


Navigate to Reports > Saved Searches > All Saved Searches > New > Customer.

1.     In the Results tab > Name, add Summary Type = Count > Add field "Date Created".

2.     In the Available Filters tab > Filter: Date Created > Show in Footer = True.

3.     Save this Search.

Add this to your dashboard: Home > Dashboard > Personalize Dashboard.

4.     Key performance Indicators Portlet > Setup > Add Custom KPIs > Select the Saved Search you just created > Range = This Month > Compare Range = Last Month > Save.

5.     Refresh the dashboard.

Expected Result in dashboard:

6.     The result should show you the number of new created customer records last month and this month

How to Show Invoices, Items and Quantity in Saved Search for Credit Memos

1. Navigate to Reports > Saved Searches > All Saved Searches > New


2. Select Transactions


3. On the Criteria tab > Standard Sub tab: Add the fields

---Type = is Credit Memo
---Created From: Type = is Invoice
---COGS Line = false
---Tax Line = false
---Shipping Line = false
---Main Line = false

4. On the Results tab > Columns sub tab :  Click on Remove All button and then Add the fields 

---Name  
---Number (Custom Label = Credit Memo #)
---Item  (Custom Label = Credit Memo Item)
---Quantity (Custom Label = Credit Memo Qty)
---Created From (Custom Label = Invoice #)
---Applied to Transaction: Item (Custom Label = Invoice Item )
---Applied to Transaction: Quantity (Custom Label = Invoice Qty)

5. Rename the search


6. Click Save & Run

Customize New Bar drop down options on Customer record

To customize the New Bar drop down options on Customer record:

1. Hover mouse to the Create New Bar drop down list.

2. Hit Personalize...

3. In the Customize New Bar page, drag and drop fields depending on your order requirement.

4. Hit Save.

Mark Up Item Class added to Billable Expense does not Display on the Invoice

As an alternate solution:

- On the Invoice > click Edit button.
- Go to the Items tab > Items subtab > add the Mark Up Item (notice: the Class will auto populate)

- Set the Price Level to = Custom

- Set the Amount (same Amount when you add the Mark Up on the Billable Expense).
- Click Save.

Create a Custom Field Hyperlink that will Search the Profile of a User in Facebook

1. Navigate to Customization > Lists, Records, & FieldsEntity Fields > New.
2. Enter the Label of the field.
3. Set the Type to Hyperlink.
4. Store Value should be unchecked.
5. In the Validation & Defaulting tab:
  • Default Value = '<a href="https://www.facebook.com/search.php?q=' || {firstname} ||'%20'|| {lastname} ||'">https://www.facebook.com/search.php?q=' || {firstname} ||'%20'|| {lastname} ||'</a>'
  • Formula = Yes
6. In the Applies To tab, select the record you want to apply this field.
7. Click Save.

Note:  This applies to entities that have the First Name and Last Name fields populated.

Non-admin User cannot Login > Error: Your role does not give you permission to view this page

First verify that the User License did not exceed the allocated number. To verify this navigate, under the Administrator role, to Setup > Company > View Billing Information > Billable Components > Full Access User and look for the related the columns for the Provisioned Quantity and Currently Used.

Note: The amount on the Currently Used column should not exceed the Provisioned Quantity column. If the license usage has exceeded the current allocation the user either frees-up user license or contact the Account Manager for the purchase of additional licenses. If the license checks out fine proceed with the following troubleshooting steps:

  1. Clear the browser's cache.  
  2. Use a different browser to login (Chrome,cMozilla Firefox, Safari or Internet Explorer).
  3. Reset the user password through the Administrator role:

a. Setup > Users/Roles > Manage Users.
b. Select User.
c. Edit the record.
d. Navigate to Access tab.
e. Set new Password and Confirm Password. 
f. Set Require Password Change On Next Login = T.
g. Save.

VAT amount in GST on Purchases Report does not match that of the Balance Sheet

When a user creates a transaction, Tax Period is based on the transaction date thereby when GST Reports are run; GST Amount will be based on the transaction date.
 
Balance Sheet report, however, is based on the Accounting Period. If the transaction date is outside of the Posting Period, there will be discrepancies in the GST amount between the two reports.
 
Sample: A vendor bill dated 03/14/2012 with a Posting Period of July 2012.

On the GST on Purchase Report, GST amount will show under March 2012 however on the Balance Sheet Report, the GST amount will show under July 2012.
 
Tax Period field is currently not yet visible in transaction forms. This is logged under Enhancement 202236

 

Non-EU countries are appearing on the Intrastat Report

This Report is filtered by Subsidiary, Default Shipping, Tax type, EC Code, and Item Type. The one that triggers International Sales/Purchases to show on EU Intrastat Report is the EC Code. This is a property set in Tax Codes for European Sales/Purchases. If EC Code is ticked for some Tax Codes, e.g. O-GB (normally used for International/Non-EU transactions), Invoices/Bills with O-GB tax code would also appear on EU Intrastat Report.

  1. Click on the Reference No link of the problem line
  2. Take note of the Tax Code
  3. Go to Setup > Accounting > Tax Codes
  4. Edit the Tax Code, ensure that the EC Code is not checked
  5. Save

 

 

EU Intrastat Report is Empty

This happens if NOTC Code is missing from the Intrastat Generic Sales Report[4873] & Intrastat Generic Purchases Sales Report[4873]. To add this:

1) Navigate to Reports > Saved Reports > All Saved Reports

2) Edit Intrastat Generic Sales Report[4873]

3) Click Edit Columns

4) Click on Tax Transactions folder > look for & click Nature of Transaction Code folder

5) Click NOTC Code

6) Put the NOTC Code column before the NoTC column that is already on the Report

7) Save

Employee Email Address Appears Twice in Global Search Result

This occurs if the Employee also has Contact Record, and same email is entered in Alt.Email field.

1. Navigate to Setup > Company > General Preferences > tick Show Employees as Contacts.

2. Save.

3. Navigate to List > Relationships > Contacts.

4. Set Type as Employee on the Filter Region to show only Employees.

5. Edit the Employee > remove Alt. Email.

6. Click Save.

Emulate SQL DISTINCT Function in Saved Search

Using SQL DISTINCT function in Formula is currently an active enhancement request under Enhancement 166265 Saved Searches > Request the ability to use the SELECT DISTINCT statement in Formula's

This behavior can be achieved however using GROUP summary type in a saved search as shown below:

Sample Search Result setup

Image

Search translated to SQL Query

Image

Add Days to Date Type Field in Saved Searches using Formula

There is currently no built in function to add days for fields in saved searches (only ADD_MONTHS is available). To add days though, user can choose the Formula (date) type field in the Field dropdown and append "+" then the number of days to add.

Example:

To add 3 days to the transaction date use the formula:

{trandate}+2

Effect of dot (.) in Company Name for ODBC Schema in SQL Server. Error: The table either does not exist or the current user does not have permissions on that table

When using MS SQL Server to connect to NetSuite account via ODBC using a linked server, and the company name set in the account has a dot (.), this causes the schema to change the dot (.) to an underscore (_). To get around this, when creating ODBC queries, user can either enclose the company name in brackets (ex. [COMPANY NAME, INC_] ) or use OPENQUERY instead.

The (_) may also cause the error 'The table either does not exist or the current user does not have permissions on that table' when trying to query a table view with an underscore in it's name.

Tuesday, February 26, 2019

Item Search Displays Current Quantity is Higher than Quantity Sold

1. Navigate to Reports> New Search> Transaction> Click on Create Saved Search button.

 

2. Rename Search Title.

 

3. Under Criteria tab and Standard sub tab, set the following Filter and Description:

 

>> Type is any of Cash Sale, Invoice  

>> Main Line set to No

>> Tax Line set to No

>> Shipping Line set to No

>> Formula (Text) select from the Field =  'Item' then Formula (Text) select is not empty

>> Item Fields… then select 'On Hand' then select On Hand = not empty    

 

4. Under Criteria tab and Summary sub tab:    

 

>> Summary Type = Sum

>> Field = Formula (Numeric)

>> Formula = case when MAX({item.quantityonhand})> SUM({quantity}) then 1 else 0 end >> Formula (Numeric) = equal to

>> Value = 1.

  

 

5. Under Results tab and Columns sub tab> click Remove All button then Add the following Field and Summary Type:

 

>> Item | Group

>> Item fields… then select Description | Group

>> Item fields… then select On Hand | Maximum

>> Quantity | Sum

 

6. Click Save and Run.

Team Selling enabled: Customer Search showing Contribution % Total not equal to 100%

This article is applicable if Team Selling is enabled.

 

1. Lists > Search> Saved Searches > New > Click Customer

 

2. Rename Search Title

 

3. On the Criteria tab> Standard sub tab

 

>> Add on *Filters | * Description

 

>> Inactive | is set to No

 

4. On the Criteria tab> Summary sub tab

 

>> *Summary Type | * Field | * Description

 

>> Sum | Contribution % | not equal to 100

 

>> Sum | Contribution % | is not empty

 

5. On the Results tab> Columns sub tab

 

>> * Field | Summary Type

 

>> Name | Group

 

>> Contribution % | Sum

 

6. Save and Run

 

 

Ability to change the subject of Sales Order confirmation email

Users may customize their Sales Order form for them to be able to change the subject of the order confirmation email. Below are the detailed steps:

1. Navigate to CustomizationForms > Transaction Forms.

2. Edit the preferred Sales Order form.

3. Navigate to Printing Fields tab > Header sub tab.

4. In the Company Name field, mark the Print/Email checkbox.

5. In the Label field, enter the subject to use when emailing orders.

6. Hit Save.

For example, if User enters "Order Confirmation for" in the Label field, the email subject would be: "Order Confirmation for: Sales Order # XXXXX".


Note: Since the change is done on the preferred transaction form, this may impact other emails (aside from confirmation emails) sent from Sales Orders.  Please proceed according to business process.

There are also related Enhancements filed under:
    Enhancements #137955 and #164332 , which requests the ability to customize subject/title of the transaction

Also see the following articles:
Change Subject of Transaction Emails via Transaction > Communication/Messages tab > Email button

Change the "YYYY" part of "Company Name: YYYY SO#" Subject of Emails send via "To Be Emailed" checkbox from Transactions > Communication subtab

Create a Saved Search that will replicate the Standard Sales by Customer Report

1.    Go to Lists > Search > Saved Searches > New.

2.    Click on Transaction link.

3.    Under Criteria tab > Standard sub tab, set the below filters:

 

- Posting = True (Yes)

- Account Type = any of Income, Other Income

 

4.    Under Results tab, mark the Show Totals checkbox.

5.    Go to Results tab > Columns sub tab and add fields as deemed necessary.

6.    Enter a Search Title.

7.    Hit Save & Run and check the results.

 

Total amount should match the total sales on Sales by Customer Report.