At work we are in the middle of a move to Fabric for reporting. We're looking at paying a vendor to import data from NetSuite, but I always like to have a backup plan in case an external dependency doesn't work out.
We have grabbed some NetSuite data for reporting in the past, but we didn't use the best approach and ran into common issues with syncing data form NetSuite. (I may write about these issues in another post). I decided that this was a good opportunity to look at building a better solution. I don't want to have to type out SuiteQL queries for every piece of data I want to sync so I looked for a way to generate them. NetSuite doesn't seem to have an easily accessible API to get this data, but as always seems to be the case, Tim Dietrich had a post that acted as a good starting point.
The code in the post did grab a lot of the schema, but the issue was that it only grabs top level objects. The transactionline table is important for us, but it only appears as child of a transaction, so it doesn't get found by the original code. I modified the code to grab these children, and download the final result as a file rather than copying it out of the console. I know it can be hard to find any information about NetSuite so I wanted to share the code.
function downloadJson(data, filename) {
const jsonData = JSON.stringify(data, null, 4); // Format with 4 spaces indentation
const blob = new Blob([jsonData], { type: 'text/json' });
const a = document.createElement('a');
a.download = filename;
a.href = window.URL.createObjectURL(blob);
a.dataset.downloadurl = ['text/json', a.download, a.href].join(':');
a.click();
}
console.clear();
var rcEndpoint = '/app/recordscatalog/rcendpoint.nl';
var recordTypes;
var action = 'getRecordTypes';
var data = encodeURI( JSON.stringify( { structureType: 'FLAT' } ) );
var url = rcEndpoint + '?action=' + action + '&data=' + data + "&_=1753882307833";
var xhr = new XMLHttpRequest();
xhr.open( 'get', url, false );
xhr.send();
recordTypes = JSON.parse( xhr.response );
console.log( JSON.stringify( recordTypes, null, 5 ) );
console.log( 'recordTypes loaded.' );
var schema = [];
var seenSchemas = {};
lookups = [];
recordTypes.data.forEach(element => {
lookups.push( element.id );
});
while ( lookups.length > 0 ) {
recordId = lookups.shift();
if ( seenSchemas[recordId] ) {
console.log( 'Skipping record type ' + recordId + ' as it has already been processed.' );
continue;
}
console.log( 'Loading details for record type ' + recordId + '...' );
// Get details for the record type.
action = 'getRecordTypeDetail';
data = encodeURI( JSON.stringify( { scriptId: recordId, detailType: 'SS_ANAL' } ) );
var url = rcEndpoint + '?action=' + action + '&data=' + data;
var xhr = new XMLHttpRequest();
xhr.open( 'get', url, false );
xhr.send();
recordDetail = JSON.parse( xhr.response );
if (recordDetail.data.joins.length) {
recordDetail.data.joins.forEach( join => {
if(seenSchemas[join.sourceTargetType.id]) {
return;
}
lookups.push( join.sourceTargetType.id );
});
}
schema.push( recordDetail.data );
seenSchemas[recordId] = true;
}
downloadJson(schema, 'netsuite_schema.json' );
console.log( 'Schema created.' );