NetSuite Script to help identifying which Analytics tables a record is stored in

findAnalyticsTable.js

/**
* A script to help identifying which Analytics tables a record is stored in.
*
* It works by retrieving the count for every table in the account and saving that to browser local storage
* The next time the script is run, it will compare the current count for each table, and identify any tables
* that have changed.
*
* USAGE:
*
* This script is intended to be copied and pasted into browser dev console. I recommend using the Chrome Snippets Feature
* It needs to be run from a page in NetSuite which has SuiteScript 2.0 require module available. Most record pages in
* Edit mode will work.
* 
* 1. Run the script.
* 2. Create a new record of the type you are trying to identify
* 3. Run a second time to identify changes.
*
* A couple of tables are excluded from the search, as from testing in my particular account they caused the query to either
* time out or run extremely slowly. You can edit the excludedTables array at the start of this script if you suspect any
* account specific tables are causing issues.
* 
* Author: Michoel Chaikin <michoel@gmail.com>
*
**/

require(["N/query"], function (query) {
  const excludedTables = [
    "pricingWithCustomers",
    "salesInvoiced",
    "revenuePlanSource",
    "TransactionNumberingAuditLog",
    "revRecEventTranLine",
  ];
  
  console.log('The following tables are excluded as they are extremely slow or cause timeouts:');
  excludedTables.forEach(tableName => console.log(`\t* ${tableName}`));

  getRecordTypes().then(function (recordTypes) {
    console.log(`Total ${recordTypes.length} tables found in this account. Please wait while counts are being retrieved. This usually takes about 20-30 seconds.`);

    const recordCounts = getRecordCounts(recordTypes);
    const oldRecordCounts = localStorage.getItem('ANALYTICS_RECORD_COUNTS');

    if (oldRecordCounts) {
      const modifiedTables = compareRecordCounts(JSON.parse(oldRecordCounts), recordCounts);
      if (modifiedTables.length) {
        console.log('The following tables have been modified since the last run:');
        modifiedTables.forEach(modifiedTable => console.log(`\t* ${modifiedTable}`));
      } else {
        console.log("No changes have been detected");
      }
    } else {
      console.log("Record counts have been saved. Please make changes and then re-run the script to identify updated tables");
    }

    localStorage.setItem('ANALYTICS_RECORD_COUNTS', JSON.stringify(recordCounts));
  });

  async function getRecordTypes() {
    // Uses an undocumented internal API from the Records Catalog to get all record types (tables) in this account
    const endpoint =
      "/app/recordscatalog/rcendpoint.nl?action=getRecordTypes&data=%7B%22structureType%22:%22FLAT%22%7D";
    const response = await fetch(endpoint);
    const responseJson = await response.json();
    const recordTypes = responseJson.data.map((recordType) => recordType.id);
    return recordTypes.filter((recordType) => !excludedTables.includes(recordType));
  }

  function getRecordCounts(recordTypes) {
    const sql = recordTypes
      .map((recordType) => `SELECT '${recordType}' AS table, COUNT(*) AS cnt FROM ${recordType}\n`)
      .join("UNION\n");

    const data = query.runSuiteQL({
      query: sql,
      params: null,
    });

    // was getting strange error with asMappedResults().
    const recordCounts = {};

    data.toJSON().results.forEach((result) => {
      recordCounts[result.values[0]] = result.values[1];
    });

    return recordCounts;
  }

  function compareRecordCounts(oldRecordCount, newRecordCount) {
    const modifiedTables = [];
    Object.keys(oldRecordCount).forEach((recordType) => {
      if (oldRecordCount[recordType] !== newRecordCount[recordType]) {
        modifiedTables.push(`${recordType} (old=${oldRecordCount[recordType]}, new=${newRecordCount[recordType]})`);
      }
    });
    return modifiedTables;
  }
});