This article is relevant if you are seeking to enhance the productivity of your NetSuite administrative team by programmatically accessing all of NetSuite’s tables and fields.
Background
For teams leveraging tools like SuiteQL and modern web frameworks, understanding how to effectively query NetSuite’s schema is a critical skill. Not only have we been focusing on all of our team members strengthening their SQL (SuiteQL) NetSuite skills, but a subset of us have been deepening our technical skills in modern development frameworks, such as React. Recently, one of our technical analysts, Borko M., built a powerful new version of our SuiteQL browser using React, replacing our previous Suitelet-based solution. This tool is an excellent demonstration of how modern web technologies can enhance both usability and maintainability.
In parallel, the NetSuite community has shown significant interest in tools for schema exploration, such as the SuiteQL Query Tool by Tim Dietrich. While Tim’s work is widely appreciated for its usefulness, our approach differs slightly: our tools are license-free (as is his) and designed not only for our team’s productivity when encountering the same types of questions across our clients but also to empower our clients’ administrators. We aim to teach clients how to help themselves, ensuring they can maximize their NetSuite investment while gaining confidence in their data capabilities.
Exploring NetSuite’s Tables and Fields
NetSuite’s relational database architecture provides access to a wide array of tables and fields. These are broadly divided into two types: native tables, which include standard NetSuite record types, and custom tables, which capture user-defined configurations and custom records. Having dynamic access to these tables and fields is crucial for developers and administrators aiming to query data or understand relationships between records effectively.
Certainly, NetSuite’s Records Catalog is the go-to tool. But we find it slow for our daily technical work.
Why NetSuite Recordtype Table Access Matters
We often seek to inspect NetSuite’s record tables and their fields dynamically to address scenarios such as:
- Identifying specific tables and fields for SuiteQL queries or integrations.
- Differentiating between native and custom tables for reporting purposes. Given their years of hands-on experience, our analysts know NetSuite’s tables, but we often look to quickly become more oriented about our clients’ custom tables.
- Understanding how fields from related tables interconnect for accurate data modeling.
Looking up these values quickly via a browser-based tool provides flexibility and efficiency, particularly when working within the NetSuite environment.
Inspiration for an Approach
I was inspired by foundational technical inspection work from our Technical Analyst, Marko O., who developed early browser-based programs for exploring NetSuite’s schema. These programs set the stage for more advanced tools we’ve developed to dynamically query, visualize, and differentiate between native and custom tables. In my mind, by building on these efforts and leveraging modern frameworks like React, we have the opportunity to produce enhanced usability which will make it faster and more intuitive to explore record metadata and field details.
Proposed Solution: Dynamically Accessing NetSuite’s Tables and Fields
To address the challenges of dynamically inspecting NetSuite’s record tables and fields, I propose combining SuiteQL, SuiteScript, and browser-based tools to provide real-time access to native and custom tables. This approach ensures usability, flexibility, and productivity for technical teams and administrators alike by leveraging modern frameworks like React and drawing from foundational efforts.
The key components of the approach are:
- Developing a Dynamic Schema Browser
- Create a browser-based tool that allows users to quickly inspect available record types, fields, and relationships.
- Include options to toggle between native and custom tables or display both simultaneously.
- Demonstrate our ability to create rich NetSuite applications for our current and prospective clients.
- Utilizing SuiteQL for Real-Time Data Exploration
- Harness the power of SuiteQL to query NetSuite’s underlying database schema.
- Use queries to fetch metadata, such as table names, field names, and their data types.
- Building with React for Modern Usability
- Implement React to enhance the front-end experience, enabling dynamic filtering, sorting, and searching of record metadata.
- Provide responsive, real-time feedback to users, improving productivity compared to conventional Suitelets.
- Inspire our clients who want more from the NetSuite UI to craft applications that run fast and are enjoyable to use.
- Extending Marko O.’s Initial Work
- Refactor and expand on existing browser programs inspired by earlier work to include advanced features like relational mapping and field-level insights.
- Integrate lessons learned from prior tools to ensure reliability and scalability.
- Deepen our team’s expertise and inspire others to grow their skills.
- Teaching Client Administrators
- Ensure the tool is accessible and understandable for clients’ administrators.
- Offer code snippets and documentation to empower administrators to tailor the tool for their unique needs.
Explore NetSuite’s Record Catalog Endpoint for Inspiration
NetSuite’s Records Catalog is a most useful feature for understanding record types and fields within an account. This feature is accessible via the /app/recordscatalog/rcbrowser.nl URL, which is the endpoint that supplies NetSuite’s provided Records Catalog. Using browser developer tools, such as the Console in your browser’s Inspect function, you can explore the underlying data NetSuite provides through its /app/recordscatalog/rcendpoint.nl API.
To get started. Navigate to the Records Catalog at Setup > Records Catalog in your NetSuite environment. Open your browser’s developer tools, and navigate to the Console tab.
Consider the following programs by cutting and pasting them into your browser console. The first code snippet provides access to all NetSuite tables in the account, both native and custom. Click the images to see what the results should look like in full screen.
// Fetch all NetSuite tables and display their names in a table in a new browser window
(function () {
// Fetch data from the API assuming you are in the browser console on the Records Catalog Page
fetch(
'/app/recordscatalog/rcendpoint.nl?action=getRecordTypes&data={"structureType":"FLAT"}'
)
.then((response) => {
if (!response.ok) {
throw new Error(`HTTP error! Status: ${response.status}`);
}
return response.json(); // Parse JSON data
})
.then((data) => {
if (!data || !data.data) {
throw new Error("Unexpected response structure. Missing `data` field.");
}
// Sort the tables by `id`
const sortedTables = data.data.sort((a, b) => a.id.localeCompare(b.id));
// Generate HTML content for the new window
const tableRowsHTML = sortedTables
.map(
(table, index) =>
`<tr><td>${index}.</td><td>${table.id}</td><td>${table.label}</td></tr>`
)
.join("");
const htmlContent = `
<html>
<head>
<title>NetSuite Tables</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
table { width: 100%; border-collapse: collapse; margin-top: 20px; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f4f4f4; }
tr:nth-child(even) { background-color: #f9f9f9; }
</style>
</head>
<body>
<h1>NetSuite Account Table Catalog</h1>
<table>
<thead>
<tr>
<th>Index</th>
<th>Record ID</th>
<th>Label</th>
</tr>
</thead>
<tbody>
${tableRowsHTML}
</tbody>
</table>
</body>
</html>
`;
// Open a new browser window and inject the HTML
const newWindow = window.open("", "_blank", "width=800,height=600");
if (newWindow) {
newWindow.document.write(htmlContent);
newWindow.document.close();
} else {
console.error("Unable to open a new browser window.");
}
})
.catch((error) => {
console.error("Error fetching or processing data:", error);
});
})();
Now fetch all of NetSuite’s native tables without regard for the custom tables and present them in HTML.
//Fetch NetSuite's native record types in this account in a pop up window for inspection
(function () {
// Ensure the NetSuite `require` function is available
if (typeof require !== 'function') {
console.error("NetSuite's require function is not available on this page.");
return;
}
// Use the require function to load the N/record module
require(['N/record'], function (record) {
try {
// Retrieve the keys and values of the record.Type enumeration
var recordTypes = Object.keys(record.Type).map(function (type) {
return { key: type, value: record.Type[type] };
});
// Generate HTML content to display in the new browser window
var htmlContent = `
<html>
<head>
<title>NetSuite Native Record Types</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
table { width: 100%; border-collapse: collapse; margin-top: 20px; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f4f4f4; }
tr:nth-child(even) { background-color: #f9f9f9; }
</style>
</head>
<body>
<h1>NetSuite Record Types</h1>
<table>
<thead>
<tr>
<th>Key</th>
<th>Value</th>
</tr>
</thead>
<tbody>
${recordTypes
.map(
(record) =>
`<tr><td>${record.key}</td><td>${record.value}</td></tr>`
)
.join('')}
</tbody>
</table>
</body>
</html>
`;
// Open a new browser window
var newWindow = window.open('', '_blank', 'width=800,height=600');
if (newWindow) {
newWindow.document.write(htmlContent);
newWindow.document.close();
} else {
console.error("Unable to open a new browser window.");
}
} catch (error) {
console.error("An error occurred while inspecting the record.Type enumeration:", error);
}
});
})();
Finally, fetch NetSuite’s fields using the “Transaction” record type and display them in the console.
// Fetch a specific record type's details (sorted) from NetSuite's endpoint; here review the 'transaction' table
fetch('/app/recordscatalog/rcendpoint.nl?action=getRecordTypeDetail&data={"scriptId":"transaction","path":""}')
.then((response) => {
if (!response.ok) {
throw new Error(`HTTP Error: ${response.status} - ${response.statusText}`);
}
return response.json();
})
.then((data) => {
// Ensure the response contains the expected structure
if (!data || !data.data || !data.data.fields) {
throw new Error("Unexpected response structure. Missing `data` or `data.fields` field.");
}
// Extract fields and sort them by `id`
const sortedFields = data.data.fields.sort((a, b) => {
if (a.id < b.id) return -1;
if (a.id > b.id) return 1;
return 0;
});
// Log the sorted fields
console.log("Sorted Fields by ID:", sortedFields);
})
.catch((error) => {
// Log any errors encountered during the fetch or processing
console.error("Error fetching or processing data:", error);
});
Analyze the Browser Inquiry Results:
These scripts provide raw JSON data containing record types, fields, and metadata. This information serves as a foundation for creating more sophisticated tools or views of NetSuite’s schema. The power of JavaScript, JSON and React will allow for rich and usable tools and applications.
Inspire the Team with a Vision for Enhanced Productivity
My desire is for this initiative to go beyond technical exploration; the goal is to inspire my team and others in the community to think creatively and contribute to tools that empower developers and administrators. Consider the following desired capacities:
- Get All Record Types Searchable in the Catalog: Encourage the team to design solutions that dynamically retrieve and organize all record types from the catalog. These solutions could include:
- Search tools to quickly find related record data.
- Filters to separate native and custom records.
- Highlights on important record structure elements reflected in the NetSuite account.
- Explore Undocumented Record Types: Some record types are not fully documented or accessible through conventional NetSuite interfaces. I encourage our team to:
- Experiment with variations of API calls to discover hidden or undocumented record types.
- Build out tools that identify and flag these records for potential use in queries or integrations.
- Expose these undocumented values within our standard tools to help us and our clients be more competitive.
- Make a more User-Friendly SQL Crafting User Interface: The ultimate goal is to enhance our new tool to continue to help bridge the gap between technical and non-technical users, offering a way to craft SQL queries that leverage both visual tools and metadata.
- Inspire Collaboration and Innovation: We want to show the community our skills in commanding the NetSuite platform. By sharing these tools and ideas with other developers, we can deepen our culture of collaboration and productivity.
Join a NetSuite Team Committed to Excellence
This exploration of NetSuite’s record catalog and schema underscores the importance of equipping our teams with the tools and knowledge to maximize NetSuite’s potential. Our reputation is dear to us and thus we are very careful about who we bring on to our team. Yet, we know there are professionals in the community who would add to our culture and be appreciated for their specialized expertise.
Our hope is to engage with other senior developers who share our vision of innovation and mastery in the NetSuite ecosystem. If you found this article relevant, sign up for notifications to new articles as I post them.
If you’re inspired by this approach and want to collaborate on building more productive tools, or if you’re a senior developer looking for a team that values creativity, expertise, and collaboration, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
