Record Relationships
Detailed patterns for linking custom records, transactions, and standard records.
Step-by-Step Tutorials
This section provides hands-on walkthroughs for creating each relationship type in the NetSuite UI.
Tutorial 1: Creating a One-to-Many (Parent-Child) Relationship
Scenario: Track maintenance logs for company vehicles. Each vehicle can have many maintenance logs.
Step 1: Create the Parent Record (Vehicle)
Navigation: Customization > Lists, Records, & Fields > Record Types > New
+-----------------------------------------------------------------------------+
| NEW CUSTOM RECORD TYPE |
+-----------------------------------------------------------------------------+
| |
| Name*: [Company Vehicle ] |
| ID*: [customrecord_vehicle ] |
| |
| [x] Include Name Field <- Checkbox checked |
| [x] Show ID |
| |
| ACCESS: |
| All Roles [x] Use Permission List |
| |
| SCRIPTING: |
| [x] Available for SuiteScript |
| [x] Available for SuiteQL |
| |
| [Save] [Cancel] |
+-----------------------------------------------------------------------------+
Step 2: Add Fields to Vehicle Record
After saving, go to: Fields subtab > New Field
+-----------------------------------------------------------------------------+
| CUSTOM RECORD FIELDS - Company Vehicle |
+-----------------------------------------------------------------------------+
| Label | Type | ID | Mandatory |
+--------------------+-------------+-----------------------+------------------+
| Vehicle Number | Text | custrecord_veh_number | Yes |
| Make | Free-Form | custrecord_veh_make | Yes |
| Model | Free-Form | custrecord_veh_model | Yes |
| Year | Integer | custrecord_veh_year | No |
| Assigned Employee | List/Record | custrecord_veh_emp | No |
+-----------------------------------------------------------------------------+
Step 3: Create the Child Record (Maintenance Log)
Navigation: Customization > Lists, Records, & Fields > Record Types > New
+-----------------------------------------------------------------------------+
| NEW CUSTOM RECORD TYPE |
+-----------------------------------------------------------------------------+
| |
| Name*: [Maintenance Log ] |
| ID*: [customrecord_maint_log ] |
| |
| [x] Include Name Field |
| |
+-----------------------------------------------------------------------------+
Step 4: Create the Parent Link Field (THE KEY STEP)
Add a field to Maintenance Log that links to Vehicle:
Navigation:
Customization > Lists, Records, & Fields > Record Types
> [Maintenance Log] > Fields > New
+-----------------------------------------------------------------------------+
| NEW FIELD - Maintenance Log |
+-----------------------------------------------------------------------------+
| |
| Label*: [Vehicle ] |
| ID*: [custrecord_maint_vehicle ] |
| Type*: [List/Record v] |
| |
| List/Record: [Company Vehicle (Custom Record) v] |
| Select the parent record type |
| |
+-----------------------------------------------------------------------------+
| DISPLAY |
+-----------------------------------------------------------------------------+
| |
| [x] Mandatory <- Require a vehicle for every log |
| [ ] Store Value |
| |
+-----------------------------------------------------------------------------+
+-------------------------------------------------------+
| |
| >>> CRITICAL: Check this box! <<< |
| |
+------------+-------------------------------------------------------+----------+
| SOURCING & FILTERING | |
+-----------------------------------------------------------------------------+
| |
| [X] RECORD IS PARENT <-- THIS CREATES THE SUBLIST! |
| |
| When checked: |
| - Maintenance Log appears as SUBLIST on Vehicle record |
| - Vehicle becomes the "parent" in the relationship |
| - Users can add/edit maintenance logs directly from Vehicle |
| |
+-----------------------------------------------------------------------------+
| [Save] [Cancel] |
+-----------------------------------------------------------------------------+
[!IMPORTANT] The "Record is Parent" checkbox is the key setting that creates the sublist relationship. Without it, you only get a lookup dropdown.
Step 5: View the Result
VEHICLE RECORD - After Configuration
-------------------------------------------------------------------------------
[Company Vehicle: VEH-00001 - Honda Accord] [Edit] [Back]
+-----------------------------------------------------------------------------+
| VEHICLE INFORMATION |
+-----------------------------------------------------------------------------+
| Vehicle Number: VEH-00001 |
| Make: Honda |
| Model: Accord |
| Year: 2022 |
| Assigned: John Smith |
+-----------------------------------------------------------------------------+
+-----------------------------------------------------------------------------+
| MAINTENANCE LOG (Sublist) [New Maintenance Log] |
+-----------------------------------------------------------------------------+
| Date | Type | Mileage | Cost | Notes |
+------------+---------------+----------+-----------+------------------------+
| 2024-01-15 | Oil Change | 45,000 | $89.99 | Synthetic oil |
| 2024-02-20 | Tire Rotation | 47,500 | $45.00 | Rotated + balanced |
| 2024-03-10 | Brake Service | 50,000 | $350.00 | Front pads replaced |
+-----------------------------------------------------------------------------+
The child records (Maintenance Logs) automatically appear as a sublist
because "Record is Parent" was checked on the Vehicle field.
Tutorial 2: Creating a Many-to-Many Relationship
Scenario: Employees can work on multiple Projects, and Projects can have multiple Employees.
MANY-TO-MANY ARCHITECTURE
-------------------------------------------------------------------------------
EMPLOYEE PROJECT ASSIGNMENT PROJECT
(Standard) (Junction Record) (Custom)
+-----------+ +-----------------+ +-----------+
| | N | | N | |
| Employee |<--------->| Assignment |<--------->| Project |
| | | | | |
+-----------+ | - Employee (P) | +-----------+
| - Project (P) |
| - Role |
| - % Allocation |
+-----------------+
(P) = Record is Parent checked
Result: Sublists appear on BOTH Employee and Project records
Step 1: Create the Project Record
Navigation: Customization > Lists, Records, & Fields > Record Types > New
+-----------------------------------------------------------------------------+
| NEW CUSTOM RECORD TYPE |
+-----------------------------------------------------------------------------+
| |
| Name*: [Project ] |
| ID*: [customrecord_project ] |
| |
| [x] Include Name Field |
| [x] Available for SuiteScript |
| [x] Available for SuiteQL |
+-----------------------------------------------------------------------------+
Add Fields:
+-----------------------------------------------------------------------------+
| Label | Type | ID | Mandatory |
+--------------------+-------------+------------------------+-----------------+
| Project Code | Text | custrecord_proj_code | Yes |
| Start Date | Date | custrecord_proj_start | Yes |
| End Date | Date | custrecord_proj_end | No |
| Budget | Currency | custrecord_proj_budget | No |
| Status | List | custrecord_proj_status | Yes |
+-----------------------------------------------------------------------------+
Step 2: Create the Junction Record (Project Assignment)
Navigation: Customization > Lists, Records, & Fields > Record Types > New
+-----------------------------------------------------------------------------+
| NEW CUSTOM RECORD TYPE |
+-----------------------------------------------------------------------------+
| |
| Name*: [Project Assignment ] |
| ID*: [customrecord_proj_assign ] |
| |
| [ ] Include Name Field <- Optional for junction records |
+-----------------------------------------------------------------------------+
Add Fields - THIS IS WHERE THE MAGIC HAPPENS:
+-----------------------------------------------------------------------------+
| Label | Type | ID | Record is Parent |
+------------+-------------+-------------------------+------------------------+
| Employee | List/Record | custrecord_pa_employee | [X] YES - checked! |
| | (Employee) | | Creates sublist on Emp |
+------------+-------------+-------------------------+------------------------+
| Project | List/Record | custrecord_pa_project | [X] YES - checked! |
| | (Custom) | | Creates sublist on Proj|
+------------+-------------+-------------------------+------------------------+
| Role | List | custrecord_pa_role | [ ] No |
+------------+-------------+-------------------------+------------------------+
| Allocation | Percent | custrecord_pa_alloc | [ ] No |
+------------+-------------+-------------------------+------------------------+
[!TIP] Check "Record is Parent" on BOTH the Employee and Project fields. This creates sublists on both parent records.
Step 3: Configure Each Parent Link Field
Employee Field Configuration:
+-----------------------------------------------------------------------------+
| FIELD: Employee (custrecord_pa_employee) |
+-----------------------------------------------------------------------------+
| |
| Label*: [Employee ] |
| Type*: [List/Record v] |
| List/Record: [Employee v] <- Standard record |
| |
| [x] Mandatory |
| |
| SOURCING & FILTERING: |
| [X] RECORD IS PARENT <- Creates sublist on Employee record |
| |
+-----------------------------------------------------------------------------+
Project Field Configuration:
+-----------------------------------------------------------------------------+
| FIELD: Project (custrecord_pa_project) |
+-----------------------------------------------------------------------------+
| |
| Label*: [Project ] |
| Type*: [List/Record v] |
| List/Record: [Project (Custom Record) v] <- Custom record |
| |
| [x] Mandatory |
| |
| SOURCING & FILTERING: |
| [X] RECORD IS PARENT <- Creates sublist on Project record |
| |
+-----------------------------------------------------------------------------+
Step 4: View the Results
On Employee Record:
EMPLOYEE RECORD - John Smith [Edit] [Back]
+-----------------------------------------------------------------------------+
| EMPLOYEE INFORMATION |
+-----------------------------------------------------------------------------+
| Name: John Smith |
| Email: john.smith@company.com |
| Department: Engineering |
+-----------------------------------------------------------------------------+
+-----------------------------------------------------------------------------+
| PROJECT ASSIGNMENTS (Sublist) [New Project Assignment] |
+-----------------------------------------------------------------------------+
| Project | Role | Allocation | Start Date |
+----------------------+----------------+------------+-----------------------+
| Website Redesign | Lead Developer | 50% | 2024-01-01 |
| Mobile App | Developer | 30% | 2024-02-01 |
| API Integration | Consultant | 20% | 2024-03-01 |
+-----------------------------------------------------------------------------+
On Project Record:
PROJECT RECORD - Website Redesign [Edit] [Back]
+-----------------------------------------------------------------------------+
| PROJECT INFORMATION |
+-----------------------------------------------------------------------------+
| Project Code: PROJ-001 |
| Start Date: 2024-01-01 |
| End Date: 2024-06-30 |
| Budget: $150,000 |
| Status: In Progress |
+-----------------------------------------------------------------------------+
+-----------------------------------------------------------------------------+
| TEAM MEMBERS (Sublist) [New Project Assignment] |
+-----------------------------------------------------------------------------+
| Employee | Role | Allocation | Notes |
+------------------+----------------+------------+---------------------------+
| John Smith | Lead Developer | 50% | Technical lead |
| Jane Doe | Designer | 40% | UI/UX |
| Bob Johnson | Developer | 60% | Backend |
+-----------------------------------------------------------------------------+
Tutorial 3: Custom Record Sublist on Transaction
Scenario: Add shipping instructions as a sublist on Sales Orders.
Step 1: Create the Custom Record
Navigation: Customization > Lists, Records, & Fields > Record Types > New
+-----------------------------------------------------------------------------+
| NEW CUSTOM RECORD TYPE |
+-----------------------------------------------------------------------------+
| |
| Name*: [Shipping Instruction ] |
| ID*: [customrecord_ship_instr ] |
| |
| [x] Include Name Field |
+-----------------------------------------------------------------------------+
Step 2: Add Fields Including Transaction Link
SHIPPING INSTRUCTION - FIELDS
-------------------------------------------------------------------------------
+-----------------------------------------------------------------------------+
| Label | Type | ID | Record is Parent|
+--------------------+-------------+------------------------+-----------------+
| Sales Order | List/Record | custrecord_si_salesord | [X] YES |
| | (Trans.) | Type: Sales Order | <- KEY SETTING! |
+--------------------+-------------+------------------------+-----------------+
| Instruction Type | List | custrecord_si_type | [ ] No |
+--------------------+-------------+------------------------+-----------------+
| Description | Text Area | custrecord_si_desc | [ ] No |
+--------------------+-------------+------------------------+-----------------+
| Priority | List | custrecord_si_priority | [ ] No |
+--------------------+-------------+------------------------+-----------------+
Sales Order Field Configuration:
+-----------------------------------------------------------------------------+
| FIELD: Sales Order (custrecord_si_salesord) |
+-----------------------------------------------------------------------------+
| |
| Label*: [Sales Order ] |
| Type*: [List/Record v] |
| List/Record: [Transaction v] |
| Transaction Type: [Sales Order v] <- Filter to SO |
| |
| [x] Mandatory |
| |
| SOURCING & FILTERING: |
| [X] RECORD IS PARENT <- Creates sublist on Sales Order |
| |
+-----------------------------------------------------------------------------+
Step 3: View the Result on Sales Order
SALES ORDER #SO-00123 [Edit] [Back]
+-----------------------------------------------------------------------------+
| SALES ORDER INFORMATION |
+-----------------------------------------------------------------------------+
| Customer: ABC Corporation |
| Date: 2024-03-15 |
| Ship Date: 2024-03-20 |
| Amount: $5,250.00 |
+-----------------------------------------------------------------------------+
+-----------------------------------------------------------------------------+
| ITEMS |
+-----------------------------------------------------------------------------+
| Item | Qty | Rate | Amount |
+----------------+-----+-----------+------------------------------------------+
| Widget A | 10 | $250.00 | $2,500.00 |
| Widget B | 5 | $550.00 | $2,750.00 |
+-----------------------------------------------------------------------------+
+-----------------------------------------------------------------------------+
| SHIPPING INSTRUCTIONS (Sublist) [New Shipping Instruction] |
+-----------------------------------------------------------------------------+
| Type | Description | Priority |
+---------------------+--------------------------------+---------------------+
| Handling | Fragile - Handle with care | High |
| Delivery | Leave at loading dock | Normal |
| Documentation | Include COA with shipment | Normal |
+-----------------------------------------------------------------------------+
Custom record sublist appears automatically because
"Record is Parent" was checked on the Sales Order field.
Tutorial 4: Simple Lookup Field (No Sublist)
Scenario: Link Purchase Orders to an Approval Matrix record for reference only.
LOOKUP VS SUBLIST COMPARISON
-------------------------------------------------------------------------------
SIMPLE LOOKUP (Record is Parent = OFF):
Purchase Order has a dropdown to SELECT one Approval Matrix
No sublist appears anywhere
SUBLIST (Record is Parent = ON):
Multiple records can be created as children of parent
Sublist appears on parent record
Step 1: Create Custom Record (Approval Matrix)
Navigation: Customization > Lists, Records, & Fields > Record Types > New
+-----------------------------------------------------------------------------+
| NEW CUSTOM RECORD TYPE |
+-----------------------------------------------------------------------------+
| |
| Name*: [Approval Matrix ] |
| ID*: [customrecord_approval_matrix ] |
| |
| [x] Include Name Field |
+-----------------------------------------------------------------------------+
Fields:
+-----------------------------------------------------------------------------+
| Label | Type | ID | Mandatory |
+--------------------+-----------+--------------------------+-----------------+
| Threshold Amount | Currency | custrecord_am_threshold | Yes |
| Approver | Employee | custrecord_am_approver | Yes |
| Approval Level | Integer | custrecord_am_level | Yes |
+-----------------------------------------------------------------------------+
Step 2: Create Body Field on Purchase Order
Navigation: Customization > Lists, Records, & Fields > Transaction Body Fields > New
+-----------------------------------------------------------------------------+
| NEW TRANSACTION BODY FIELD |
+-----------------------------------------------------------------------------+
| |
| Label*: [Approval Matrix ] |
| ID*: [custbody_po_approval_matrix ] |
| Type*: [List/Record v] |
| List/Record: [Approval Matrix (Custom Record) v] |
| |
| APPLIES TO: |
| [x] Purchase Order |
| [ ] Sales Order |
| |
| DISPLAY: |
| [ ] Mandatory <- Not required |
| [x] Store Value |
| |
| RECORD IS PARENT: |
| [ ] NOT CHECKED <- This is just a lookup, not a sublist |
| |
+-----------------------------------------------------------------------------+
Step 3: View the Result on Purchase Order
PURCHASE ORDER #PO-00456 [Edit] [Back]
+-----------------------------------------------------------------------------+
| PURCHASE ORDER INFORMATION |
+-----------------------------------------------------------------------------+
| Vendor: XYZ Supplies |
| Date: 2024-03-15 |
| Amount: $12,500.00 |
| |
| Approval Matrix: [Level 2 - Manager Approval v] <- Dropdown selector |
| (No sublist, just a reference field) |
| |
+-----------------------------------------------------------------------------+
Quick Reference: Record is Parent vs Lookup
| Feature | Record is Parent (Checked) | Simple Lookup (Unchecked) |
|---|---|---|
| Creates sublist? | Yes - on parent record | No |
| Multiple children? | Yes - unlimited | N/A (just a reference) |
| UI display | Sublist with Add/Edit/Remove | Dropdown selector |
| Relationship type | One-to-Many (1:N) | Many-to-One (N:1) |
| Data entry | Create children from parent | Select existing record |
| Use case | Line items, logs, history | Categories, status, lookup |
Relationship Patterns Overview
One-to-Many (1:N) - Parent-Child
PATTERN: One-to-Many
-------------------------------------------------------------------------------
Parent Record Child Records
+----------------+ +----------------+
| | 1 N | |
| Vehicle |<------------->| Maintenance |
| | | Log |
+----------------+ +----------------+
(Record is Parent
on Vehicle field)
Many-to-One (N:1) - Lookup
PATTERN: Many-to-One (Lookup)
-------------------------------------------------------------------------------
Many Records One Record
+----------------+ +----------------+
| | N 1 | |
| Purchase Order |-------------->| Approval |
| | | Matrix |
+----------------+ +----------------+
(Just a lookup field,
Record is Parent OFF)
Many-to-Many (N:M) - Junction
PATTERN: Many-to-Many
-------------------------------------------------------------------------------
Record A Junction Record Record B
+-----------+ +-----------------+ +-----------+
| | N | | N | |
| Employee |<->| Assignment |<->| Project |
| | | (Both fields | | |
+-----------+ | have Record | +-----------+
| is Parent ON) |
+-----------------+
One-to-One (1:1) - Exclusive
PATTERN: One-to-One
-------------------------------------------------------------------------------
Record A Record B
+----------------+ +----------------+
| | 1 1 | |
| Employee |<------------->| Employee |
| | | Profile |
+----------------+ +----------------+
(Add unique constraint
via validation)
Working with Relationships in Code
SuiteScript: Create Child Record
/**
* Create a child record linked to parent
* @NApiVersion 2.1
*/
define(['N/record'], function(record) {
function createMaintenanceLog(vehicleId, logData) {
var logRecord = record.create({
type: 'customrecord_maint_log'
});
// Set the parent link (Record is Parent field)
logRecord.setValue({
fieldId: 'custrecord_maint_vehicle',
value: vehicleId
});
// Set other fields
logRecord.setValue({ fieldId: 'custrecord_log_date', value: logData.date });
logRecord.setValue({ fieldId: 'custrecord_log_type', value: logData.type });
logRecord.setValue({ fieldId: 'custrecord_log_cost', value: logData.cost });
return logRecord.save();
}
return { createMaintenanceLog: createMaintenanceLog };
});
SuiteScript: Query Child Records
/**
* Get all children for a parent record
* @NApiVersion 2.1
*/
define(['N/search'], function(search) {
function getMaintenanceLogs(vehicleId) {
var logs = [];
search.create({
type: 'customrecord_maint_log',
filters: [
['custrecord_maint_vehicle', 'is', vehicleId]
],
columns: [
'custrecord_log_date',
'custrecord_log_type',
'custrecord_log_cost',
'custrecord_log_notes'
]
}).run().each(function(result) {
logs.push({
id: result.id,
date: result.getValue('custrecord_log_date'),
type: result.getText('custrecord_log_type'),
cost: result.getValue('custrecord_log_cost'),
notes: result.getValue('custrecord_log_notes')
});
return true;
});
return logs;
}
return { getMaintenanceLogs: getMaintenanceLogs };
});
SuiteQL: Join Across Relationships
-- Get all maintenance logs with vehicle info
SELECT
v.name as vehicle_name,
v.custrecord_veh_number as vehicle_number,
m.custrecord_log_date as log_date,
m.custrecord_log_type as log_type,
m.custrecord_log_cost as cost
FROM
customrecord_maint_log m
INNER JOIN customrecord_vehicle v
ON m.custrecord_maint_vehicle = v.id
WHERE
v.id = 123
ORDER BY
m.custrecord_log_date DESC
-- Many-to-Many: Get all employees on a project
SELECT
e.entityid as employee_name,
e.email,
pa.custrecord_pa_role as role,
pa.custrecord_pa_alloc as allocation
FROM
customrecord_proj_assign pa
INNER JOIN employee e
ON pa.custrecord_pa_employee = e.id
WHERE
pa.custrecord_pa_project = 456
Accessing Related Records in Saved Searches
Join Syntax
SAVED SEARCH JOIN PATTERNS
-------------------------------------------------------------------------------
Parent to Child (1:N):
On Vehicle search, add column:
Join: Maintenance Log (via custrecord_maint_vehicle)
Child to Parent (N:1):
On Maintenance Log search, add column:
Join: Vehicle (custrecord_maint_vehicle)
Junction Record:
On Project Assignment search:
Join: Employee (custrecord_pa_employee)
Join: Project (custrecord_pa_project)
Common Join Aliases
| Relationship | Search Type | Join Alias |
|---|---|---|
| Child to Parent | customrecord_maint_log | custrecord_maint_vehicle |
| Employee Assignment | customrecord_proj_assign | custrecord_pa_employee |
| Transaction Link | customrecord_ship_instr | custrecord_si_salesord |
Best Practices
Naming Conventions
| Element | Convention | Example |
|---|---|---|
| Parent link field | custrecord_[child]_[parent] | custrecord_maint_vehicle |
| Junction record | customrecord_[a]_[b]_link | customrecord_emp_proj_link |
| Junction fields | custrecord_[abbr]_[parent] | custrecord_epl_employee |
Design Checklist
- Define relationship type (1:N, N:M, 1:1, lookup)
- Create parent record(s) first
- Create child/junction record with link fields
- Check "Record is Parent" for sublist relationships
- Test by creating sample records
- Verify sublists appear correctly
- Create saved searches for reporting
- Document field purposes
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Sublist not appearing | "Record is Parent" not checked | Edit field, check the box |
| Wrong record in dropdown | List/Record set incorrectly | Verify List/Record selection |
| Can't select transaction | Transaction Type not filtered | Add Transaction Type filter |
| Sublist on wrong record | Field on wrong custom record | Move field to child record |
| Performance slow | Too many child records | Add pagination or filters |
References
- Record Types Guide - Creating custom records
- Custom Fields - Field configuration
- Saved Searches - Joined searches