How to Create a Custom Field to a NetSuite Customer or Transaction

NetSuite custom fields are incredibly powerful and flexible, but they are not easy to work with! Below is a quick guide that walks through how to create customer and transaction custom fields and some tips and tricks for working with custom fields.

How to create a custom customer (i.e. entity) field in NetSuite: Navigate to: Customization > Lists, Records & Fields > Entity Fields > New Label: use a name you’d like to see displayed on your customer. Type: “Free-form Text”. There are cases where you won’t want to use free-form text, but in most cases this is the best option. ID: “_a_internal_identifier”. Note that you should start your identifier with a _ since custentity will be prepended to whatever you provide here. You’ll want to change “_a_internal_identifier” to an ID that is descriptive of this field in your setup. Store value: checked Applies to: Customer Save How to create a transaction (invoice, cash sale, order, payment, etc) field in NetSuite: Navigate to: Customization > Transaction Body Fields > New Label: use a name you’d like to see displayed on your transaction. ID: _a_internal_identifier. Note that you should start your identifier with a _ since custbody will be prepended to whatever you provide here. Type: free form text Store Value: checked Applies to: Sale, Payment, etc. Where you want this field displayed is up to your use-case. Global Search: checked. This enables you to search for the value in the NetSuite global search Save Other Tips & Tricks for Working With NetSuite Custom Fields Customer (entity) custom fields will always start with custentity. Transaction (invoice, sales order, payment, etc) custom fields will always start with custbody. You can create custom fields on most, but not all records. For instance, you can’t create a custom field on the currency record in NetSuite but you can create one on a subsidiary. It’s possible to access “hidden” fields that are not available through SuiteTalk or SuiteScript using field defaults or formula field defaults on custom fields. You can source values from a related record. For instance, if you want to source a value from a CashSale connected to a CashRefund you could use a formula like {createdfrom.externalid} as the default value for a field that doesn’t store a value. A field which does not store value (i.e. a field that just contains a default value) is not available via a formula on another field. A field not found error will be reported. In other words, you can’t nest formulas. This is a huge bummer. If “formula” is not checked when creating the custom field you can still substitute field values using the {field} syntax You’ll get ERROR: Invalid Expression for lots of different types of errors. For instance, if your formula doesn’t output the correct field type you’ll get this error. This makes it very challenging to debug why a formula isn’t working. Join fields (i.e. {entity.datecreated}) strips the value of its type. Everything comes over as a string. You can use SQL functions in a formula field default

Continue Reading

Notes on Working with Jobs (Projects) using the SuiteTalk NetSuite API

Jobs are a confusing part of the NetSuite system. Here’s some notes and resources to help make handling jobs easier:

Jobs are named Projects in the GUI There are two versions of jobs: “standard” and “Advanced Jobs”. They both use the same core job record but change how the data is structured on the customer. Jobs have a kcustomer attribute that is not accessible via SuiteTalk which represents the customer that the job is associated with. This field is accessible via SuiteScript. You could most likely expose this field by creating a formula field on the job record with {kcustomer} as the formula. There is no customer field on the job schema However, there is a customer field on the job search columns. You can use an advanced search to return the customer associated with a job. If “Project Management” is enabled (aka ADVANCEDJOBS) the entity reference on the customer is a customer during the edit view, but will switch to be a job reference when the record is saved. When ADVANCEDJOBS is disabled the job reference could be either a customer or a job and the job field is not displayed. The parent field on a job can reference a customer. I believe you can have an heirarchy of jobs so it can be possible for the parent to be a job reference. Since the kcustomer field is not exposed via SuiteTalk the only way to determine the correct customer reference is to walk up the hierarchy.

References:

Determine the customer associated with a job via SuiteTalk SuiteScript to extract a valid customer reference from an entity ID Determining if an entity ID is a customer or a project

Continue Reading

Notes on Dates & TimeZones with NetSuite’s SuiteTalk API

Setting date and datetime fields in NetSuite using the SuiteTalk API is tricky business. Here’s some notes on how to ensure that the date you push over to NetSuite persists the way you’d expect regardless of the server, local server, user, and company timezone.

The API is sensitive to the timezone of the current user. If you push the same datetime value to the XML via SuiteTalk, and change the timezone settings on the user preferences page, the resulting date set on the record will most likely change. The timezone is not available on the employee record. Also, the timezone is not available on the company/subsidiary record. It is impossible to determine the timezone set on the user or the company from the SuiteTalk API. The get_server_time method does not return the time in the current user’s timezone preference. There is no SuiteScript-specific method to getting a timezone. Although it looks like you can infer it from the stdlib JS responses. It could be possible to create a RESTlet bundle that could retrieve the timezone of the current user as a JSON response. If the timezone of the user (employee) differs from the timezone of the request NetSuite will convert the datetime in the request to the timezone of the current user. If you push a date of 2017-04-17T00:00:00-07:00 and the user’s timezone is GMT -04:00 the resulting date field in NetSuite returns 2017-04-16T21:00:00-07:00. This appears properly as 04/17 in the GUI for the current user as well as for other users with different timezone configurations. If you push a date in the timezone of the NetSuite servers (PDT) NetSuite seems to ignore the timezone of the user used to make the request. Note that accounting for DST offset and zeroing out the hours, minutes, seconds is essential to making this work. Here’s an example of what your date should look like 2017-04-17T00:00:00-07:00. If you push a date one second after midnight NetSuite will take into account the user’s timezone. Best practice: convert your date to UTC0 and use NetSuite::Utilities.normalize_time_to_netsuite_date(time_stamp). This utility will handle DST, zeroing out hr/minute/seconds, and convert the datetime to the right format for SuiteTalk. The date returned by the API is adjusted based on the user’s timezone. For instance, if you push 2017-04-17T00:00:00-07:00 to NetSuite with a user whose timezone is set to EST the date returned by the API will be 2017-04-16T17:00:00-07:00 although it will properly display as 2017-04-17 in the GUI.

References:

NetSuite ruby client conversion utility https://netsuite.custhelp.com/app/answers/detail/a_id/34163/kw/TIMEZONE https://netsuite.custhelp.com/app/answers/detail/a_id/43268/kw/TIMEZONE https://netsuite.custhelp.com/app/answers/detail/a_id/44687/kw/TIMEZONE Converting between time and datetime in ruby Modifying time without railscn

Continue Reading

Notes on NetSuite’s Gift Certificate Record

The gift certificate functionality in NetSuite is much more limited than other functionality in NetSuite. It’s also the only option for implementing store credit, electronic, and physical gift cards in NetSuite. If you need to integrate with the gift certificate system in NetSuite, here are some notes to help you along the way:

Gift certificates are not created directly. They are are created by creating a “Gift Certificate Item” and using that item in a SalesOrder. Gift certificates can be created without an associated posting transaction (Sales Order) by importing a CSV. However, you cannot create a standalone gift certificate via SuiteTalk. You must create a posting transaction (Invoice/CashSale) with a gift certificate item in order to create a gift certificate via SuiteTalk. Technically, a non-posting transaction (Sales Order) will create a gift certificate record. However, the gift certificate cannot be used until it is associated with a posting transaction. The “Amount Remaining” field is nil if the gift card has been created by a non-posting transaction by has not been filled/purchased. In other words, if amount remaining is nil the user should not be able to use this gift card on an ecommerce site. Gift certificate codes can be a maximum of 9 characters. It’s important that external systems respect this limit if NetSuite is not the system-of-origin for gift card codes. The gift certificate code can be blank. It’s hard to tell exactly how this can occur because there is no system log, but I believe it occurs when gift certificates are imported. There is a custom field list advances search option for gift certificates, but strangely enough custom fields cannot be added to the gift certificate item. The system log is not available for a gift certificate item The last modified date is not shown in the UI You cannot search gift certificates by the last modified date, although it is available in the SuiteTalk response. The last modified date is not updated when a gift card is used. The best way to search for updated gift cards is using a transaction search. A gift certificate is not tied to a specific customer, although in some areas of the UI it does seem as though The gift certificate record does not contain a reference to the customer that purchased or used the gift card. You must search for transactions associated with the gift certificate, then pull a customer reference from the associated transaction. Use the transaction search’s giftCertificate field to retrieve all transactions that are connected with the gift certificate. Note that this includes the non-posting transaction that may have created the gift certificate record, along with any other transactions that have effected the gift card balance over time. You’ll need to filter out the exact transactions you are looking for.` If you need to automatically generate codes that play nicely with other systems (3PL, ecommerce, etc) the best approach is create a before save via SuiteScript to call out to an external service that coordinates gift card code generation. NetSuite does provide the option to generate gift certificate codes for you. However, if you have other systems (such as an eCommerce platform), you won’t be able to push gift certificates into NetSuite using the externally generated code if this option is enabled. If a transaction’s line item contains the giftCertCode field, then the line item is a GiftCertificateItem. If a transaction’s header-level giftCertApplied field is less than zero, then a gift certificate was used as part of the payment. If you search for transactions with giftCertificate not empty ItemFulfillments will be returned in the results, although there is no reference to a gift certificate record on that transaction. A gift card notification email will be sent to the email in the “To” field. There is no way to disable this feature. Example Gift Card Transaction Search

Here’s an example transaction search using the NetSuite ruby bindings for any transactions that use a gift card:

NetSuite::Records::SalesOrder.search( basic: [ { field: 'giftCertificate', operator: 'notEmpty' } ] )

Note that not just SalesOrders are returned. Any transaction associated with a gift certificate is returned in this search. To restrict to a specific transaction type, using a type = _salesOrder search criteria.

Enhancements Requested

I requested enhancements for the more critical gift card limitations I found through SuiteIdeas (vote on them if you can!):

116981 Ability to disable gift certificate emails 411408 Ability to search by gift certificate’s last modified date 176418 Gift certificate custom fields can’t be viewed in SuiteTalk response 159896 Cannot create custom fields on gift certificate

Continue Reading

Finding the NetSuite Deposit Associated with a Payment or Refund

Determining which NetSuite deposit record is associated with a customer payment, customer refund, cash payment, cash refund, etc using SuiteTalk is not straightforward. It’s also challenging to determine if a refund record is still available to be deposited. Payment transactions have a status field which indicates if a record has been deposited or not, but refund records do not have this field.

In order to determine if a refund is deposited, you need to search for an associated deposit. Here’s a sample implementation using the NetSuite ruby bindings:

ns_customer_refund_id = 2158604 search = NetSuite::Records::Deposit.search( criteria: { basic: [ { field: 'type', operator: 'anyOf', value: [ '_deposit' ] }, { field: 'appliedToTransaction', operator: 'anyOf', value: [ NetSuite::Records::RecordRef.new(internal_id: ns_customer_refund_id) ] }, ] }, preferences: { page_size: 10 } ) ns_deposit = search.results.first # deposit needs to be refreshed to include body fields ns_deposit.refresh Linking to the Associated Deposit in the NetSuite GUI

Another way to solve this problem is creating a custom field on the refund record and use a formula to pull the associated deposit from the refund. NetSuite stores a reference to the deposit in their refund DB table that is not exposed to SuiteTalk or SuiteScript. Here’s more details on this pulled from a conversation with NetSuite support:

I created a custom body field on Customer Refund page to display the associated Bank Deposit Number as steps listed below.

Navigate to Customization > Lists, Records, & Fields > Click on Transaction Body Fields > New.

Enter a Label for the custom field. For example, Bank Deposit Number (Test).

Type field: select ‘Free-Form Text’

Store Value checkbox > Unchecked.

Under Applies To tab > Check ‘Sales’

Under Display tab > Subtab > I selected ‘Main’

Under Validation&Defaulting tab > Default Value field > Enter ‘{deposittransaction.number} ‘

Save.

Navigate to Customization > Forms > Transaction Forms > Edit the Customer Refund transaction form in concern/in use > Under Screen Fields tab > Main Subtab > Make sure that the above custom field created is checked with ‘Show’ checkbox > Save.

Now, if you open/view an existing Customer Refund transaction that is associated with a Bank Deposit, the Bank Deposit number will show automatically.

Searching for the Associated Deposit Using a Transaction Search

Here’s an example of how to search for the associated deposit using the NetSuite GUI. Note that the "Applied To Transaction" field requires that you prefix the payment transaction ID with "Payment #" or you won’t be able to find a match in the search creation GUI.

Here’s a direct link to open up a deposit transaction search.

Continue Reading

Using NetSuite’s Token Based Authentication with SuiteTalk

NetSuite’s OAuth is very different from the standard oauth flow: setting up a user for token based auth is very cumbersome. It requires digging around in the NetSuite GUI, creating roles, and copy/pasting various keys.

Why use token based authentication? The alternative is email + password based authentication. This method works fine, but passwords expire every six months; resetting passwords every six months is a huge pain for a SAAS product that integrates with NetSuite. Plus, email + password auth is much less secure (an attacker can login to the GUI with a email and password).

Here’s a guide to getting setup with token based authentication. Note that you must be using a SuiteTalk API versions greater than 2015_2.

1. Create a Integration Record

The integration record identifies the application in NetSuite’s system.

Visit the integrations page or global search for page:integrations Create a integration record if none exists. After you create the record you will need to copy/paste the consumer key and consumer secret to your secrets file. Name: Your-Application-Name Authentication: Token-Based Authentication State: Enabled If the integration record already exists, but you don’t have the consumer key and consumer secret, edit the record, then press “Reset Credentials”. 2. Enable Token Based Authentication Setup > Company > Setup Tasks > Enable Features > SuiteCloud > Manage Authentication Make sure “Token Based Authentication” is enabled Save

If this feature is not enabled, you will not see the permissions required in the next step.

3. Create a Token Role

Strangely enough, the administrator does not have token permissions by default. If you do not create a token role and assign it to your administrator, you will get a "Login access has been disabled for this role." error when creating a token.

Global search for page:role, then choose “New Role” Navigate to “Permissions > Setup” and add the following permissions: User Access Token: Full Access Token Management: Full Web Services: Full 4. Add Token Management Permissions Global search for page:employees Edit your employee record Navigate to “Access > Roles” and add the token auth role you just created 5. Create Access Tokens Global search for page: tokens New Access Token Select the application and role we created earlier, then press save. Copy/past the token ID and token secret to your secrets file.s 6. Configure Your Client

Here’s how to setup the netsuite ruby client with token based authentication:

NetSuite.configure do reset! account ENV['NETSUITE_ACCOUNT'] consumer_key ENV['NETSUITE_CONSUMER_KEY'] consumer_secret ENV['NETSUITE_CONSUMER_SECRET'] token_id ENV['NETSUITE_TOKEN_ID'] token_secret ENV['NETSUITE_TOKEN_SECRET'] end

Continue Reading

Depositing Transaction Records in NetSuite

Moving transactions in NetSuite from "Not Deposited" to "Deposited" is not straightforward when using NetSuite SuiteTalk.

You need to ensure that undocumented requirements for each record type are met. After you’ve validated that your records are properly configured, you can include them in a new deposit using the following structure:

ns_deposit = NetSuite::Records::Deposit.new ns_deposit.payment_list << { deposit: true, # internalId of a CashSale, CustomerPayment, CustomerRefund, CustomerDeposit, etc id: 123, # the amount is not required if the currency of the payment is the same as the currency # of the bank account that the deposit is posting to payment_amount: 100.0 } ns_deposit.add

Note that you don’t need doc, type, or any of the other fields available on the a DepositPayment item. It’s also important to note that the other fields on the DepositPayment don’t actually effect how NetSuite handles the referenced transaction record. deposit, id, and payment_amount are the only fields that matter when referencing a NetSuite transaction on a deposit.

Continue Reading

How to Refund a Customer Deposit using NetSuite SuiteTalk

When refunding a NetSuite CustomerRefund or CashSale you’ll add it to apply_list:

refund.apply_list = { apply: [ { doc: 123, # internalId of the CustomerRefund apply: true } ] }

Refunding a CustomerDeposit works a bit differently. If you inspect the SuiteTalk XML response for a CustomerRefund created for a CustomerDeposit it will appear under the apply_list XML tag. If you use that XML tag when creating the CustomerRefund you’ll get the following error:

Unable to find a matching line for sublist apply with key

Instead, you need to use the deposit_list tag:

refund.deposit_list = { customer_refund_deposit: [ { doc: 123, # internalId of CustomerDeposit apply: true } ] }

Continue Reading

Determine Deposited Status of CustomerRefunds in NetSuite

Most payment and refund records in NetSuite have a straightforward way to determine if they’ve been deposited or not. The CustomerRefund is one exception: there is no way to determine from the GUI, or from the SuiteTalk API response for a CustomerRefund, if a given CustomRefund has been linked to a deposit record.

However, you can search for CustomerRefunds which are deposited or undeposited using a couple poorly documented search filters:

NetSuite::Records::CustomerRefund.search( criteria: { basic: [ { field: 'type', operator: 'anyOf', value: [ '_customerRefund' ] }, { field: 'applyingLinkType', operator: 'noneOf', value: [ '_depositRefundCheck' ] }, { field: 'mainLine', value: true }, { field: 'account', operator: 'anyOf', # undep funds account reference value: [ NetSuite::Records::RecordRef.new(internal_id: 6) ] } ] }, preferences: { page_size: 250 } )

Continue Reading