Retrieving all possible values for a field via a RESTlet
Categories:
Retrieving All Possible Values for a Field via a NetSuite RESTlet
Learn how to programmatically fetch all available values for a specific field in NetSuite using a custom RESTlet, covering both standard and custom fields.
NetSuite offers extensive customization capabilities, including custom fields and lists. Often, when integrating with external systems or building custom UIs, you need to retrieve all possible values for a given field, such as a dropdown list, multi-select, or custom record reference. While the NetSuite UI provides this information, programmatically accessing it requires a different approach. This article will guide you through creating a NetSuite RESTlet to efficiently fetch all possible values for both standard and custom fields.
Understanding Field Value Retrieval in NetSuite
NetSuite fields can store various types of data, and for fields that present a selection of options (like dropdowns, multi-selects, or references to custom lists/records), you often need to know the full set of available choices. This is crucial for validation, populating external forms, or ensuring data consistency. The method for retrieving these values depends on the field's nature:
- Standard List/Record Fields: These are fields like 'Department', 'Class', 'Location', or 'Currency'. Their values are typically stored in standard NetSuite records.
- Custom List Fields: These are custom fields whose values are defined in a custom list within NetSuite.
- Custom Record Fields: These are custom fields that reference a custom record type, where each record represents a possible value.
Our RESTlet will need to dynamically determine the field type and use the appropriate NetSuite API to fetch the values.
Workflow for retrieving field values via a RESTlet
Building the NetSuite RESTlet
The core of our solution is a NetSuite RESTlet that accepts parameters for the record type and field ID, then returns a JSON array of available values. We'll leverage NetSuite's N/query module for efficient data retrieval, especially for custom lists and records, and N/record for standard fields where applicable. The RESTlet will need to handle different field types and provide a consistent output format.
/**
* @NApiVersion 2.1
* @NScriptType Restlet
* @NModuleScope SameAccount
*/
define(['N/query', 'N/record', 'N/search', 'N/log'],
function(query, record, search, log) {
function getPossibleFieldValues(requestParams) {
var recordType = requestParams.recordType;
var fieldId = requestParams.fieldId;
var values = [];
if (!recordType || !fieldId) {
return { error: 'Missing recordType or fieldId parameter.' };
}
try {
// Try to load the record to inspect the field
// This is a common approach for standard fields or custom fields on standard records
var rec = record.create({ type: recordType, isDynamic: true });
var field = rec.getField({ fieldId: fieldId });
if (field) {
// Handle standard select/multiselect fields
if (field.type === 'select' || field.type === 'multiselect') {
var selectOptions = field.getSelectOptions();
selectOptions.forEach(function(option) {
if (option.value) { // Exclude empty option if present
values.push({ id: option.value, name: option.text });
}
});
} else if (field.type === 'list' || field.type === 'multiselectlist') {
// This might indicate a custom list or custom record reference
// We need to determine the source list/record type
// This is a more complex scenario and often requires knowing the custom list/record ID
// For simplicity, we'll try to infer from the field definition or rely on N/query later
log.debug('Field Type is List/MultiSelectList', 'Field: ' + fieldId + ', Type: ' + field.type);
// Attempt to find custom list/record via search if field.source is not directly available
var customListId = getCustomListIdFromField(fieldId, recordType);
if (customListId) {
values = getValuesFromCustomList(customListId);
} else {
// Fallback for custom record references or other complex types
// This part might need manual mapping or more advanced introspection
log.audit('Complex Field Type', 'Could not directly get values for field: ' + fieldId + ' on record: ' + recordType);
// Attempt to search for related records if it's a custom record reference
var customRecordTypeId = getCustomRecordTypeIdFromField(fieldId, recordType);
if (customRecordTypeId) {
values = getValuesFromCustomRecord(customRecordTypeId);
}
}
}
}
// If values are still empty, try a more generic search for custom lists/records
if (values.length === 0) {
var customListId = getCustomListIdFromField(fieldId, recordType);
if (customListId) {
values = getValuesFromCustomList(customListId);
} else {
var customRecordTypeId = getCustomRecordTypeIdFromField(fieldId, recordType);
if (customRecordTypeId) {
values = getValuesFromCustomRecord(customRecordTypeId);
}
}
}
} catch (e) {
log.error('Error getting field values for ' + fieldId + ' on ' + recordType, e.message);
// If record.create fails (e.g., invalid recordType), or field not found, try search-based approach
var customListId = getCustomListIdFromField(fieldId, recordType);
if (customListId) {
values = getValuesFromCustomList(customListId);
} else {
var customRecordTypeId = getCustomRecordTypeIdFromField(fieldId, recordType);
if (customRecordTypeId) {
values = getValuesFromCustomRecord(customRecordTypeId);
}
}
}
return values;
}
function getCustomListIdFromField(fieldId, recordType) {
try {
var customFieldSearch = search.create({
type: 'customrecordtype',
filters: [
['scriptid', search.Operator.IS, recordType],
'AND',
['customrecordcustomfield.scriptid', search.Operator.IS, fieldId]
],
columns: [
search.createColumn({ name: 'scriptid', join: 'customrecordcustomfield' }),
search.createColumn({ name: 'customlist', join: 'customrecordcustomfield' })
]
});
var result = customFieldSearch.run().getRange({ start: 0, end: 1 });
if (result && result.length > 0) {
var customListRef = result[0].getValue({ name: 'customlist', join: 'customrecordcustomfield' });
if (customListRef) {
// customListRef is the internal ID of the custom list
return customListRef;
}
}
} catch (e) {
log.error('Error getting custom list ID from field ' + fieldId, e.message);
}
return null;
}
function getCustomRecordTypeIdFromField(fieldId, recordType) {
try {
var customFieldSearch = search.create({
type: 'customrecordtype',
filters: [
['scriptid', search.Operator.IS, recordType],
'AND',
['customrecordcustomfield.scriptid', search.Operator.IS, fieldId]
],
columns: [
search.createColumn({ name: 'scriptid', join: 'customrecordcustomfield' }),
search.createColumn({ name: 'recordtype', join: 'customrecordcustomfield' })
]
});
var result = customFieldSearch.run().getRange({ start: 0, end: 1 });
if (result && result.length > 0) {
var customRecordTypeRef = result[0].getValue({ name: 'recordtype', join: 'customrecordcustomfield' });
if (customRecordTypeRef) {
// customRecordTypeRef is the internal ID of the custom record type
return customRecordTypeRef;
}
}
} catch (e) {
log.error('Error getting custom record type ID from field ' + fieldId, e.message);
}
return null;
}
function getValuesFromCustomList(customListId) {
var values = [];
try {
var listItems = query.runSuiteQL({
query: 'SELECT id, name FROM customlistitem WHERE customlist = ? ORDER BY name ASC',
params: [customListId]
}).asMappedResults();
listItems.forEach(function(item) {
values.push({ id: item.id, name: item.name });
});
} catch (e) {
log.error('Error getting values from custom list ' + customListId, e.message);
}
return values;
}
function getValuesFromCustomRecord(customRecordTypeId) {
var values = [];
try {
// Get the script ID of the custom record type from its internal ID
var customRecordType = record.load({ type: 'customrecordtype', id: customRecordTypeId });
var customRecordScriptId = customRecordType.getValue({ fieldId: 'scriptid' });
var recordItems = query.runSuiteQL({
query: 'SELECT id, name FROM ' + customRecordScriptId + ' ORDER BY name ASC'
}).asMappedResults();
recordItems.forEach(function(item) {
values.push({ id: item.id, name: item.name });
});
} catch (e) {
log.error('Error getting values from custom record ' + customRecordTypeId, e.message);
}
return values;
}
return {
get: getPossibleFieldValues
};
}
);
NetSuite RESTlet (get_field_values.js) to retrieve field options
N/query (SuiteQL) for custom list and custom record value retrieval, which is generally more performant than N/search for simple lookups. Ensure your NetSuite account has SuiteQL enabled.Deployment and Usage
Once the RESTlet script is created, you need to deploy it in NetSuite and then call it from your client application. The RESTlet expects two parameters: recordType (the script ID of the record, e.g., 'salesorder', 'customrecord_my_record') and fieldId (the script ID of the field, e.g., 'department', 'custbody_my_custom_field').
1. Upload the Script
Navigate to Customization > Scripting > Scripts > New. Upload the get_field_values.js file.
2. Create Script Record
Select 'RESTlet' as the script type and click 'Create Script Record'. Give it a meaningful name (e.g., 'Get Field Values RESTlet').
3. Deploy the Script
On the script record, go to the 'Deployments' tab and click 'New Deployment'. Set the 'Status' to 'Released' and choose an appropriate 'Audience' (e.g., 'All Employees' or specific roles). Note down the 'External URL' for the deployment.
4. Test with a Client
Use a tool like Postman or write a simple client-side script to make a GET request to the deployed RESTlet URL. Append the recordType and fieldId as query parameters.
// Example Client-side JavaScript (e.g., in a browser or Node.js)
const restletUrl = 'YOUR_RESTLET_EXTERNAL_URL'; // Replace with your actual URL
const recordType = 'salesorder'; // Example: 'salesorder', 'customer', 'customrecord_my_custom_record'
const fieldId = 'department'; // Example: 'department', 'custbody_my_custom_list_field'
fetch(`${restletUrl}&recordType=${recordType}&fieldId=${fieldId}`, {
method: 'GET',
headers: {
'Content-Type': 'application/json',
// Add NetSuite authentication headers if required (e.g., NLAuth)
// 'Authorization': 'NLAuth nlauth_account=YOUR_ACCOUNT_ID, nlauth_email=YOUR_EMAIL, nlauth_signature=YOUR_PASSWORD'
}
})
.then(response => {
if (!response.ok) {
throw new Error(`HTTP error! status: ${response.status}`);
}
return response.json();
})
.then(data => {
console.log('Possible values:', data);
})
.catch(error => {
console.error('Error fetching field values:', error);
});
Example client-side JavaScript to call the RESTlet