Pages

2012/08/23

Welcome to the Wonderful World of NetSuite Custom Fields Where All Your Wildest Data Fantasies Come True

Basic Concepts
NetSuite is an ERP system and like other tools of the same breed, it runs on a database. One can think of a database as a series of tables where each column holds a field. Each line is a database entry (a record in database parlance) for which values are set in the columns. The operations you can do on a table is to add, edit or remove a record. Each of these tables correspond to a group of NetSuite "objects" (i.e. an item, a transaction, a customer...) which share the same set of columns.


Table
Example of a Database Table

If you add a new table, you get a new object. Although it's more complex than this in the background, it is essentially what is happening when you add a custom record. This brand new table initially has one column. In order to store additional information for entries added to the table, you need to insert columns. These are custom fields. You can add new columns to existing tables, these are custom fields used on standard records.


NewTable
Creating a New Table and Adding A Custom Field
NewField
Adding a Custom Field to an Existing Table

A final concept I'd like to explain that may appear off topic, but that is essential when dealing with custom fields, are forms. NetSuite provides screen forms to display the information from the database. These forms can be considered as windows into the database. You can create several different windows in order to view different perspectives of the data. These windows can hide or display specific columns.

View
A View Limiting How a Table is Viewed
Record
A Database Record
Form
A Form Limiting How a Record is Viewed


Creating Custom Fields
NetSuite breaks down its custom fields in eight different types that correspond more or less to record types:
  • Items: Inventory items, Non-inventory items, Assemblies, Service, Kits...
  • Entities: Customers, Vendors, Employees...
  • CRM Fields: Tasks, Phone Calls, Events, Case...
  • Transaction Body Fields: Sales Order, Purchase Order, Fulfillment, Receipt, Journal Entry...
  • Transaction Column Fields: The line items on any transaction
  • Item Number Fields: Serial and Lot numbers for Inventory Items
  • Other Fields: Several other records not falling in the above categories such as Locations, Classes, Departments, Accounts, Role...
  • Custom Record Fields: Fields used when creating a custom record
When you are granted the permission to work with them, you can create these types of fields in Setup > Customization > Lists, Records & Fields


Menu
Menu for Creating Custom Fields

Whichever field type you create, they all share a common definition. Without walking through all the details, let's take a few several lines to review the custom field creation form. 

The Header Area


Header
Header of a Custom Field Form

In the header area, the Label is used to identify the field when displayed in NetSuite. You can select the field's data type with the Type drop down. The most commonly used data type is the Free Form Text which displays a fixed width box where 300 characters can be captured. Other types of text fields allow entering from 4000 to 100 000 characters and some support rich text formatting. The following list gives a rapid breakdown of the most useful and powerful data types:
  • Document provides a link to a file stored in the NetSuite File Cabinet
  • Inline HTML allows injecting HTML code directly into a NetSuite web page. This is both a powerful and dangerous feature
  • List/Record and Multiple Select provide a mean of displaying a drop down list from which one or multiple entries can be selected. The drop down list can be populated from a predefined static list or from a list of existing records.
The other data types are self explanatory: Check Box, Currency, Date, Date/Time, Decimal Number...

The Applies To Tab
The Applies To tab provides a means of filtering the record sub-types in which your field will be visible. i.e. on a transaction body field you can say that it will only be visible on sales orders. You can also specify if the field should be displayed in special contexts like printed forms.

AppliesTo
Applies To Tab of a Custom Field Form

The Display tab
From the Display tab, you can control how the field will display on the NetSuite screen forms. I strongly recommend to get into the habit of describing the use of this custom field in the Help field as this is the text that will be displayed whenever a user clicks on your field's label. You need to be careful with what you configure here and on the forms as some settings can conflict. The Insert Before and Subtab determine where the field will be displayed on the forms. If you alter a form and move a field to a different tab, NetSuite will have a hard time resolving the conflicting information and I've seen fields disappear because of this. As for the Display Type, you need to understand that the setting on the form will be applied on top of this setting.


Display
Display Tab of a Custom Field Form

The Access tab
The Access tab offers a powerful feature for implementing approval process flows. The settings on this tab provides the means to define the custom field's access permissions. You can define which role, department or subsidiary can view and edit the content of the field. A typical scenario I implement is providing signature fields that can only be edited by manager roles whilst they are not visible to people not involved in the process.


Access
Access Tab of a Custom Field Form

Dynamic defaults and Dynamic fields
The Validation & Defaulting tab offers the possibility of configuring dynamic defaults based on other field's values, but they are only possible under very constrained conditions. First off, the Store Value in the header must be set to true. Then, a formula must be built in the Default Value of the Validation & Defaulting tab. This formula can use fields from the current record or joined fields (fields from records that are directly linked to the current record). But the catch here is that the dynamic default formula is evaluated once and only once before the creation form is loaded. Therefore, any fields used in your formula must refer to values that already exist at that specific moment. When creating a new sales order, no customer is set so you cannot define a dynamic default sourcing information from it. On the other hand, if you create a sales order from a client record or from an estimate, a dynamic default can indeed source information from the joined customer record because it's already defined when loading the creation form. If all the right conditions are brought together, you have a default value that is dynamically computed and that you can edit afterwards.


ValidationDefaulting
Validation & Defaulting  Tab of a Custom Field Form

The counterpart to dynamic defaults are dynamic fields which are always updated but cannot be edited. In order to configure these, you must make sure the that Store Value is unchecked. You can then use a formula in the Validation & Defaulting tab or source a value in the Sourcing & Filtering tab. NetSuite will update the value of this field dynamically once a record is saved.


Source
Sourcing & Filtering  Tab of a Custom Field Form

Parent/Child Relationships
I could keep on writing about custom fields until I run out of screen, but I'll finish my post with this topic. Custom fields provide the means to create parent child relationships between records but the process is no  intuitive. Let's study a scenario where we have Specification records which need to be linked to an item record. Each Specification can only be associated with one item but an item can have several Specifications. We therefore want to define the item as the parent and the Specification as its children.

To define this relationship in NetSuite, we need to define a custom field on the child record, in this case the Specification. The data type must be set as List/Record which opens up the List/Record drop down. In this field, the parent record must be selected. In our scenario, it would be the item. Finally, the Record is Parent checkbox needs to be ticked


ParentChild
Creating a Parent/Child Relationship

The custom field will now appear on the  Specification records and it will allow you to select an item from a drop down list. The selected item will become the Specification's parent. When you view the item record, a new list will display the children Specification records


ParentChildDiagram
Diagram of a Parent/Child Relationship

8 comments:

  1. Excellent piece - thank you for opening up my mind to the possibilities in the parent/child relationships. This will help solve several data handling issues we currently have.

    ReplyDelete
  2. Do you know if there is a way to only show certain drop down options to one role, then other drop down options for the same field to another role?

    ReplyDelete
    Replies
    1. I can't think of a way through configuration to filter the content of a drop down by role. If it was by employee, class or department it might work.

      Through customization (scripting) it can be done rather easily though.

      Delete
  3. Is there anyway to display a custom item field (for example a matrix item option) on a packing slip? I've been trying to create a custom transaction field but no luck so far.

    ReplyDelete
    Replies
    1. Yes, it's possible. You need to create a custom Transaction Column field. Make sure the the Store Value check box is NOT selected and the the field Type matches the item's sourced field type.

      The catch is that for column transaction fields, the formula doesn't work so you need to use the Sourcing & Filtering tab. Select Item from the Source List drop down and your custom field in the Source Filter By drop down.

      Make sure that the Print on Packing Slip check box is selected on the Applies To tab.

      This should make your custom field available on a Packing Slip PDF printed form.

      Hope this helps

      Delete
  4. I have Media Outlets that are entered as Partner relationships. I want to list zip codes that they cover as child fields. Do I create a custom field on the Entity record, or do I create a Custom Record Type and tie it to the Entity? Also, how do I display these child values as a list on a "Coverage Area" tab?

    ReplyDelete
    Replies
    1. There is a way to do what you're describing with a custom mutli-select tool but IMO, it's not the best option because it doesn't provide an intuitive display on a record.

      I would recommend to create a Zip Code Region custom Record Type. When you define its the fields create one called Partner of type List/Record using the Partner record. On the right hand side of the screen, check the "Record is Parent" box. This will automatically make the Zip Code Region custom Record Type available as a sublist on Partner records.

      You might have to edit the partner form(s) to show the sublist (List tab). To have the list appear on its own tab, you will have to create one in Setup > Customization > Froms > Subtabs and then edit yout Partner form(s) to move the Zip Code Region list to it.

      Once you have this data structure in place, you could eventually develop a Google Map integration visually showing the partner's coverage.

      Hope this helps

      Delete
  5. This comment has been removed by the author.

    ReplyDelete