Link Records by Field

This script links records in two NocoDB tables when the values in the selected fields match. The user does these steps:

Select a source table and a target table.
Select a matching field in each table.
Select the linked record field in the source table that connects to the target table.

For each record in the source table, the script finds a record in the target table with a matching value. If it finds a match, it updates the linked record field in the source record. The field then points to that record in the target table.

The script uses pagination for large datasets. At the end, it prints a summary of the results.

// Prompt user to pick Source & Target table
const sourceTable = await input.tableAsync("Select Source table");
const targetTable = await input.tableAsync("Select Target table");

// Prompt for matching field in Source
const fieldInSource = await input.fieldAsync("Select matching field in Source table", sourceTable);

// Prompt for matching field in Target
const fieldInTarget = await input.fieldAsync("Select matching field in Target table", targetTable);

// Prompt for linked record field in Source (should link to Target)
const linkFieldInSource = await input.fieldAsync("Select linked record field in Source table", sourceTable);

// Fetch all records from Target with pagination
let targetQuery = await targetTable.selectRecordsAsync({
    fields: [fieldInTarget],
    pageSize: 100
});

const mapTarget = new Map();
function indexTargetRecords(records) {
    for (let record of records) {
        const key = record.getCellValueAsString(fieldInTarget)?.trim();
        if (key) {
            mapTarget.set(key, record);
        }
    }
}
indexTargetRecords(targetQuery.records);

while (targetQuery.hasMoreRecords) {
    await targetQuery.loadMoreRecords();
    indexTargetRecords(targetQuery.records.slice(-100));
}

// Fetch all records from Source with pagination
let sourceQuery = await sourceTable.selectRecordsAsync({
    fields: [fieldInSource, linkFieldInSource],
    pageSize: 100
});

let totalProcessed = 0;
let totalLinked = 0;
let totalUnmatched = 0;

async function linkSourceRecords(records) {
    for (let record of records) {
        const valueSource = record.getCellValueAsString(fieldInSource)?.trim();
        const matchingTargetRecord = mapTarget.get(valueSource);

        if (matchingTargetRecord) {
            await sourceTable.updateRecordAsync(record.id, {
                [linkFieldInSource.id]: [{ id: matchingTargetRecord.id }]
            });
            totalLinked++;
        } else {
            totalUnmatched++;
        }

        totalProcessed++;
        output.clear();
        output.text(`Processed ${totalProcessed} records...`);
    }
}

// Process first page
await linkSourceRecords(sourceQuery.records);

// Process remaining pages
while (sourceQuery.hasMoreRecords) {
    await sourceQuery.loadMoreRecords();
    const newRecords = sourceQuery.records.slice(-100);
    await linkSourceRecords(newRecords);
}

// Output final summary (vertical format)
output.markdown(`### ✅ Summary`);
output.table({
    "Total Records Processed": totalProcessed,
    "Records Linked": totalLinked,
    "Unmatched Records": totalUnmatched
});

Use Cases

CRM Linking: Connect customers to orders or support tickets by customer IDs or names.
Product Mapping: Link SKUs from an imported dataset to master product records.
Inventory Management: Link stock movement entries to their items.
Data Consolidation: Normalize records from many sources by shared identifiers.

How It Works

Select Tables & Fields: Select a source table, a target table and a matching field in each.
Run the Script: The script finds the target record for each matching value in the source table.
Link Records: When the script finds a match, it updates the linked field of the source record.
View Summary: A final summary shows how many records were processed, linked or skipped.

Behavior in Edge Cases

Case	Result
Missing in Target	No target record matches. The script skips the source record.
Missing in Source	No source record matches. The script ignores the target record.
Duplicate Values in Source	All matching source records link to the same target record.
Duplicate Values in Target	The script uses the first match and ignores the others.
Blank or Null Values	The script skips records with empty matching values.