Unique Field Values Summary

View unique values and their frequencies in any field, with summary and table output

This script scans any field in your NocoDB table and displays all unique values along with how many times each value appears. It's helpful for identifying duplicates, auditing data, or quickly understanding the distribution of field content.

// Prompt user to select a table and a field
let table = await input.tableAsync("Select a table");
let field = await input.fieldAsync("Select a field", table);

// Start query with pageSize
let queryResult = await table.selectRecordsAsync({
    fields: [field],
    pageSize: 100,
});

// Create a Map to store value -> count
let valueCounts = new Map();
let totalRecordsProcessed = 0;

// Helper function to process a batch of records
function processRecords(records) {
    totalRecordsProcessed += records.length;

    for (let record of records) {
        let value = record.getCellValue(field);

        // Normalize the value
        let normalizedValues = [];
        if (Array.isArray(value)) {
            normalizedValues = value.map(v => v?.name ?? v);
        } else if (value && typeof value === 'object') {
            normalizedValues = [value.name ?? JSON.stringify(value)];
        } else {
            normalizedValues = [value];
        }

        for (let val of normalizedValues) {
            let key = val === null ? "null" : String(val);
            valueCounts.set(key, (valueCounts.get(key) || 0) + 1);
        }
    }
}

// Process first page
processRecords(queryResult.records);

// Keep loading more pages if available
while (queryResult.hasMoreRecords) {
    await queryResult.loadMoreRecords();
    processRecords(queryResult.records.slice(-100)); // Only process newly loaded records
}

// Convert to array of objects for output.table()
let resultTable = Array.from(valueCounts.entries())
    .sort((a, b) => b[1] - a[1])
    .map(([value, count]) => ({
        Value: value,
        Count: count
    }));

output.markdown("### Unique Values with Counts");
output.table(resultTable);

// Output total number of records processed
output.markdown(`**Total records processed:** ${totalRecordsProcessed}`);

Use Cases

• Data Cleanup : Identify unexpected or duplicate values
• Analytics & Auditing : Understand the frequency of values in any field
• Form Design : Spot commonly used values for building selects or filters
• Debugging : Review field behavior during imports or syncs

How It Works

1. Select a Table and Field
2. Script Scans All Records using pagination
3. Normalizes All Values (including arrays, objects, linked/selects)
4. Counts Frequency of each unique value
5. Displays a Table of values and their counts
6. Prints Total Records Processed at the bottom

Benefits

• ✅ Works with any field type (text, select, linked, etc.)
• ⚡ Fast and scalable (handles thousands of records with pagination)
• 📊 Output is clean, sortable, and copyable
• 🔍 Instantly highlights common or inconsistent entries
• 🧹 Helps with cleanup, analysis, or automation planning

Related pages

• API Reference Manual Examples : Browse categorized examples from the NocoDB API reference documentation to quickly access common scripting tasks and operations.
• Find and Replace : Search and replace text patterns across records in any text-based field with preview and confirmation
• Convert Attachments to URLs : Convert attachment fields into comma-separated URL lists for integration with external services
• Randomize Values : Generate random data for empty fields across multiple data types with customizable ranges and constraints
• Validate Emails : Identify and list all invalid email addresses in email fields for data quality assurance
• Get Select Options : Extract and display all available options from Single Select or Multiple Select fields in various formats
• Save View Ordering to Field : Preserve manual record ordering from any view by saving it as sequential numbers in a numeric field
• Link Records by Field : Link records between two tables using matching field values, with pagination and summary reporting.